DbSet <T> .Include () calls SELECT N + 1 when used in the extension method

Question

I have an extension for IQueryable that allows you to pass property names on the separator string that, when used, cause the query not to create JOINs and effectively cause SELECT N + 1 problem.

I noticed that if I call the native extension EF.Include ("property") directly from DbSet, everything works fine. But if I use my extension (I even simplified it to just call .Include ("property") there is a SELECT N + 1 ...

My questions are why? What am I doing wrong?

This is where the method is called (from the service)

public MyModel[] GetAll(int page, out int total, int pageSize, string sort, string filter) { return _myModelRepository .Get(page, out total, pageSize, sort, filter, "PropertyOnMyModelToInclude") .ToArray(); } 

Here is the repository method that uses the extension

 public virtual IQueryable<T> Get(int page, out int total, int pageSize, string sort, string filter = null, string includes = null) { IQueryable<T> query = DatabaseSet; if (!String.IsNullOrWhiteSpace(includes)) { //query.IncludeMany(includes); // BAD: SELECT N+1 //query.Include(includes); // BAD: SELECT N+1 } if (!String.IsNullOrWhiteSpace(filter)) { query.Where(filter); } total = query.Count(); // needed for pagination var order = String.IsNullOrWhiteSpace(sort) ? DefaultOrderBy : sort; var perPage = pageSize < 1 ? DefaultPageSize : pageSize; //return query.OrderBy(order).Paginate(page, total, perPage); // BAD: SELECT N+1 (in both variations above) //return query.IncludeMany(includes).OrderBy(order).Paginate(page, total, perPage); // BAD: SELECT N+1 return query.Include(includes).OrderBy(order).Paginate(page, total, perPage); // WORKS! } 

Here is the extension (only for Include () call to illustrate the problem)

 public static IQueryable<T> IncludeMany<T>(this IQueryable<T> query, string includes, char delimiter = ',') where T : class { // OPTION 1 //var propertiesToInclude = String.IsNullOrWhiteSpace(includes) // ? new string[0] // : includes.Split(new[] {delimiter}, StringSplitOptions.RemoveEmptyEntries).Select(p => p.Trim()).ToArray(); //foreach (var includeProperty in propertiesToInclude) //{ // query.Include(includeProperty); //} // OPTION 2 //if (!String.IsNullOrWhiteSpace(includes)) //{ // var propertiesToInclude = includes.Split(new[] { delimiter }, StringSplitOptions.RemoveEmptyEntries).AsEnumerable(); //.Select(p => p.Trim()); // propertiesToInclude.Aggregate(query, (current, include) => current.Include(include)); //} // OPTION 3 - for testing query.Include(includes); return query; } 

Answer 1

I think the main problem here is how you use the Include method, and also, by the way, the Where method. These methods, as is typical of LINQ extension methods, do not change the object they are called onto. Instead, they return a new object that represents the request after the statement has been applied. So, for example, in this code:

 var query = SomeQuery(); query.Include(q => q.Bing); return query; 

The Include method basically does nothing, because the new request returned by Include is discarded. On the other hand, these are:

 var query = SomeQuery(); query = query.Include(q => q.Bing); return query; 

applies Include to the query, and then updates the query variable with the new query object returned from Include.

This is not in the code you posted, but I think you still see N + 1 with your code, because Include is ignored, and related collections are still loaded using lazy loading.