score:2

Accepted answer

first, don't use methods like firstordefault() on groupby result - they are not translatable. use only key(s) and aggregate functions (because that's what sql group by operator supports).

second, use temporary projection (select) for groupby result containing the key/aggregates needed, then join it to another entities (tables) to get the additional info needed for the final projection.

e.g.

from v in journalvoucherlines
group v by v.accountid into vg
select new // <-- temporary projection with group by fields needed
{
    accountid = vg.key,
    credit = vg.sum(v => v.credit),
    debit = vg.sum(v => v.debit),
} into vg
join bp in parties on vg.accountid equals bp.accountid // <-- additional join(s)
select new
{
    name = bp.fullname,
    key = vg.accountid,
    creditor = vg.credit,
    deptor = vg.debit,
    remainamount = vg.credit - vg.debit
};

which translates successfully to

select [p].[fullname] as [name], [t].[accountid] as [key], [t].[c] as [creditor], [t].[c0] as [deptor], [t].[c] - [t].[c0] as [remainamount]
from (
    select [j].[accountid], sum([j].[credit]) as [c], sum([j].[debit]) as [c0]
    from [journalvoucherlines] as [j]
    group by [j].[accountid]
) as [t]
inner join [parties] as [p] on [t].[accountid] = [p].[accountid]

update: the same linq query with method syntax is even straight forward:

var query = journalvoucherlines
    .groupby(v => v.accountid)
    .select(vg => new
    {
        accountid = vg.key,
        credit = vg.sum(v => v.credit),
        debit = vg.sum(v => v.debit),
    })
    .join(parties, vg => vg.accountid, bp => bp.accountid, (vg, bp) => new
    {
        name = bp.fullname,
        key = vg.accountid,
        creditor = vg.credit,
        deptor = vg.debit,
        remainamount = vg.credit - vg.debit
    });

but if you need more joins, query syntax is preferable.


Related Query

More Query from same tag