如何在EF Core中高效实现T-SQL的SELECT TOP (1) WITH TIES查询

我们的需求是从订单表中查询订单数量最多的所有客户ID，原生T-SQL可以通过SELECT TOP (1) WITH TIES语法直接实现，仅需一次查询即可得到结果。而原始的两次分组查询实现需要两次数据库往返，性能较差，可通过以下两种更高效的方案实现：

方案一：EF Core 7.0及以上版本（推荐） #

EF Core 7.0开始支持将Take(1)配合分组后聚合排序的LINQ语句自动翻译为SELECT TOP (1) WITH TIES的SQL语句，性能和原生SQL完全一致，代码写法如下：

var custids = OrdersTable
    .GroupBy(order => order.Custid)
    .OrderByDescending(grouping => grouping.Count())
    .Take(1)
    .Select(grouping => grouping.Key)
    .ToList();

执行后生成的SQL和题目中给出的原生T-SQL效果完全相同，仅需一次数据库查询。

方案二：EF Core 7.0以下版本兼容方案 #

如果使用的EF Core版本低于7.0，可以将最大订单数的计算逻辑嵌入为子查询，让EF Core生成单次查询语句，避免两次数据库往返：

var custids = OrdersTable
    .GroupBy(order => order.Custid)
    .Select(grouping => new { Custid = grouping.Key, OrderCount = grouping.Count() })
    .Where(item => item.OrderCount == OrdersTable.GroupBy(order => order.Custid).Max(g => g.Count()))
    .Select(item => item.Custid)
    .ToList();

该写法虽然表面上出现了两次GroupBy，但EF Core会将内层的最大值计算翻译为嵌套子查询，最终只会向数据库发送一次查询请求，性能远优于原始的两次查询实现。

可通过开启EF Core的SQL日志输出来验证生成的查询语句，确认无多余的数据库请求。

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