EF Core 7.0.5聚合函数报错：如何重构查询解决问题？

问题背景 #

从EF6升级到EF Core 7.0.5后，原有报表查询报错：Cannot perform an aggregate function on an expression containing an aggregate or a subquery。需求为：每个预订对应多条支付记录，筛选出每条预订的最新支付记录，再汇总所有已完成状态的支付金额。

原EF6可运行代码 #

var query = 
    from reservation in DbContext.Reservations
    join p1 in DbContext.Payments on reservation.Id equals p1.ReservationId into p2
    let payment = p2.OrderByDescending(x => x.Id).FirstOrDefault()
    orderby reservation.Id descending
    select new ReservationPaymentReport
    {
       ReservationId = reservation.Id,
       PaymentId = payment != null ? payment.Id : (int?)null,
       PaymentAmount = payment != null ? payment.Amount : (decimal?)null,
       IsPaymentCompleted = payment != null && paymentCompletedStatusIds.Contains(payment.StatusId),
    };

var paymentTotal = query
    .Where(x => x.PaymentId != null && x.IsPaymentCompleted.Value)
    .Sum(x => x.PaymentAmount) ?? 0;    

SQL生成差异 #

• EF6生成的SQL：使用OUTER APPLY先获取每个预订的最新支付记录，生成中间结果集后再做聚合计算，符合SQL语法规范。
• EF Core 7生成的SQL：将多个TOP 1子查询直接嵌套在SUM聚合函数中，触发SQL语法限制报错。

重构方案 #

方案1：分组获取最新支付记录ID后关联查询 #

先通过分组获取每个预订的最新支付记录ID，再关联支付表获取完整数据，最后筛选状态并求和：

// 获取每个预订对应的最新支付记录ID
var latestPaymentIds = DbContext.Payments
    .GroupBy(p => p.ReservationId)
    .Select(g => new { ReservationId = g.Key, LatestPaymentId = g.Max(p => p.Id) });

// 关联查询最新支付记录，筛选已完成状态后求和
var paymentTotal = DbContext.Payments
    .Join(
        latestPaymentIds,
        p => new { p.ReservationId, p.Id },
        lp => new { lp.ReservationId, lp.LatestPaymentId },
        (p, lp) => p
    )
    .Where(p => paymentCompletedStatusIds.Contains(p.StatusId))
    .Sum(p => (decimal?)p.Amount) ?? 0;

方案2：使用窗口函数（推荐，性能更优） #

利用EF Core支持的ROW_NUMBER()窗口函数，直接给每个预订的支付记录按ID倒序编号，取编号为1的最新记录，再筛选状态求和：

var paymentTotal = DbContext.Payments
    .Select(p => new 
    {
        p.Amount,
        p.StatusId,
        // 按预订分组，支付ID倒序编号，最新记录编号为1
        RowNum = EF.Functions.RowNumber().Over(PartitionBy(p.ReservationId).OrderByDescending(p.Id))
    })
    .Where(x => x.RowNum == 1 && paymentCompletedStatusIds.Contains(x.StatusId))
    .Sum(x => (decimal?)x.Amount) ?? 0;

方案说明 #

两种方案均避免了将子查询嵌套在聚合函数中的问题：

• 方案1通过分组预取最新ID，将关联后的结果作为聚合的数据源，SQL生成逻辑符合语法要求。
• 方案2使用窗口函数一次性完成"取最新记录"的筛选，生成的SQL会先过滤出符合条件的记录，再执行聚合计算，性能更高效。

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