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.


Comments
Benny Thomas Wednesday, May 21, 2008
Nice work done here!
Andy Wednesday, May 28, 2008
Hi, off topic for this blog but I found your J# eval code from google and I just wanted to say a very large thank you. It's made my life SO much easier.

Again, Thank you so very much.
Comments are now closed.
by K. Scott Allen K.Scott Allen
My Pluralsight Courses
The Podcast!