EntityFramework Core 3关于group by取最大值的写法

本想百度抄下现成的，发现不多，而且大多感觉好复杂，还是自己写了发个吧：

List<M_Entity> svcs = db.M_Entities.Where(u => db.M_Entities.GroupBy(k => k.ColumnID).Select(k => k.Max(o => o.ID)).Contains(u.ID)).ToList();

 

EF生成的语句如下：

SELECT [s].[ID], [s].[ColumnID]
FROM [M_Entities] AS [s]
WHERE [s].[ID] IN (
    SELECT MAX([s0].[ID])
    FROM [M_Entities] AS [s0]
    GROUP BY [s0].[ColumnID]
)

 

就我的半吊子sql水平直接写也就这样了，再加个条件ColumnID>1

List<M_Entity> svcs = db.M_Entities.Where(u => db.M_Entities.GroupBy(k => k.ColumnID).Select(k => k.Max(o => o.ID)).Contains(u.ID) && ColumnID>1).ToList();

生成语句如下：

SELECT [s].[ID], [s].[ColumnID]
FROM [M_Entities] AS [s]
WHERE [s].[ID] IN (
    SELECT MAX([s0].[ID])
    FROM [M_Entities] AS [s0]
    GROUP BY [s0].[ColumnID]
)

AND [s].[ColumnID] >1

 

哪位大牛有更好的欢迎评论贴下