Deferred Execution in Linq to SQL

Just like the last post, this one is motivated by a comment I got from someone identified as merlin981. Since we seem to have a running dialog, do you have a blog or other online presence? In any case, I wanted to explain my understanding of how Linq to SQL uses deferred execution because merlin and I seemed to have a very different ideas.

Let's take a look at a simple query like the one below.

var dbContext = new TestDataContext();
var result = from x in dbContext.Products
         select x;

At this point, the query is just and expression tree. When you iterate over the the results, the following single query executes against the database:

SELECT [t0].[Id], [t0].[Name], [t0].[Price], [t0].[CategoryId]
FROM [dbo].[Product] AS [t0]

At this point, I can access the Id, Name and CategoryId of all the products that were in the the database without any other connections to the database. On the other hand, if you were to do something like this:

foreach (var product in result)
{
  Response.Write(product.Category.Name);
}

This block of code is going to hit the database once for each product. Obviously we want to avoid that, and there are several ways to do so. One is to return an anonymous type containing just the columns we need:

var result = from x in dbContext.Products
         select new
         {
             x.Name,
             CategoryName = x.Category.Name
         };

foreach (var product in result)
{
    Response.Write(product.CategoryName);
}

This method will do an inner join and pull back just the columns we asked for. Another way is to specify load options for our original query:

var dbContext = new TestDataContext();
dbContext.LoadOptions.LoadWith<Product>(p => p.Category);
var result = from x in dbContext.Products
         select x;

This tells the Linq to SQL Execution engine to load all the fields in the Category entity for each product. The generated SQL is below.

SELECT [t0].[Id], [t0].[Name], [t0].[Price], [t0].[CategoryId], [t1].[Name] AS [Name2]
FROM [dbo].[Product] AS [t0]
INNER JOIN [dbo].[Category] AS [t1] ON [t1].[Id] = [t0].[CategoryId]

I hope this has been a helpful example of how Linq To SQL uses deferred execution.