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

I have an extension on IQueryable that allows passing in delimited string of property names which, when used causes query not to construct JOINs and effectively causes SELECT N+1 issue.

What I noticed is that if I call native EF extension .Include("property") directly off of DbSet everything works fine. But if I use my extension (I even simplified it to just call .Include("property") SELECT N+1 occurs...

My questions is why? What am I doing wrong?

Here is calling method (from 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 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 (reduced just to call Include() to illustrate the issue)

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;
}

I think the fundamental problem here is in the way you are using the Include method, and also incidentally, the Where method. These methods, as is typical with LINQ extension methods, do not modify the object that they are called on. Instead they return a new object which represents the query after the operator 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 query returned by Include is thrown away. On the other hand, this:

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

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

It's not in the code you have posted, but I think you are still seeing N+1 with your code because the Include is being ignored and the related collections are therefore still being loaded using lazy loading.

Recommended topics

Hot tags