linq - 如何在 EF Core 3.1 中使用 Linq 表达式加入 Group By

Question

这是我的 SQL 查询，我在 linqpad 中对其进行了测试，它有效，但在 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)
  }

当我在 EF Core 中使用查询时，出现以下异常:

The LINQ expression '(GroupByShaperExpression:KeySelector: (j.AccountId),ElementSelector:(EntityShaperExpression:EntityType: JournalVoucherLineValueBufferExpression:(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. "

此查询的最佳实践是什么？

Answer 1

首先，不要使用像FirstOrDefault()这样的方法。在 GroupBy结果 - 它们不可翻译。仅使用键和聚合函数(因为这是 SQL GROUP BY 运算符支持的)。
其次，对 Select 使用临时投影( GroupBy )结果包含所需的键/聚合，然后将其加入另一个实体(表)以获得最终投影所需的附加信息。
例如

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
};

成功转换为

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]

更新:使用方法语法的相同 LINQ 查询甚至是直截了当的:

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
    });

但是如果你需要更多的连接，查询语法更可取。