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 Articles