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 26/03/2008

In ASP.Net the engine looks for various methods to call to handle events such as

public void Page_Load(object sender, EventArgs e) {}

I had always assumed that the framework searched for these methods based on their full signature, turns out this isn't the case. The ASP.Net MVC framework uses a

public void Page_Load() {}

method, and I was trolling through the code trying to find where this is called from, when I ended up inside the Page ProcessRequest method, i.e. the default ASP.net processing. Which meant there was no special processing for this version of Page_Load.

I quickly create a bog standard web app and added a no parameter Page_Load to it, and sure enough it fires!

Note that if you have the parameterised and no parameter Page_Load only the parameterised one is called.

Posted 20/03/2008

I've just started playing with ASP.NET MVC (or Microsoft's Homage to Ruby on Rails as Tim Ewald labelled it!), it's good to see a community growing up around this.

Posted 20/03/2008

As a developer I constantly use Visual Studio and SQL Server, and it turns out thatn when you install these, the install order is important. If you install SQL Server before VS200x then you get all the SQL tools such as the Management Studio and the Profiller. However, if you install VS200x and then install SQLServer the tools do not get installed. This has annoyed and frustrated me for years, then I discovered it wasn't just me, other people were having the same problem.

If only I'd read the warnings!

This week when I was going through the process again, having already installed VS200x I paid attention to what the SQL Server installation was telling me. When you install SQLServer it goes through a systems check to make sure you are able to install the software, things such as how much memory you have, whether you have IIS etc. One of the warnings I got was an "Edition Change Check (Warning)". Normally I ignore this as it's only a warning. But this time I took notice

The warning says

To change an existing instance of Microsoft SQL Server 2005 to a different edition of SQL Server 2005, you must run SQL Server 2005 Setup from the command prompt and include the SKUUPGRADE=1 parameter.

and this time I did take notice. I fired up a command prompt, flipped to the Servers directory and ran

Setup SKUUPGRADE=1

and joy of joys all the tools appear as part of the install.

Of course, the annoying thing is, why doesn't the installer do this. It's detected the problem, one little 'Do you want to upgrade?' checkbox wouldn't have hurt anybody!

Posted 20/03/2008

Rock Solid Knowledge © Copyright 2013