IQueryable and lazy loading

Question

I'm having a hard time determining the best way to handle this... With Entity Framework (and L2S), LINQ queries return IQueryable. I have read various opinions on whether the DAL/BLL should return IQueryable, IEnumerable or IList. Assuming we go with IList, then the query is run immediately and that control is not passed on to the next layer. This makes it easier to unit test, etc. You lose the ability to refine the query at higher levels, but you could simply create another method that allows you to refine the query and still return IList. And there are many more pros/cons. So far so good.

Now comes Entity Framework and lazy loading. I am using POCO objects with proxies in .NET 4/VS 2010. In the presentation layer I do:

foreach (Order order in bll.GetOrders())
{
  foreach (OrderLine orderLine in order.OrderLines)
  {
    // Do something
  }
}

In this case, GetOrders() returns IList so it executes immediately before returning to the PL. But in the next foreach, you have lazy loading which executes multiple SQL queries as it gets all the OrderLines. So basically, the PL is running SQL queries "on demand" in the wrong layer.

Is there any sensible way to avoid this? I could turn lazy loading off, but then what's the point of having this "feature" that everyone was complaining EF1 didn't have? And I'll admit it is very useful in many scenarios. So I see several options:

Somehow remove all associations in the entities and add methods to return them. This goes against the default EF behavior/code generation and makes it harder to do some composite (multiple entity) LINQ queries. It seems like a step backwards. I vote no.
If we have lazy loading anyway which makes it hard to unit test, then go all the way and return IQueryable. You'll have more control farther up the layers. I still don't think this is a good option because IQueryable ties you to L2S, L2E, or your own full implementation of IQueryable. Lazy loading may run queries "on demand", but doesn't tie you to any specific interface. I vote no.
Turn off lazy loading. You'll have to handle your associations manually. This could be with eager loading's .Include(). I vote yes in some specific cases.
Keep IList and lazy loading. I vote yes in many cases, only due to the troubles with the others.

Any other options or suggestions? I haven't found an option that really convinces me.

Roger Johansson Roger Johansson · Accepted Answer · 2010-05-08T17:05:20

You could make your methods accept some sort of load strategy.

Func<ObjectSet<Order>, ObjectQuery<Order>> loadSpan = 
orders=> orders.Include("OrderLines");

foreach (Order order in bll.GetOrders(loadSpan)) 
{ 
  foreach (OrderLine orderLine in order.OrderLines) 
  { 
    // Do something 
  } 
}

And inside your GetOrders method, you do something like

public IList<Oorder> GetOrders(
                     Func<ObjectSet<Order>, ObjectQuery<Order>> loadSpan)
{ 
    var ordersWithSpan = loadSpan(context.OrderSet);
    var orders = from order in ordersWithSpan
                 where ...your normal filters etc

    return orders.ToList();
}

This will allow you to specify entire load graphs per use case. You can ofcourse also wrap those strategies up in some wrapper class so you would write:

//wrapped in a static class "OrderLoadSpans"
foreach (Order order in bll.GetOrders(OrderLoadSpans.WithOrderLines))

HTH

IQueryable and lazy loading

1 Answers