Wednesday, 26 March 2008

LINQ Nasties

This was raised on an internal mailing list by Brock Allen, and as he isn't blogging much (not that I can comment) I thought I'd raise it here.

Look at this query:

NorthwindDataContext db = new NorthwindDataContext();
db.Log = Console.Out;

var supp = (from s in db.Suppliers
           select s).FirstOrDefault();

var prods = from p in supp.Products
           where p.UnitsInStock > 10
           select p;

foreach (var p in prods)
{
   Console.WriteLine(p.ProductName);
}

Looks pretty harmless, get a supplier, then get the products fromt that supplier where the UnitsInStock > 10.

Problem is if yuou run this, you see the following in SQLProfiler

exec sp_executesql N'SELECT TOP (1) [t0].[SupplierID],
[t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle],
[t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode],
[t0].[Country], [t0].[Phone], [t0].[Fax], [t0].[HomePage]
FROM [dbo].[Suppliers] AS [t0]
WHERE [t0].[SupplierID] = @p0',N'@p0 int',@p0=1

exec sp_executesql N'SELECT [t0].[ProductID], [t0].[ProductName],
[t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit],
[t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder],
[t0].[ReorderLevel], [t0].[Discontinued]
FROM [dbo].[Products] AS [t0]
WHERE [t0].[SupplierID] = @p0',N'@p0 int',@p0=1

The first statement is executed when you call FirstOrDefault(), the second when you execute the foreach. Notice anything about the statements? They are almost exactly the same, except the second statement loses the TOP(1) part. This means that the where clause is happening on the client! Not a big deal you may think, unless of course the select returns 1000s of rows.

Fixing this is easy

var prods = from p in db.Products
           join s in db.Suppliers
           on p.SupplierID equals s.SupplierID
           where s.SupplierID == 2
           && p.UnitsInStock > 10
           select p;

which gives:

exec sp_executesql N'SELECT [t0].[ProductID], [t0].[ProductName],
[t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit],
[t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder],
[t0].[ReorderLevel], [t0].[Discontinued]
FROM [dbo].[Products] AS [t0]
INNER JOIN [dbo].[Suppliers] AS [t1] ON [t0].[SupplierID] = ([t1].[SupplierID])
WHERE ([t1].[SupplierID] = @p0) AND ([t0].[UnitsInStock] > @p1)',N'@p0
int,@p1 int',@p0=2,@p1=10

Which is probably what you want.

I like LINQ, especially LINQ to XML, but this reminds of the dark days of EJB Entity Beans. You really have to profile the generated code to understand exactly what LINQ is giving you, don't be seduced by the ease of use.

Update

Ian Griffith pointed this out:

"One subtlety with your LINQ Nasties post is that you kind of make it look like the solution is to use one query instead of two. In fact, the key is understanding which operations will evaluate the query and which won't."

And he's dead right, Ian's blogged about this here. Even if you don't read Ian's full post, read the "Know Your Tools" section at the end.

Posted by kevin at 2:28 PM in Net

Responses (0)