In my last post:

LinqDataSource And How You Can *Maximize* Roundtrips to Your Database

I discussed how you can easily misuse LINQ To SQL and bring your application to a crawl by causing performance issues via too many roundtrips to the database when accessing lazy-loaded properties. Most people familiar with O/R Mappers will recognize that classic problem.

I continued exploring how one can better tune the database queries and limit database roundtrips this weekend which resulted in the discovery of DataLoadOptions in LINQ To SQL and several posts:

• LinqDataSource - High Performance Queries Using DataLoadOptions - Avoiding Database Roundtrips
• LINQ to SQL - Performance Tradeoffs: Less Database Roundtrips but Duplicated Data in Each Row??
• LINQ To SQL - Lazy Loading Properties and Specifying PreFetch When Needed - Sweet Query and Performance Control

The DataContext in LINQ To SQL has a property called LoadOptions which gives you the ability to fine tune your queries and decide when to prefetch properties and associations that are normally lazy-loaded.

For example, if one wants to grab a Blog Entity and prefetch the Categories Associated with it that are normally lazy-loaded, you have the option of specifying the prefetch via the LoadOptions Property and a Lambda Expression. In this case we are fetching BlogId = 1 and prefetching the Categories for the Blog at the same time:

using (BlogDataContext context = new BlogDataContext())
{
    DataLoadOptions options = new DataLoadOptions();
    options.LoadWith<Blog>(c => c.Categories);

    context.LoadOptions = options;

    Blog blog = context.Blogs.Single<Blog>(c => c.BlogId == 1);

}

I discovered one can also do this with the LinqDataSource by hooking into the ContextCreated Event and specifying load options there as such:

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<Blog>(Blog => Blog.Categories);
    (e.Result as BlogDataContext).LoadOptions = options;
}

The end result is that via DataLoadOptions you can do some serious query tuning for performance optimization in your applications. The whole process has made me more thrilled about LINQ To SQL than ever before. Awesome stuff!

by David Hayden