Posts tagged 'LINQ to SQL'

Eager-Fetching of Relationships with LINQ to SQL

Monday, 13 April 2009 1 comments
LINQ to SQL .NET

By default, LINQ to SQL lazy-loads its relationships. This means it won't go fetching entire trees of objects when you're only using the top ones. However, this might not always be desirable. Imagine if every time you output a post for your blog, you include tagsa. You might write something like this:

foreach(var post in db.Posts)
{
	Response.Write("<h1>{0}</h1>", post.Title);
	foreach(var Tag in post.Tags)
	{
		Response.Write("<a href=\"{0}\">{1}</a>",
			Html.Encode(tag.FullUrl),
			Html.Encode(tag.Name)
		);
	}
}

This code will work fine, but if you were to examine the SQL being generated, you'd see n+1 SELECT queries. One for fetching the posts, and one for fetching the tags for each posts - individually.

What can we do about it?

LINQ to SQL allows us to specify a set of DataLoadOptions that dictate this behaviour. One of the methods of the DataLoadOptions is LoadWith<T> which allows us to say "whenever you load x, always include y". E.g.

// Create DataLoadOptions
DataLoadOptions dlo = new DataLoadOptions();

// Always fetch tags when we get posts
dlo.LoadWith<Post>(p => p.Tags);

// Set these options on the DataContext
db.LoadOptions = dlo;

If we re-run the original query, we'll now find a JOIN to the Tags table and just a single query to fetch all the data we require.

Simple! However, bear in mind that LINQ to SQL might not always generate joins. I've got a few cases where the LoadWith seems to be ignored. As soon as I figure out why, I'll be sure to update this post!

Related Reading

1 comments