How can I use Linq expression for Join with GroupBy in EF Core 3.1

Question

This is my SQL query and I tested it in linqpad, and it worked, but it doesn't work in EF Core 3.1:

  from v in JournalVoucherLines
  group v by v.AccountId into vg
  join bp in Parties on vg.FirstOrDefault().AccountId equals bp.AccountId
  select new
  {
    name = bp.FullName,
    key = vg.Key,
    Creditor = vg.Sum(v => v.Credit),
    Deptor = vg.Sum(v => v.Debit),
    RemainAmount = vg.Sum(v => v.Credit) - vg.Sum(v => v.Debit)
  }

When I use the query in EF Core, I get this exception:

The LINQ expression '(GroupByShaperExpression: KeySelector: (j.AccountId), ElementSelector:(EntityShaperExpression: EntityType: JournalVoucherLine ValueBufferExpression: (ProjectionBindingExpression: EmptyProjectionMember) IsNullable: False ) ).FirstOrDefault()'
could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information. "

What is the best practice for this query?

user_5202563 · 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.

How can I use Linq expression for Join with GroupBy in EF Core 3.1

1 Answers