LINQ to SQL DataLoadOptions.LoadWith and Take by ThinqLinq

LINQ to SQL DataLoadOptions.LoadWith and Take

While trying to increase the performance of this site, I found a bug which may drastically slow the performance. By default when navigating to child objects from a parent object, LINQ to SQL lazy loads the children. This is good when you don't know if you want the children.

However, on this site when viewing posts, I ALWAYS display the categories and number of comments. As mentioned in LINQ in Action, you can eager load child records using the context's LoadOptions to set the child to be eagerly loaded with the parent using the following:

Dim dc As New LinqBlogDataContext
Dim options As New DataLoadOptions
options.LoadWith(Function(p As PostItem) p.CategoryPosts)
options.LoadWith(Function(cp As CategoryPost) cp.Category)
options.LoadWith(Function(p As PostItem) p.Comments)
dc.LoadOptions = options

There are a couple issues with the implementation at this point however. First, the LoadWith setting only works for one level of hierarchy. It does not automatically navigate to grandchildren records. In this case, you may need to project into an anonymous type to remove that extra level of the object graph.

The trickier situation comes when trying to do paging over the result sets. When traversing one level, the LoadOptions work fine for standard queries, however as soon as you throw a Take clause in, the LoadWith options are ignored as shown below:

Dim good =  From p In dc.PostItems _
            Order By p.PubDate Descending _
            Select p

Dim bad =   From p In dc.PostItems _
            Order By p.PubDate Descending _
            Take 5
            Select p

In the first case, a single query is sent to the database when navigating to the children. In the second (bad) query, separate statements are sent to the database as we fetch the children. I submitted a bug item on this. The solution here (if you target the 4.0 framework) is to include a Skip(0) clause which will cause the Take to not short circuit the LoadOptions:

Dim fixed = From p In dc.PostItems _
            Order By p.PubDate Descending _
            Skip 0
            Take 5
            Select p

Unfortunately, this trick doesn't work with the current VS 2010 build when targeting 3.5. I suspect that you may need to target 4.0 in order to get Take to play nice with the LoadOptions.

Posted on - Comment
Categories: Visual Studio - LINQ -
comments powered by Disqus