[Solved]-Entity Framework GroupBy to Sql Generation-LINQ,C#

try avoiding the

context.procedures.where(h => h.id == x.key).select(h => h.code).firstordefault()

by including the code in the groupby clause - i know it seems redundant, but ef is known to have problems translating operations on grouping that involve something other than using key accessors and aggregates:

//...
.groupby(x => new { id = x.visitline.procedureid, x.visitline.procedure.code })
.select(x => new
{
    id = x.key.id,
    paidamount = x.sum(t => t.paidamount),
    code = x.key.code
}).toarray();

update: the above generates the following sql in my test environment (latest ef6.1.3):

select
    1 as [c1],
    [groupby1].[k1] as [procedureid],
    [groupby1].[a1] as [c2],
    [groupby1].[k2] as [code]
    from ( select
        [extent5].[procedureid] as [k1],
        [extent6].[code] as [k2],
        sum([filter1].[paidamount]) as [a1]
        from    (select [extent1].[visitlineid] as [visitlineid], [extent1].[paidamount] as [paidamount]
            from    [dbo].[transactionline] as [extent1]
            inner join [dbo].[transaction] as [extent2] on [extent1].[transactionid] = [extent2].[id]
            left outer join [dbo].[eob] as [extent3] on [extent2].[eobid] = [extent3].[id]
            left outer join [dbo].[eobbatch] as [extent4] on [extent3].[eobbatchid] = [extent4].[id]
            where (1433 = [extent2].[transactiontypeid]) and ([extent3].[eobbatchid] is null or [extent4].[status] = 1) ) as [filter1]
        left outer join [dbo].[visitline] as [extent5] on [filter1].[visitlineid] = [extent5].[id]
        left outer join [dbo].[procedure] as [extent6] on [extent5].[procedureid] = [extent6].[id]
        group by [extent5].[procedureid], [extent6].[code]
    )  as [groupby1]

which is much better as i expected.

update 2: ef is a strange beast. using double projection produces the desired result:

//...
.groupby(x => x.visitline.procedureid)
.select(x => new
{
    id = x.key,
    paidamount = x.sum(t => t.paidamount),
})
.select(x => new
{
    x.id,
    x.paidamount,
    code = context.procedures.where(h => h.id == x.id).select(h => h.code).firstordefault()
}).toarray();

which produces the following:

select
    1 as [c1],
    [project2].[procedureid] as [procedureid],
    [project2].[c1] as [c2],
    [project2].[c2] as [c3]
    from ( select
        [groupby1].[a1] as [c1],
        [groupby1].[k1] as [procedureid],
        (select top (1)
            [extent6].[code] as [code]
            from [dbo].[procedure] as [extent6]
            where [extent6].[id] = [groupby1].[k1]) as [c2]
        from ( select
            [extent5].[procedureid] as [k1],
            sum([filter1].[paidamount]) as [a1]
            from   (select [extent1].[visitlineid] as [visitlineid], [extent1].[paidamount] as [paidamount]
                from    [dbo].[transactionline] as [extent1]
                inner join [dbo].[transaction] as [extent2] on [extent1].[transactionid] = [extent2].[id]
                left outer join [dbo].[eob] as [extent3] on [extent2].[eobid] = [extent3].[id]
                left outer join [dbo].[eobbatch] as [extent4] on [extent3].[eobbatchid] = [extent4].[id]
                where (1433 = [extent2].[transactiontypeid]) and ([extent3].[eobbatchid] is null or [extent4].[status] = 1) ) as [filter1]
            left outer join [dbo].[visitline] as [extent5] on [filter1].[visitlineid] = [extent5].[id]
            group by [extent5].[procedureid]
        )  as [groupby1]
    )  as [project2]

p.s. if it's unclear, the answer to your concrete question

is it possible to force ef to generate sql exactly as it was written within linq ?

is no. contrary, you should write linq query in a certain way in order to get the desired (or closer) sql query.