SQL Server 2005: what LINQ generated query is better - with "Where()" or "Any()"?
var query = from c in db.Customers select c;
query = query.Where(c => c.Orders.Where(o => o.OrderItems.Where(oi => oi.SellerID == sellerID).Count() > 0).Count() > 0);
that generates SQL:
SELECT [t0].[CustomerID], [t0].[FirstName], [t0].[LastName], [t0].[Email], [t0].[Company], [t0].[BillingAddress1], [t0].[BillingAddress2], [t0].[ShippingAddress1], [t0].[ShippingAddress2], [t0].[Phone], [t0].[Mobile], [t0].[Fax], [t0].[CreateDT], [t0].[UpdateDT], [t0].[DefaultLanguage], [t0].[DefaultPaymentInfo], [t0].[Active], [t0].[Title], [t0].[LoginID], [t0].[ReferringLink], [t0].[CustomerIP], [t0].[CustomerCountryByIP], [t0].[VatID]
FROM [dbo].[Customers] AS [t0]
WHERE ((
SELECT COUNT(*)
FROM [dbo].[Orders] AS [t1]
WHERE (((
SELECT COUNT(*)
FROM [dbo].[OrderItems] AS [t2]
WHERE ([t2].[SellerID] = 2428) AND ([t2].[OrderID] = [t1].[OrderID])
)) > 0) AND ([t1].[CustomerID] = [t0].[CustomerID])
)) > 0
or
var query = from c in db.Customers select c;
query = query.Where(c => c.Orders.Any(o => o.OrderItems.Any(oi => oi.SellerID == sellerID)));
that generates SQL:
SELECT [t0].[CustomerID], [t0].[FirstName], [t0].[LastName], [t0].[Email], [t0].[Company], [t0].[BillingAddress1], [t0].[BillingAddress2], [t0].[ShippingAddress1], [t0].[ShippingAddress2], [t0].[Phone], [t0].[Mobile], [t0].[Fax], [t0].[CreateDT], [t0].[UpdateDT], [t0].[DefaultLanguage], [t0].[DefaultPaymentInfo], [t0].[Active], [t0].[Title], [t0].[LoginID], [t0].[ReferringLink], [t0].[CustomerIP], [t0].[CustomerCountryByIP], [t0].[VatID]
FROM [dbo].[Customers] AS [t0]
WHERE EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[Orders] AS [t1]
WHERE (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[OrderItems] AS [t2]
WHERE ([t2].[SellerID] = 2428) AND ([t2].[OrderID] = [t1].[OrderID])
)) AND ([t1].[CustomerID] = [t0].[CustomerID])
)
A开发者_如何学运维ctual execution plan looks the same, but anyways, what is better to use? Thanks.
With the EXISTS
it is immediately clear to the database that 1 matching record is already enough. So as soon as it finds anything, it can return. With a count... that optimisation might not be used. But since you say that the execution plan is the same, I guess that MS SQL understands how it can optimize the query.
Personally I would choose for the exists since that describes exactly what you need. Having a non-zero count is less clear imho.
精彩评论