我想要获取客户ID列表以及每个客户下订单的数量。筛选条件如下:
- 订单总额不足$10将不会计入。
- 未至少下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