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

dynamic-linq entity-framework linq-to-entities objectquery

Question

I have an extension for IQueryable that lets you give in a delimited string of property names; but, doing so prevents the query from creating JOINS, which in turn results in a SELECT N+1 problem.

If I call the native EF extension, I've observed. Everything is good when Include("property") is straight off of DbSet. However, if I use my extension (I even made it simpler by simply calling. It happens when Include("property") SELECT N+1...

My concern is, why? Why am I misusing this?

This is the 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 extension-based repository technique.

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 (which has been condensed only to call Include() to highlight 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;
}
1
4
6/6/2012 6:40:18 PM

Accepted Answer

The way you are utilizing the Include technique and, incidentally, the Where method, in this case, is what I believe to be the core issue. These methods do not alter the object they are called on, as is common with LINQ extension methods. Instead, they provide back a fresh object that symbolizes the query once the operator is used. As an example, in this code:

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

The new query that Include returns is discarded, thus the Include function essentially does nothing. However, consider this:

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

changes the query variable with the new query object obtained from Include after applying Include to the query.

Since the Include is being disregarded and the linked collections are still being loaded using lazy loading even though it is not in the code you supplied, I believe you are still seeing N+1 with your code.

7
6/7/2012 1:21:24 AM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow