Tech:
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>();
}
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));