如何在EF Core中按条件查询每个Identifier分组的最新一条数据

你的两个写法报错都是受限于EF Core 5的LINQ翻译能力：

• 第一种写法在Where中嵌套了q1.First(...)的子查询逻辑，同时Join仅关联了Identifier字段，后续的GroupBy模式不符合EF Core 5的可翻译规则，无法生成正确SQL。
• 第二种写法直接在GroupBy后对分组内元素排序取第一条，这类操作EF Core 5不支持翻译，需要升级到EF Core 6+才有原生支持。

以下两种写法都可以被Pomelo.EntityFrameworkCore.MySql 5.0.3正确翻译，完全不需要客户端求值，性能和原生SQL一致。

方案1：子查询Join（和你原生SQL逻辑完全对齐） #

逻辑和你写的MariaDB原生SQL完全一致，先按Identifier分组求最大UseDate，再关联原表取对应记录：

// 第一步：构造分组求最大日期的子查询，不会立即执行
var maxDateQuery = db.MyTable
    .GroupBy(t => t.Identifier)
    .Select(g => new 
    {
        Identifier = g.Key,
        MaxUseDate = g.Max(x => x.UseDate)
    });

// 第二步：关联原表，同时匹配Identifier和最大日期，再按Identifier去重取第一条
var result = db.MyTable
    .Join(
        maxDateQuery,
        t => new { t.Identifier, t.UseDate },
        m => new { m.Identifier, UseDate = m.MaxUseDate },
        (t, m) => t
    )
    .GroupBy(t => t.Identifier)
    .Select(g => g.First())
    .ToList();

return new ObjectResult(result);

如果同一个Identifier的最大UseDate存在多条记录，想指定取数规则，可以把Select(g => g.First())改成Select(g => g.OrderByDescending(x => x.PartId).First())，根据实际需求加排序规则即可。

方案2：窗口函数（性能更优） #

用ROW_NUMBER窗口函数实现，仅需一次表扫描，数据量大的时候性能远高于子查询Join的方案：

var result = db.MyTable
    .Select(t => new 
    {
        Entity = t,
        RowNumber = EF.Functions.RowNumber().Over(
            partitionBy: x => x.Identifier,
            orderBy: x => x.UseDate.Descending()
        )
    })
    .Where(x => x.RowNumber == 1)
    .Select(x => x.Entity)
    .ToList();

return new ObjectResult(result);

如果需要自定义同日期下的取数优先级，只需要在orderBy参数里追加排序字段即可，示例：

orderBy: x => new { x.UseDate.Descending(), x.PartId.Descending() }

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