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.

Does this occur with the `Any()` operator as well? A predicateless `Any` returns if the collection is non-empty. — Femaref, Feb 21 '11 at 19:02
Yes, looking at SQL profiler, it pulls the collection down with any `Any` call. — Adam Rackis, Feb 21 '11 at 19:08

score 18 · Accepted Answer · edited Jul 12 '21 at 18:57

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.

I've seen this situation referred to as "select N+1", but I'll take your word for it that that usage is non-standard. Still though, the ability to intelligently execute `Count` or `Any` queries on lazily loaded collections (*without eagerly loading them*) seems like a handy feature which already exists in NH. I was mainly asking to see if the EF has said anything about whether this will be supported in the next release — Adam Rackis, Feb 21 '11 at 20:06
@Adam: I guess you should ask EF team directly. Try it here: http://blogs.msdn.com/b/adonet/ — Ladislav Mrnka, Feb 21 '11 at 20:09
@Ladislav - also, I was wrong. I was using N+1 incorrectly. Mia Culpa :) +1 - thank you — Adam Rackis, Feb 21 '11 at 20:18
Why do all of the n+1 examples on the Internet use `foreach`? Surely the problem exists in Linq, but if it doesn't, then is it really a problem at all? — Robert Harvey, Jan 16 '15 at 22:02

Select N+1 in next Entity Framework

1 Answers1

Linked

Related