EF Core 5 + MySQL 5.7分组取每组最新记录的LINQ改写问题

问题背景 #

使用EF Core 5重写旧系统，数据库为MySQL 5.7（不支持ROW_NUMBER()等窗口函数，且无法升级版本），需要将以下原生SQL转换为LINQ查询：

SELECT 
    MAX(CreateTime) AS LatestRecordTime,MemberId,TotalAmount,CostAmount,BonusAmount
FROM 
    CashFlowRecord
WHERE 
    CreateTime <= '2023-12-31'
    And TotalAmount>0
GROUP BY 
    MemberId;

此前尝试的LINQ写法因EF Core自动翻译为MySQL 5.7不支持的窗口函数报错：

var qry = DbContext.Set<CashFlowRecord>()
        .Where(t=>t.CreateTime <= '2023-12-31' && t.TotalAmount > 0)
        .GroupBy(t => t.MemberId)
        .Select(t=>new { MemId = t.Key, Latest = t.OrderByDescending(e=>e.CreateTime).First()});

var lst = (await qry.ToDictionaryAsync(t => t.MemId, t => t.Latest)).Select(t=>t.Value).ToList(); 

错误信息：

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY c0.MemberId ORDER BY c0.CreateTime DESC) AS row

可行解决方案 #

方案1：子查询匹配最大时间关联原表 #

利用MySQL 5.7支持的分组取最大值+关联查询逻辑，对应LINQ写法如下：

// 子查询：获取每个符合条件会员的最新记录时间
var maxTimeSubquery = DbContext.Set<CashFlowRecord>()
    .Where(t => t.CreateTime <= new DateTime(2023, 12, 31) && t.TotalAmount > 0)
    .GroupBy(t => t.MemberId)
    .Select(g => new { MemberId = g.Key, LatestCreateTime = g.Max(t => t.CreateTime) });

// 关联原表获取完整记录
var result = await DbContext.Set<CashFlowRecord>()
    .Join(maxTimeSubquery,
        record => new { record.MemberId, record.CreateTime },
        sub => new { sub.MemberId, sub.LatestCreateTime },
        (record, sub) => record)
    .Where(t => t.TotalAmount > 0 && t.CreateTime <= new DateTime(2023, 12, 31))
    .ToListAsync();

说明：该写法完全基于MySQL 5.7支持的SQL语法，不会生成窗口函数。若存在同一会员同一时间多条记录的情况，会返回所有符合时间条件的记录，与原生SQL的非标准GROUP BY行为一致（MySQL关闭ONLY_FULL_GROUP_BY时的结果）。

方案2：直接执行原生SQL（最稳妥） #

既然原生SQL本身可以正常运行，直接用FromSqlRaw或FromSqlInterpolated执行原生语句，完全避免EF翻译问题：

// 原生SQL写法（参数化避免注入）
var cutoffDate = new DateTime(2023, 12, 31);
var sql = $@"SELECT 
    MAX(CreateTime) AS LatestRecordTime,MemberId,TotalAmount,CostAmount,BonusAmount
FROM 
    CashFlowRecord
WHERE 
    CreateTime <= {cutoffDate}
    And TotalAmount>0
GROUP BY 
    MemberId;";

var result = await DbContext.Set<CashFlowRecord>()
    .FromSqlInterpolated(sql)
    .ToListAsync();

说明：FromSqlInterpolated会自动处理参数化，避免SQL注入风险，比直接拼接字符串更安全。

原写法失败原因 #

EF Core 5在处理GroupBy后对分组内数据排序取First()的逻辑时，会自动将其翻译为ROW_NUMBER()窗口函数实现，而MySQL 5.7不支持该函数，因此触发语法错误。

长期建议 #

若业务允许，建议升级MySQL至8.0+版本并同步升级EF Core至更高版本，这样可以使用更简洁的窗口函数式LINQ写法，同时获得更好的性能和功能支持。

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