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

- John L.

---

你是否遇到了性能问题或仅是猜测？ - johnny 5

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与期望的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

- Ivan Stoev

---

它从未生成带有HAVING子句的SQL吗？ - John L.

如果您的Linq查询最终不返回每个IGrouping内的数据，而仅仅使用聚合函数，那么它将使用HAVING语句（但是此答案中的Linq查询确实返回了所有内部数据）。请注意，带有WHERE的外部查询在很大程度上相当于使用HAVING的手写查询，并且我会惊讶地发现DBMS会制定与手写查询有明显不同执行计划。 - Dai