如何在EF Core中按条件获取最新的条目？

Question

我有一张表格，结构如下（且包含样本数据）：

标识符	使用日期	零件编号
a123	05/01/2000	237
a123	05/01/2000	4656
a123	01/01/2000	2134
a124	04/01/2000	5234
a124	01/01/2000	2890

我需要获取每个（非唯一）标识符的最新条目，但每个标识符最多只能获取一条。

似乎可以解决我的问题的 MariaDB SQL 查询如下：

SELECT a.Identifier, a.MaxDate, b.PartId, b.UseDate 
FROM
(SELECT Identifier, MAX(UseDate) AS MaxDate FROM MyTable GROUP BY Identifier) a
LEFT JOIN MyTable b ON a.Identifier = b.Identifier
WHERE a.MaxDate = b.UseDate GROUP BY a.Identifier;

然而我需要它与C#和EF Core (Pomelo.EntitiFrameworkCore.MySql 5.0.3) 兼容，我的尝试如下：

var q1 = db.MyTable
            .GroupBy(t => t.Identifier)
            .Select(t => new { Identifier = t.Key, MaxDate = t.Max(x => x.UseDate) });

return new ObjectResult(db.MyTable
    .Join(
        q1,
        t1 => t1.Identifier,
        t2 => t2.Identifier,
        (t1, t2) => new { Identifier = t2.Identifier, PartId = t1.PartId, MaxDate = t1.MaxDate, UseDate = t1.UseDate })
    .Where(t => t.UseDate == q1.First(x => x.Identifier == t.Identifier).MaxDate)
    .GroupBy(t => t.Identifier)
    .ToList()
);

并且

return new ObjectResult(db.MyTable
    .GroupBy(t => t.Identifier)
    .Select(t => t.OrderByDescending(x => x.UseDate).FirstOrDefault())
    .ToList()
);

第一个抛出此错误：

System.InvalidOperationException: "无法翻译给定的 'GroupBy' 模式。在对其进行客户端评估之前，调用 'AsEnumerable'."

第二个实质上产生了相同的结果，只是抱怨LINQ表达式而不是GroupBy。

我想避免使用原始SQL，但该如何正确（且希望高效地）实现这一点？

- marxlaml

---

你的目标是哪个EFC版本？ - Ivan Stoev

我正在使用Pomelo.EntityFrameworkCore.MySql 5.0.3。 - marxlaml

Answer 1

有许多方法可以使用LINQ编写此类查询，其中大部分方法都能被EF Core 5/6+翻译。

一旦您为所需的分组和聚合定义了子查询，最直接的方法是将其与数据表进行连接，但不要使用join运算符 - 而是使用行限制相关子查询（使用SelectMany和Where、Take），例如：

var query = db.MyTable
    .GroupBy(t => t.Identifier)
    .Select(t => new { Identifier = t.Key, MaxDate = t.Max(x => x.UseDate) })
    .SelectMany(g => db.MyTable
        .Where(t => t.Identifier == g.Identifier && t.UseDate == g.MaxDate)
        .Take(1));

如果每个键值对应的排序字段都是唯一的（例如，在你的情况下，如果每个唯一的Identifier值都对应唯一的UseDate值），你可以直接使用Join运算符（因为不需要限制），例如：

var query = db.MyTable
    .GroupBy(t => t.Identifier)
    .Select(t => new { Identifier = t.Key, MaxDate = t.Max(x => x.UseDate) });
    .Join(db.MyTable,
        g => new { g.Identifier, UseDate = g.MaxDate },
        t => new { t.Identifier, t.UseDate },
        (g, t) => t);

或者直接将基于 Where 条件的 Max 应用到数据表中：

var query = db.MyTable
    .Where(t => t.UseDate == db.MyTable
        .Where(t2 => t2.Identifier == t.Identifier)
        .Max(t2 => t2.UseDate)
    );

最后，使用“标准”的LINQ方式获取每个组的前1个项目。

对于EF Core 6.0+：

var query = db.MyTable
    .GroupBy(t => t.Identifier)
    .Select(g => g
        .OrderByDescending(t => t.UseDate)
        .First());

对于 EF Core 5.0，查询中的分组结果集必须进行模拟处理：

var query = db.MyTable
    .GroupBy(t => t.Identifier)
    .Select(g => db.MyTable
        .Where(t => t.Identifier == g.Key)
        .OrderByDescending(t => t.UseDate)
        .First());

- Ivan Stoev