LinqDataSource - High Performance Queries Using DataLoadOptions - Avoiding Database Roundtrips
by David Hayden ( Microsoft MVP C# ), Filed: LINQ To SQL
I have been messing with the new LinqDataSource in Visual Studio 2008 Beta 2 to figure out the best way to maintain some decent performance in my queries by avoiding too many database roundtrips. I believe the answer is a nice little gem I stumbled upon called DataLoadOptions, which gives you much more control over the lazy loading and prefetch functionality of the DataContext Class.
Take a classic case where you want to display all the categories in a GridView but rather than displaying a foreign key of BlogId you want to display the actual Title of the Blog ( Blog.Title ). In essence you need access to the Blog Class which has a 1:1 relationship with each Category.
In your GridView, you will have a Template Column as such that traverses the relationship from Category -> Blog to display the Title:
<asp:TemplateField HeaderText="Blog" SortExpression="Blog.Title">
<ItemTemplate>
<%# Eval("Blog.Title") %>
ItemTemplate>
asp:TemplateField>
The problem in a Lazy-Loading scenario is that Blog will not be included in the original select query and thus will need to be loaded on demand as each Category is added to the GridView. This mean for each Category in the GridView there will be a query issued against the database to load the Blog associated with it so the Blog's Title can be displayed. This means at a minimum there will be 51 queries ( roundtrips ) against the database to simply display 50 categories. That can be a performance nightmare.
One way to avoid this for which I just discovered, is to specify that the Blog be Prefetched as Part of each Category. One can do this with the LinqDataSource by hooking into the ContextCreated Event of the LinqDataSource and specifying a DataLoadOption of prefetching the Blog with each Category:
protected void Page_Init(object sender, EventArgs e)
{
LinqDataSource1.ContextCreated += new
EventHandler<LinqDataSourceStatusEventArgs>
(LinqDataSource1_ContextCreated);
}
void LinqDataSource1_ContextCreated(object sender,
LinqDataSourceStatusEventArgs e)
{
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Category>(Category => Category.Blog);
(e.Result as BlogDataContext).LoadOptions = options;
}
The DataLoadOption above will cause only 1 query being sent to the Database, helping greatly improve performance in this case. Check your trusty SQL Server Profiler when using these various DataSource Controls to make sure you are aware of the database traffic.
Also Read:
News Feed: David Hayden ( Microsoft MVP C# )
Filed: LINQ To SQL