Rob's Not So Lazy MVC Storefront

Wednesday, May 21, 2008

Rob ran into some lazy load problems in his MVC Storefront and later proclaimed:

"…if you set any Enumerable anything as a property, it's Count property will be accessed when you load the parent object. This negates using any deferred loading for any POCOs, period"

Rob thought this was a problem with .NET in general, but I was suspicious. Veeery suspicious. I downloaded Rob's latest bits and found some interesting behavior.

Based on the screen shot of the call stack that Rob posted, it appeared LINQ to SQL was doing some type conversions. If you poke around the classes mentioned in the call stack, you'll eventually wander into a GenerateConvertToType method that uses LCG to build dynamic methods. Just based on the opening conditional logic, I thought Rob might solve his problem by using LazyList<T> for his business object properties, too (whether or nor he'd want to is a different question), so I modified his Category class for a few experiments to see what would really lazy load.

public class Category {

    // rob's original

    public IList<Product> Products { get; set; }

    // experimental

    public LazyList<Product> ProductsLazy { get; set; }

    public IQueryable<Product> ProductsQueryable { get; set; }

    public IEnumerable<Product> ProductsEnumerable { get; set; }

    // ...

This was in hopes that LINQ to SQL wouldn't feel compelled to do a conversion via List<T>. I just needed to tweak the query to set all four properties.

var result = from c in db.Categories

 join cn in culturedName on c.CategoryID equals cn.CategoryID

 let products = from p in GetProducts()

                              join cp in db.Categories_Products 

                                on p.ID equals cp.ProductID

                            where cp.CategoryID == c.CategoryID

                            select p

              select new Category

              {

                 ID = c.CategoryID,

                 Name = cn.CategoryName,

                 ParentID = c.ParentID ?? 0,

                 Products = new LazyList<Product>(products),

                 ProductsQueryable = products,

                 ProductsEnumerable = products.AsEnumerable(),

                 ProductsLazy = new LazyList<Product>(products)          

 };

             return result;

This experiment failed in a stunning fashion, because none of the Product properties lazy loaded – they all eagerly populated themselves full of real product objects. Hmmm.

Slight Detour

Watching SQL Profiler, I started to wonder why there were soooo many queries running. Sure, the stuff wasn't lazy loading but the queries were flying by quicker than eggs at a Steve Ballmer talk. Yet, the code that was kicking off the whole process was just looking for a single category:

Category result = _repository.GetCategories()

.WithCategoryID(id)

.SingleOrDefault();

That problem turned out to be in Rob's WithCategoryID extension method.

public static IEnumerable<Category> WithCategoryID(

                                     this IEnumerable<Category> qry, 
    int ID) {

    return from c in qry

           where c.ID == ID

           select c;

}

By taking an IEnumerable<T> parameter, the extension method was forcing the query to execute and then doing all the ID checks using LINQ to Objects. Just switching over to IQueryable<T> made the method a lot more efficient, and the number of queries came down tremendously.

Correlating Problems

Back to the original problem, which was a bit of a mystery because I've been able to lazy load collections using IEnumerable<T> and IQueryable<T>. After some more fiddling, I began to suspect the query itself. The query uses a correlated subquery by virtue of the fact that the range variable c is used inside the query for products (c.CategoryID). I'm guessing that LINQ to SQL felt compelled to take care of all the work in one fell swoop. Instead of using a subquery, I presented LINQ to SQL with a method call that pushed the needed parameter (c.CategoryID) onto the stack, and made things slightly more readable in the process.

       var result = from c in db.Categories

                      join cn in culturedName 
    
                        on c.CategoryID equals cn.CategoryID

                    let products = GetProducts(c.CategoryID)

                    select new Category

                    { 

                        ID = c.CategoryID,

                        Name = cn.CategoryName,

                        ParentID = c.ParentID ?? 0,

                        Products = new LazyList<Product>(products),

                        ProductsQueryable = products,

                        ProductsEnumerable = products.AsEnumerable(),

                        ProductsLazy = new LazyList<Product>(products)                     

                    };

       return result;

   }

   public IQueryable<Product> GetProducts(int categoryID)

   {

       var products = from p in GetProducts()

                      join cp in db.Categories_Products on p.ID equals cp.ProductID

                      where cp.CategoryID == categoryID

                      select p;

       return products;

   }

And voila! Three of the properties (ProductsQueryable, ProductsEnumerable, ProductsLazy) would lazy load their Products from the database. Only the original IList<Product> property would eagerly fetch data. From what I can decipher in the grungy code, when LINQ to SQL sees it needs to assign to an IList<T>, and it doesn't have an IList<T>, it eagerly loads a new List<T> and copies those elements into the destination. At least, that's my theory.

Knowing what I know now, I could tell Rob to stick with IList<T> as his property type, but to make sure he has IList<T> on both sides of the assignment in his projection (and tuck the product query into a method call). In other words, use the following to create the LazyList<T> - LINQ to SQL won't load up Products during some wierd type conversion:

public class LazyList<T> : IList<T> {

public static IList<T> Create(IQueryable<T> query)

{

        return new LazyList<T>(query);

}

    // ...

Conclusion? Beware of mismatched types, particularly with IList<T>, and watch out for eager execution with correlated subqueries.

All My Pluralsight Courses

OdeToCode by K. Scott Allen