EF Core 2.0 - filtering IQueryable navigation property collection empty

Question

EF Core 2.0 - filtering IQueryable navigation property collection empty

asp.net-mvc entity-framework entity-framework-core

Question

Tech:

EF Core 2.0.0
Asp.Net Core Mvc 2.0.0

When i execute this method it throws "InvalidOperationException: Sequence contains no matching element" on evaluating CurrentGrade. Why does it throw and how can I fix it?

I have a search method filtering on quite a lot of properties on a large dataset (10 000 users with thousands of related entities). I'm trying to optimize the query and i don't want to execute the query until all filtering has been made. While using ToList() makes the method work i would rather work against an IQueryable and execute the query when done filtering.

I'm pretty sure this worked before updating EF Core from 1.x to 2.0.

  public MemberQueryResult Search(MemberQuery filter)
        {
            var query = Context.Users
                .Include(x => x.Honours)
                .Include(x => x.Grades)
                .Include(x => x.Strokes)
                .Include(x => x.Posts)
                .Include(x => x.Loge)
                .AsNoTracking();

            query = query.ApplyFiltering(filter);

            return result;
        }

ApplyFiltering() works well for filtering on foreign keys but when filtering on a navigation property collection using .Where() it throws on ICollection Grades on Member when just before the filtering it was included.

This is the method inside ApplyFiltering() that throws:

  private static IQueryable<Member> SearchByCurrentGradeRange(MemberQuery filter, IQueryable<Member> result)
    {
        if (filter.GradeRange == null) return result;

        var gradeRange = filter.GradeRange.Split(',');
        var gradeFrom = (Grade)int.Parse(gradeRange[0]);
        var gradeTo = (Grade)int.Parse(gradeRange[1]);

        result = result.Where(x => x.CurrentGrade >= gradeFrom && x.CurrentGrade <= gradeTo);

        return result;
    }

CurrentGrade is a calculated property on a member, Grade is just an enum.:

    public sealed class Member : IdentityUser
{
        public Grade CurrentGrade => Grades.OrderBy(x => x.Grade).Last(x => x.ReceivedDate != null).Grade;

        public ICollection<MemberGrade> Grades { get; set; } = new Collection<MemberGrade>();

}

Lindeberg

View more on Stack Overflow

Ivan Stoev · Accepted Answer · 8/26/2017 8:07:07 AM

The problem is that the unmapped ("calculated") property is causing client evaluation, but at the time EF evaluates the client part of the Where clause, the navigation properties are not loaded yet, hence your Grades collection is empty (as it has been iinitialized with new Collection<MemberGrade> - if you remove the initializer, then you'll get NullReferenceException).

Now, probably it could be treated as EF Core bug. But I strongly recommend not using unmapped properties in LINQ queries in general, and especially in query filter conditions. Even if they work, the client evaluation will cause loading a lot of data in memory just to apply the filter there, rather than at the database (SQL) side.

Also make sure to use SQL translatable constructs. For instance, Last / LastOrDefault have no natural SQL translation, while FirstOrDefault does, so the usual pattern is OrderByDescending().FirstOrDefault() rather than OrderBy().LastOrDefault().

With that being said, the working server side evaluating solution in your case would be like this:

result = result.Where(m => m.Grades
    .Where(x => x.ReceivedDate != null).OrderByDescending(x => x.Grade).Take(1)
    .Any(x => x.Grade >= gradeFrom && x.Grade <= gradeTo));

EF Core 2.0 - filtering IQueryable navigation property collection empty

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related