EF Core 6按月份分组并聚合内部集合求和的查询报错问题

需求描述 #

我需要创建一个按指定条件分组的查询，结果集需包含内部列表的求和值。以下是我编写的两个失败查询：

第一个失败查询 #

var invoices = await _dbContext.Beneficiaries
                 .Where(dbEntry => dbEntry.Id == beneficiaryId && dbEntry.ProviderId == providerId)
                 .SelectMany(dbEntry => dbEntry.Invoices)
                 .GroupBy(dbEntry => dbEntry.IssueDate.Month)
                 .Select(dbEntry => new
                 {
                     IssueMonth = dbEntry.Key,
                     VAT = dbEntry.Max(invoice => invoice.VAT),
                     TotalPay = dbEntry.Select(invoice => invoice.InvoiceEntries.Sum(entry => entry.DelegateHourlyRate)).Max(),
                     TotalSell = dbEntry.Select(invoice => invoice.InvoiceEntries.Sum(entry => entry.BeneficiaryHourlyRate)).Max(),
                 })
            .Where(group => group.IssueMonth <= _todayDate.UtcNow.Month && group.IssueMonth >= _todayDate.UtcNow.Month - (int)by)
            .ToListAsync();

实体类结构 #

public class Beneficiary
{
    public ICollection<Invoice> Invoices { get; set; }
}

public class Invoice
{
    public ICollection<InvoiceEntry> InvoiceEntries { get; set; }
}

public class InvoiceEntry
{
    public decimal DelegateHourlyRate { get; set; }
    public decimal BeneficiaryHourlyRate { get; set; }
}

异常信息 #

• EF Core 5.0.9异常：

The LINQ expression 'GroupByShaperExpression: KeySelector: b.IssueDate, ElementSelector:EntityShaperExpression: EntityType: Invoice ValueBufferExpression: ProjectionBindingExpression: EmptyProjectionMember IsNullable: True .Max(invoice => invoice.InvoiceEntries.Count)' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

• EF Core 6.0.8异常：

SqlException: Cannot perform an aggregate function on an expression containing an aggregate or a subquery. Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

项目依赖 #

<ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.8" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.8">
        <PrivateAssets>all</PrivateAssets>
        <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="6.0.8" />
</ItemGroup>

第二个失败查询变体 #

var invoices2 = await _dbContext.Beneficiaries
     .Where(dbEntry => dbEntry.Id == beneficiaryId && dbEntry.ProviderId == providerId)
     .SelectMany(dbEntry => dbEntry.Invoices
         .GroupBy(dbEntry => dbEntry.IssueDate.Month)
         .Select(dbEntry => new
         {
             IssueMonth = dbEntry.Key,
             VAT = dbEntry.Sum(invoice => invoice.VAT),
             TotalPay = dbEntry.Sum(invoice => invoice.InvoiceEntries.Sum(entry => entry.DelegateHourlyRate)),
             TotalSell = dbEntry.Sum(invoice => invoice.InvoiceEntries.Sum(entry => entry.BeneficiaryHourlyRate))
         }))
 .Where(group => group.IssueMonth <= _todayDate.UtcNow.Month && group.IssueMonth >= _todayDate.UtcNow.Month - (int)by)
 .ToListAsync();

该查询异常 #

SqlException: Cannot perform an aggregate function on an expression containing an aggregate or a subquery. Cannot perform an aggregate function on an expression containing an aggregate or a subquery. Cannot perform an aggregate function on an expression containing an aggregate or a subquery. Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

解决方案 #

问题根源是SQL Server不允许在聚合函数中嵌套另一个聚合或子查询，你之前的查询都存在Sum(Sum(...))或Max(Sum(...))的嵌套聚合逻辑。调整思路，先计算每个Invoice对应的内部列表求和值，再进行分组聚合：

var invoices = await _dbContext.Beneficiaries
    .Where(b => b.Id == beneficiaryId && b.ProviderId == providerId)
    .SelectMany(b => b.Invoices)
    // 先计算单个Invoice的内部求和值，避免嵌套聚合
    .Select(inv => new 
    {
        Month = inv.IssueDate.Month,
        inv.VAT,
        InvoiceTotalPay = inv.InvoiceEntries.Sum(e => e.DelegateHourlyRate),
        InvoiceTotalSell = inv.InvoiceEntries.Sum(e => e.BeneficiaryHourlyRate)
    })
    // 按月份分组
    .GroupBy(x => x.Month)
    .Select(g => new 
    {
        IssueMonth = g.Key,
        // 根据需求选择Max或Sum，这里沿用你第一个查询的Max逻辑
        VAT = g.Max(x => x.VAT),
        TotalPay = g.Sum(x => x.InvoiceTotalPay),
        TotalSell = g.Sum(x => x.InvoiceTotalSell)
    })
    .Where(g => g.IssueMonth <= DateTime.UtcNow.Month && g.IssueMonth >= DateTime.UtcNow.Month - (int)by)
    .ToListAsync();

说明 #

1. 先通过Select将每个Invoice的InvoiceEntries求和结果提前计算出来，得到单个发票的InvoiceTotalPay和InvoiceTotalSell
2. 再基于月份分组，对每个月的发票级别的求和值进行二次聚合，这样就避免了SQL中的嵌套聚合问题
3. EF Core可以将这个逻辑正确翻译成合法的SQL语句，不会再触发聚合嵌套的错误

内容的提问来源于stack exchange，提问作者user19510842