c# - EF Linq 中的 HAVING 子句

Question

我想获取客户 ID 列表和该客户所下订单的数量。筛选条件为:

1. 总计不超过 10 美元的订单将不计算在内。
2. 未下至少 3 笔订单(每笔总金额不低于 10 美元)的客户将不会被列出。

因此，我将在 SQL 中执行以下操作:

SELECT customerID, COUNT(*)
FROM Orders
WHERE orderTotal > 10
GROUP BY customerID
HAVING COUNT(*) > 2

在 EF 中，我认为这可以表示为:

dbContext.Order
.Where(o => o.orderTotal > 10)
.GroupBy(o => o.customerID)
.Where(g => g.Count() > 2)
.ToList();

但这会生成以下使用派生表和连接的 SQL，而不是简单地使用 HAVING 子句。我认为这在性能方面远非最佳。有没有更好的方法来制定 EF 中的大小写，以便翻译后的查询将按应有的方式使用 HAVING 子句？

SELECT 
    [Project1].[C1] AS [C1], 
    [Project1].[customerID] AS [customerID], 
    [Project1].[C2] AS [C2], 
    [Project1].[ID] AS [ID], 

FROM ( SELECT 
    [GroupBy1].[K1] AS [customerID], 
    1 AS [C1], 
    [Extent2].[ID] AS [ID], 
    CASE WHEN ([Extent2].[storeID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
    FROM   (SELECT 
        [Extent1].[customerID] AS [K1], 
        COUNT(1) AS [A1]
        FROM [dbo].[Orders] AS [Extent1]
        WHERE [Extent1].[orderTotal] > cast(10 as decimal(18))
        GROUP BY [Extent1].[customerID] ) AS [GroupBy1]
    LEFT OUTER JOIN [dbo].[Orders] AS [Extent2] ON ([Extent2].[orderTotal] > cast(10 as decimal(18))) AND (([GroupBy1].[K1] = [Extent2].[customerID]) OR (([GroupBy1].[K1] IS NULL) AND ([Extent2].[customerID] IS NULL)))
    WHERE [GroupBy1].[A1] > 2
)  AS [Project1]
ORDER BY [Project1].[customerID] ASC, [Project1].[C2] ASC

Answer 1

好吧，LINQ to Entities 查询不等同于 SQL 查询，因为它返回一个分组(键和匹配元素对)的列表，它根本没有 SQL 等价物。

如果像在 SQL 查询中那样只返回 customerId 和 Count:

db.Orders
.Where(o => o.orderTotal > 10)
.GroupBy(o => o.customerID)
.Select(g => new { customerId = g.Key, orderCount = g.Count() })
.Where(g => g.Count > 2)
.ToList();

然后 EF 生成的 SQL 将与预期的几乎相同(或在功能上等同于):

SELECT
    [GroupBy1].[K1] AS [customerID],
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT
        [Extent1].[customerID] AS [K1],
        COUNT(1) AS [A1]
        FROM [dbo].[Orders] AS [Extent1]
        WHERE [Extent1].[orderTotal] > cast(10 as decimal(18))
        GROUP BY [Extent1].[customerID]
    )  AS [GroupBy1]
    WHERE [GroupBy1].[A1] > 2