Wednesday, 26 March 2008
LINQ Nasties
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 at 2:28 PM in Net
