Search code examples
linq-to-sqlcompiled-queryloadoptions

Rewrite a LinqToSql query to return same instances without LoadWith

I'm investigating CompiledQuery performance, but CompiledQuery is allergic to LoadWith.

using (CustomDataContext myDC = new CustomDataContext())
{
  DataLoadOptions options = new DataLoadOptions();
  options.LoadWith<Customer>(c => c.Orders)
  myDC.LoadOptions = options;

  IQueryable<Customer> query = myDC.Customers.Where(filterExpr);
  List<Customer> result = query.ToList();
  return result;
}

This code populates the Orders property of each Customer instance loaded by issuing a left join sql query. How can I rewrite the query without LoadWith to get the same result (Customers have populated Orders property)?

Solution

  • I found a way to use DataLoadOptions with CompiledQuery. http://www.mrkwatkins.co.uk/Blog/2010/05/16/Improving-LINQ-To-SQL-Performance-Part-2---Combining-Compiled-Queries-And-Load-Options

    It involves having the instance of DataLoadOptions available at time of compilation, holding on to that DataLoadOptions instance for the lifetime of the CompiledQuery instance, and swapping that DataLoadOption into and out-of-place each time the CompiledQuery is run.