c# - 将“SELECT TOP (1) WITH TIES”转换为 EF Core
问题描述
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 Core 的简单转换:
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 和 EF Core 版本。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);
}
}
}
解决方案
您应该能够通过一个查询来做到这一点:
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 个订单时。(如果您有选择要比较的客户的标准,例如每个城市/州等)
如果要覆盖一个非常大的数据表,另一个选项就是查询最大计数,前提是您的实体配置了导航属性,以便您的 Customer 实体可以与它的 Orders 集合相关联:
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,请.Select(x => x.CustomerId)
在之前添加。ToList()
它运行两个查询,但它们相当简单,只返回所需的数据,而不是可能返回所有客户 ID/数据。
推荐阅读
- loops - 使用 Liquid 将 +1 计数动态传递给 zendesk 中的票证字段
- javascript - 使用导出默认值检查来自另一个 JavaScript 模块的变量状态
- android - 在 React-native 中从同一 Sourcefile 创建具有不同图标和 SplashScreen 的多个 APK
- google-cloud-platform - Cloud Composer 没有被删除
- android - 将回收的视图卡保留在底部表中
- java - ExecutorService - 并行运行任务并保存结果
- linux - 漏洞利用的 Bash 语法问题
- perl - perl中减法后文件的绝对值
- java - 无法在 cmd 中编译 Helloworld.java
- mysql - 如何从两个数据库表中删除数据