对比EF Core谈分组排序取前N条数据问题（GroupBy + Take Top N）

[LeaFrock]

很多人都会遇到“对表分组，然后按条件取前N（N≥1）条数据”的业务需求。例如FreeSql issue区就有：#441， #731 等等。

EF Core以前不支持该功能，但EF Core 6增强了GroupBy：https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-6.0/whatsnew#improved-groupby-support 。例如：

var grouping = context.People
    .GroupBy(i => i.LastName)
    .Select(g => new { LastName = g.Key, Count = g.Count() , First = g.FirstOrDefault(), Take = g.Take(2)})
    .OrderByDescending(e => e.LastName)
    .ToList();

生成SQL：

SELECT [t].[LastName], [t].[c], [t0].[Id], [t2].[Id], [t2].[Age], [t2].[FirstName], [t2].[LastName], [t2].[MiddleInitial], [t0].[Age], [t0].[FirstName], [t0].[LastName], [t0].[MiddleInitial]
FROM (
    SELECT [p].[LastName], COUNT(*) AS [c]
    FROM [People] AS [p]
    GROUP BY [p].[LastName]
) AS [t]
LEFT JOIN (
    SELECT [t1].[Id], [t1].[Age], [t1].[FirstName], [t1].[LastName], [t1].[MiddleInitial]
    FROM (
        SELECT [p0].[Id], [p0].[Age], [p0].[FirstName], [p0].[LastName], [p0].[MiddleInitial], ROW_NUMBER() OVER(PARTITION BY [p0].[LastName] ORDER BY [p0].[Id]) AS [row]
        FROM [People] AS [p0]
    ) AS [t1]
    WHERE [t1].[row] <= 1
) AS [t0] ON [t].[LastName] = [t0].[LastName]
LEFT JOIN (
    SELECT [t3].[Id], [t3].[Age], [t3].[FirstName], [t3].[LastName], [t3].[MiddleInitial]
    FROM (
        SELECT [p1].[Id], [p1].[Age], [p1].[FirstName], [p1].[LastName], [p1].[MiddleInitial], ROW_NUMBER() OVER(PARTITION BY [p1].[LastName] ORDER BY [p1].[Id]) AS [row]
        FROM [People] AS [p1]
    ) AS [t3]
    WHERE [t3].[row] <= 2
) AS [t2] ON [t].[LastName] = [t2].[LastName]
ORDER BY [t].[LastName] DESC, [t0].[Id], [t2].[LastName], [t2].[Id]

而FreeSql在3.2.666版本之前，似乎只能通过分步ToSql和WithSql实现。在666版本之后，因为新增了嵌套查询WithTempQuery，也可以生成类似的语句了，但我个人觉得不够“自然”。例如作者在#441 给出的样例：

fsql.Select<User1>()
    .Where(a => a.Id < 1000)
    .WithTempQuery(a => new
    {
        item = a,
        rownum = SqlExt.RowNumber().Over().PartitionBy(a.Nickname).OrderBy(a.Id).ToValue()
    })
    .Where(a => a.rownum == 1)
    .ToList();

生成SQL：

SELECT *
FROM (
    SELECT a.[Id], a.[Nickname], row_number() over( partition by a.[Nickname] order by a.[Id]) [rownum]
    FROM [User1] a
    WHERE a.[Id] < 1000
) a
WHERE (a.[rownum] = 1)

所谓“自然”指的是使用者面对这样的问题，第一反应想到应该用WithTempQuery——而根据我个人的观察，大部分人对该问题的第一反应是：尝试使用“GroupBy”。我个人认为EF Core的方式更符合使用者解决该问题的“直觉”。

是否同样可以增强FreeSql的GroupBy相关API呢？又或者可以有其他方面的优化？欢迎各位参与讨论，谢谢！

[2881099]

我觉得即使用了 ORM 仍然要保持对 SQL 的熟练习惯，ef 产生的 SQL 不忍直视啊

[LeaFrock]

翻译成左联也还行啊哈哈哈，而且正常人也不会写First = g.FirstOrDefault(), Take = g.Take(2)这样的。
主要是Linq写起来很符合直觉，代码也精炼。