LINQ to SQL Tips 3: Deferred (lazy) or eager loading of related objects with stored procs

  • Article

This post is a confluence of two distinct sets of comments I got:

  1. The above-mentioned feature is a well-hidden secret; and
  2. I post code only in C# while there is a big community of VB users who like LINQ to SQL

So here we go ...

First, the MSDN docs on this topic are a bit too terse and don't describe the purpose of LoadXyz as well as they could. That is perhaps the result of clubbing too many features in one small topic.

Stored procedures can be used for any of the following purposes:

  1. Queries for objects
  2. Insert, Update, Delete operations
  3. Deferred or eager loading of related objects (i.e. indirectly queried objects based on navigation)

The support for the first is quite well known as the designer exposes that feature quite well. Drag and drop a sproc on the right hand pane and you get a method wrapping the sproc. That works for single or multiple results returned.

The support for the second group takes a little more work in the designer as you have to configure the behavior and associate parameters. This is also covered in the documentation topic mentioned above.

That leaves the most interesting feature. Many ORMs support the first two categories above but when it comes to loading of related objects (e.g. Products for a given category object), you often need dynamic SQL. But you don't want to lose the power of the dot just because your DBA does not allow dynamic SQL! You still want to go from a category to its products collection and be able to see the products loaded. Plus ideally this should work for both eager and deferred (or lazy) loading. That is the purpose of the LoadXyz methods. Here is a VB sample that shows how to use them:

In your NorthwindDataContext partial class, just add the following (see comments for assumed sprocs)

    ' Override loading of Category.Products by using method wrapper. Assume method named CategoryProducts that wraps a sproc

    Private Function LoadProducts(ByVal category As Category) As _

        IEnumerable(Of Product)

        Return Me.CategoryProducts(category.CategoryID)

    End Function

    ' Override loading of Product.Category by using method wrapper. Assume method named CategoryById that wraps a sproc

    Private Function LoadCategory(ByVal product As Product) As Category

        Return Me.CategoryById(product.CategoryID).Single()

    End Function

Now, somecategory.Products and someProduct.Category will work without the need for dynamic SQL and with eager and lazy loading. (BTW, these LoadXyz methods are not intended for top level query - for that all you need to do is call a method wrapping a sproc or write a query that goes against the relevant TVF instead of the table).

 I really like this feature, do you?

Update 1 (5/5/08): If you like tricks, not just tips and if you are not faint of the heart, then you must check Matt's latest posting about mockable DataContext . Even if you don't care about TDD, mocking etc., it is a good reminder of how certain mechanisms can be repurposed for new and bizarre (in a good way) goals.