LINQ To SQL - Lazy Loading Properties and Specifying PreFetch When Needed - Sweet Query and Performance Control
by David Hayden ( Microsoft MVP C# ), Filed: LINQ To SQL
I can't tell you the last time I have been so excited about an O/R Mapper. LINQ To SQL continues to amaze me at the control you have over the lazy loading and prefetch of properties and associations.
This is another post in a series of posts on LINQ To SQL in how I think I have discovered a potential performance problem of LINQ To SQL only to find an elegant solution to the problem using LINQ To SQL. If you haven't been reading along with the discovery, here are a few previous posts on LINQ To SQL in this discussion of performance and query tuning:
In the last post, I was talking about the performance problem we may have with LINQ To SQL duplicating data in a single resultset when getting a Blog and prefetching the Categories associated with it:
If we were to have several columns associated with Blog that are fairly heavy, like binary and NVARCHAR(MAX) columns, duplicating the Blog data above could be very expensive. So how then can we avoid the performance impact associated with heavy columns? Why delay load ( lazy load ) them of course. In the example below, I have specified that the Title Column ( although not heavy ) be lazy loaded for testing reasons via the Visual Designer:
Remember we are pretending Title is another property that may be real heavy, like a Description Column that may contain a lot of data and be specified as NVARCHAR(MAX).
Now that Visual Designer changes the Title Field from a basic string field:
private string _Title;
to the following decaration which obviously has lazy loading characteristics that we will talk about later:
private System.Data.Linq.Link<string> _Title;
This means the Blog.Title is lazy loaded, which means it won't be duplicated above and could really help with performance. Thus, for heavy fields you may want to specify they are always delay loaded.
Of course, if we want them to be prefetched, we write the very common code to prefetch Categories and now Title when we need them:
using (BlogDataContext context = new BlogDataContext())
{
DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Blog>(c => c.Categories);
options.LoadWith<Blog>(c => c.Title);
context.LoadOptions = options;
Blog blog = context.Blogs.Single<Blog>(c => c.BlogId == 1);
}
Freaking brilliant!
Tutorials By David Hayden ( Microsoft MVP C# )
Filed: LINQ To SQL