正在将"SELECT TOP(1)WITH TIES"转换为EF Core

Question

T-SQL版本的查询

让我们用一些数据设置一个简单的表格：

DROP TABLE IF EXISTS #OrdersTable
CREATE TABLE #OrdersTable
(
Id int,
Custid int
);
INSERT INTO #OrdersTable (Id, Custid) VALUES (1, 71);
INSERT INTO #OrdersTable (Id, Custid) VALUES (2, 71);
INSERT INTO #OrdersTable (Id, Custid) VALUES (3, 71);
INSERT INTO #OrdersTable (Id, Custid) VALUES (4, 72);
INSERT INTO #OrdersTable (Id, Custid) VALUES (5, 72);
INSERT INTO #OrdersTable (Id, Custid) VALUES (6, 72);
INSERT INTO #OrdersTable (Id, Custid) VALUES (7, 73);
INSERT INTO #OrdersTable (Id, Custid) VALUES (8, 74);
INSERT INTO #OrdersTable (Id, Custid) VALUES (9, 74);

在这种情况下，客户71和72各有3个订单。客户73有1个订单。客户74有2个订单。

比方说，我们想知道订单最多的客户。

以下查询：

SELECT TOP (1) WITH TIES Custid
FROM #OrdersTable
GROUP BY Custid
ORDER BY COUNT(*) DESC;

结果如下：

Custid
-----------
71
72
(2 rows affected)

天真地转换为EF Core

给定以下类别：

public class Order
{
public int Id { get; set; }
public int Custid { get; set; }
}

以及以下数据：

var OrdersTable = new List<Order>()
{
new Order(){ Id = 1, Custid = 71},
new Order(){ Id = 2, Custid = 71},
new Order(){ Id = 3, Custid = 71},
new Order(){ Id = 4, Custid = 72},
new Order(){ Id = 5, Custid = 72},
new Order(){ Id = 6, Custid = 72},
new Order(){ Id = 7, Custid = 73},
new Order(){ Id = 8, Custid = 74},
new Order(){ Id = 9, Custid = 74},
};

这是一个简单的查询到EF核心的转换：

var n = OrdersTable.GroupBy(order => order.Custid).Select(grouping => grouping.Count()).Max();
var custids = OrdersTable.GroupBy(order => order.Custid).Where(grouping => grouping.Count() == n).Select(grouping => grouping.Key);

使用以下内容显示数据：

foreach (var custid in custids)
Console.WriteLine(custid);

我们得到：

71
72

问题

让我们把T-SQL和EFCore版本放在一起。T-SQL:

SELECT TOP (1) WITH TIES Custid
FROM #OrdersTable
GROUP BY Custid
ORDER BY COUNT(*) DESC;

EF核心：

var n = OrdersTable.GroupBy(order => order.Custid).Select(grouping => grouping.Count()).Max();
var custids = OrdersTable.GroupBy(order => order.Custid).Where(grouping => grouping.Count() == n).Select(grouping => grouping.Key);

我的问题是，在EF Core中有没有更有效的方法来实现这个查询？

完整程序

演示上述查询的完整C#控制台程序：

using System;
using System.Collections.Generic;
using System.Linq;
namespace EfCoreTop1Ties
{
public class Order
{
public int Id { get; set; }
public int Custid { get; set; }
}
internal class Program
{
static void Main(string[] args)
{
var OrdersTable = new List<Order>()
{
new Order(){ Id = 1, Custid = 71},
new Order(){ Id = 2, Custid = 71},
new Order(){ Id = 3, Custid = 71},
new Order(){ Id = 4, Custid = 72},
new Order(){ Id = 5, Custid = 72},
new Order(){ Id = 6, Custid = 72},
new Order(){ Id = 7, Custid = 73},
new Order(){ Id = 8, Custid = 74},
new Order(){ Id = 9, Custid = 74},
};
var n = OrdersTable.GroupBy(order => order.Custid).Select(grouping => grouping.Count()).Max();
var custids = OrdersTable.GroupBy(order => order.Custid).Where(grouping => grouping.Count() == n).Select(grouping => grouping.Key);
foreach (var custid in custids)
Console.WriteLine(custid);
}
}
}

Answer 1

您应该能够通过一个查询做到这一点：

var n = OrdersTable
.GroupBy(order => order.Custid)
.Select(grouping => new { CustomerId = grouping.Key, OrderCount = grouping.Count() })
.OrderByDescending(g => g.OrderCount)
.ToList();
var maxCount = n.First().OrderCount;
var custIds = n.Where(g => g.OrderCount == maxCount)
.Select(g => g.CustomerId)
.ToList();

在OrdersTable是DbContext DBSet的情况下，这将导致对数据库的1个查询。根据无回程的结果，在内存中检查与该计数匹配的返回项目。

如果你处理的是一个特别大的数据集，你可以考虑一些合理的假设，比如如果有成千上万的客户，无论是100还是1000；领带；订单数量最多。

var n = OrdersTable
.GroupBy(order => order.Custid)
.Select(grouping => new { CustomerId = grouping.Key, OrderCount = grouping.Count() })
.OrderByDescending(g => g.OrderCount)
.Take(100)
.ToList();
if (n.All(g => g.OrderCount == n.First().OrderCount)
// Redo query with larger threshold.

如果所有返回的行恰好具有相同的订单计数，它将再次运行查询。您可能希望以不同的方式处理这种情况，例如当所有客户都有0个订单时。(如果您有选择要比较的客户的标准，例如每个城市/州等(

有了一个非常大的数据表，另一个选项就是查询最大计数，前提是您的实体配置了导航属性，以便您的客户实体可以与其订单集合相关：

var maxOrderCount = dbContext.Customers
.OrderByDescending(x => x.Orders.Count)
.Select(x => x.Orders.Count)
.First();
var customers = dbContext.Customers
.Where(x => x.Orders.Count == maxOrderCount)
.ToList();

如果您只想要客户ID，请在ToList()之前添加.Select(x => x.CustomerId)。它运行两个查询，但它们相当简单，只返回所需的数据，而可能不是所有客户ID/数据。