Select N+1 in next Entity Framework

Question

One of the few valid complaints I hear about EF4 vis-a-vis NHibernate is that EF4 is poor at handling lazily loaded collections. For example, on a lazily-loaded collection, if I say:

if (MyAccount.Orders.Count() > 0) ;

EF will pull the whole collection down (if it's not already), while NH will be smart enough to issue a select count(*)

NH also has some nice batch fetching to help with the select n + 1 problem. As I understand it, the closest EF4 can come to this is with the Include method.

Has the EF team let slip any indication that this will be fixed in their next iteration? I know they're hard at work on POCO, but this seems like it would be a popular fix.

Answer 1

What you describe is not N+1 problem. The example of N+1 problem is here. N+1 means that you execute N+1 selects instead of one (or two). In your example it would most probably mean:

// Lazy loads all N Orders in single select
foreach(var order in MyAccount.Orders)
{
  // Lazy loads all Items for single order => executed N times
  foreach(var orderItem in order.Items)
  {
     ...
  }
}

This is easily solved by:

// Eager load all Orders and their items in single query
foreach(var order in context.Accounts.Include("Orders.Items").Where(...))
{
 ...
}

Your example looks valid to me. You have collection which exposes IEnumerable and you execute Count operation on it. Collection is lazy loaded and count is executed in memory. The ability for translation Linq query to SQL is available only on IQueryable with expression trees representing the query. But IQueryable represents query = each access means new execution in DB so for example checking Count in loop will execute a DB query in each iteration.

So it is more about implementation of dynamic proxy.

---

Counting related entities without loading them will is already possible in Code-first CTP5 (final release will be called EF 4.1) when using DbContext instead of ObjectContext but not by direct interaction with collection. You will have to use something like:

int count = context.Entry(myAccount).Collection(a => a.Orders).Query().Count();

Query method returns prepared IQueryable which is probably what EF runs if you use lazy loading but you can further modify query - here I used Count.