EF Core在.NET 7与.NET Core 3.1中针对MySQL的查询生成差异问题

需要生成的目标SQL语句：

SELECT *
FROM `CustomerMainTable` AS `t`
WHERE `t`.`CustomerMainTableId` IN (
    SELECT MAX(`t0`.`CustomerMainTableId`)
    FROM `CustomerMainTable` AS `t0`
    GROUP BY `t0`.`CustomerMainTableCustomerId`
)

业务背景：CustomerMainTable表中，CustomerMainTableId是自增主键，CustomerMainTableCustomerId是客户唯一标识。同一客户可能有多条交易记录，需求是获取每个客户的最新交易记录（即每个客户对应CustomerMainTableId最大的那条记录）。

在EF Core 3.1中，以下LINQ查询可以正确生成上述目标SQL：

var CustomerGroupQuery = _DB.CustomerMainTable.Where(p => _DB.CustomerMainTable
    .GroupBy(l => l.CustomerMainTableCustomerId)
    .Select(g => g.Max(c => c.CustomerMainTableId))
    .Contains(p.CustomerMainTableId));

但在EF Core 6或7中执行相同的LINQ查询时，生成的SQL存在逻辑错误：

SELECT *
FROM `CustomerMainTable` AS `t`
WHERE EXISTS (
    SELECT 1
    FROM `CustomerMainTable` AS `t0`
    GROUP BY `t0`.`CustomerMainTableCustomerId`
    HAVING MAX(`t0`.`CustomerMainTableId`) = `t`.`CustomerMainTableId`)

拆分查询（先将最大ID列表拉取到内存再查询）可以生成正确SQL，但需要额外的内存操作，不够理想：

List<int> idlist = _DB.CustomerMainTable
    .GroupBy(l => l.CustomerMainTableCustomerId)
    .Select(g => g.Max(c => c.CustomerMainTableId)).ToList();

var CustomerGroupQuery = _DB.CustomerMainTable.Where(p => idlist.Contains(p.CustomerMainTableId));

1. 显式定义子查询变量（推荐） #

将分组取最大ID的逻辑单独定义为IQueryable变量，再用于Contains条件。这种方式能引导EF Core的查询翻译器生成正确的IN子查询：

var maxIdsQuery = _DB.CustomerMainTable
    .GroupBy(l => l.CustomerMainTableCustomerId)
    .Select(g => g.Max(c => c.CustomerMainTableId));

var CustomerGroupQuery = _DB.CustomerMainTable.Where(p => maxIdsQuery.Contains(p.CustomerMainTableId));

该方法与原业务逻辑完全一致，无需修改核心逻辑，仅通过拆分变量优化查询翻译结果。

2. 使用JOIN关联实现 #

通过JOIN方式关联分组后的最大ID记录，同样能实现需求，且生成的SQL逻辑等价：

var CustomerGroupQuery = from customer in _DB.CustomerMainTable
                         join maxCustomerId in _DB.CustomerMainTable
                             .GroupBy(l => l.CustomerMainTableCustomerId)
                             .Select(g => new 
                             { 
                                 CustomerId = g.Key, 
                                 MaxRecordId = g.Max(c => c.CustomerMainTableId) 
                             })
                         on new 
                         { 
                             customer.CustomerMainTableCustomerId, 
                             customer.CustomerMainTableId 
                         } equals new 
                         { 
                             maxCustomerId.CustomerId, 
                             maxCustomerId.MaxRecordId 
                         }
                         select customer;

生成的SQL会采用JOIN替代IN子查询，执行效率与目标SQL相近。

3. 调整EF Core查询翻译配置（备选） #

如果上述方法无效，可尝试禁用部分查询优化选项，强制EF Core使用IN子查询翻译：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer("你的数据库连接字符串")
        .UseQuerySplittingBehavior(QuerySplittingBehavior.SingleQuery);
}

注意：此方法需谨慎使用，避免对其他查询的性能或翻译结果产生负面影响。

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