EF Core按日分组统计订单状态 数据库端查询实现问题

错误原因 #

你遇到的Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.报错，是因为EF Core 5无法将GroupBy后直接关联子查询/聚合的逻辑转换为合法的T-SQL语句，MSSQL原生不支持在GROUP BY的分组字段列表中使用聚合或者子查询结果。

可行查询方案 #

我们改用EF Core 5支持翻译的ROW_NUMBER窗口函数逻辑实现取每日每个订单的最新状态，所有统计逻辑完全在数据库端执行：

// 1. 先定义统计的起止日期，可根据业务需求动态调整
var startDate = new DateTimeOffset(new DateTime(2021,10,1), TimeSpan.Zero);
var endDate = new DateTimeOffset(new DateTime(2021,11,1), TimeSpan.Zero); // 统计到10月31日23:59:59

var statistics = await _context.OrderStatusLogs
    // 先筛选指定日期范围的日志，减少不必要的数据扫描
    .Where(l => l.StartDateTime >= startDate && l.StartDateTime < endDate)
    // 按日期+订单ID分组，取每组内开始时间最晚的日志，即为当日该订单的最终状态
    .GroupBy(l => new { l.StartDateTime.Date, l.OrderId })
    .Select(g => g.OrderByDescending(l => l.StartDateTime).FirstOrDefault())
    // 按日期+状态分组统计对应订单数量
    .GroupBy(l => new { l.StartDateTime.Date, l.Status })
    .Select(g => new 
    {
        g.Key.Date,
        g.Key.Status,
        Count = g.Count()
    })
    .ToListAsync();

// 可选：补全所有日期+所有状态的0值（解决没有对应状态时数据缺失的问题，该部分数据量极小，放客户端执行无性能损耗）
var allDates = Enumerable.Range(0, (endDate.Date - startDate.Date).Days)
    .Select(offset => startDate.Date.AddDays(offset))
    .ToList();
var allStatuses = Enum.GetValues<OrderStatus>().Cast<OrderStatus>().ToList();

var fullResult = allDates
    // 生成日期和状态的全量笛卡尔积
    .Join(allStatuses, _ => 1, _ => 1, (date, status) => new { Date = date, Status = status })
    // 和统计结果关联补全数量
    .GroupJoin(statistics, 
        left => new { left.Date, left.Status }, 
        right => new { right.Date, right.Status }, 
        (left, right) => new 
        {
            left.Date,
            left.Status,
            Count = right.FirstOrDefault()?.Count ?? 0
        })
    .OrderBy(r => r.Date)
    .ThenBy(r => r.Status)
    .ToList();

// 按期望格式输出
foreach(var item in fullResult)
{
    Console.WriteLine($"{item.Date:dd/MM/yyyy} {item.Status} {item.Count} orders");
}

方案说明 #

• 全数据库端执行：除可选的0值补全逻辑外，所有统计逻辑都会被EF Core 5翻译为合法T-SQL语句直接在SQL Server执行，无客户端拉取全量数据计算的性能问题
• 仅Fluent语法：全程使用链式调用的Fluent查询语法，符合要求
• 可扩展性：适配其他按日统计场景时，仅需要修改日期过滤条件、分组维度即可，整体逻辑通用

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