OrderBy().FirstOrDefault(<condition>) vs. Where(<condition>).OrderBy().FirstOrDefault()

Question

Im using EntitiyFramework 6.1.3 with SQL Server in a C# Project. I've two queries which basically should do the same.

1.

Exams.GroupBy(x=>x.SubjectID).Select(x => x.OrderBy(y => y.Level.NumericValue).FirstOrDefault(y => y.Date.Value > DateTime.Today))

2.

Exams.GroupBy(x=>x.SubjectID).Select(x => x.Where(y => y.Date.Value > DateTime.Today).OrderBy(y => y.Level.NumericValue).FirstOrDefault())

However, the result of the first query is the same as I would leave the order by away. The second one works as expected.

When I look at the generated SQL via Linqpad there is no order by clause in the first query.

My question is, why does the first query not work like the second query? I always thought

.Where(<condition>).FirstOrDefault() == .FirstOrDefault(<condition>)

like already answered here: .Where(<condition>).FirstOrDefault() vs .FirstOrDefault(<condition>)

EDIT: I played around a little more and I've found out that these two queries produce the same SQL output.

Exams.GroupBy(x => x.SubjectID).Select(x => x.FirstOrDefault(y => y.Date.Value > DateTime.Today))
Exams.GroupBy(x => x.SubjectID).Select(x => x.OrderBy(y => y.Level.NumericValue).FirstOrDefault(y => y.Date.Value > DateTime.Today))

Even though it looks like a bug I'm still not 100% convinced.

Answer 1

Even though the related bug report was closed as being answered here on SO, this is clearly a bug. I ran into this exact same issue using a similar construction of an OrderByDescending followed by FirstOrDefault with predicate, within a GroupBy.

It appears that Entity Framework does not support a FirstOrDefault statement with a predicate, at least not in the context of this question. As the question states, the following expression yields a correct SQL query with an ORDER BY clause:

Exams
    .GroupBy(x => x.SubjectID)
    .Select(x => x
        .Where(y => y.Date.Value > DateTime.Today)
        .OrderBy(y => y.Level.NumericValue)
        .FirstOrDefault())

However, adding any predicate to the FirstOrDefault statement confuses EF and results in a query without ORDER BY:

Exams
    .GroupBy(x => x.SubjectID)
    .Select(x => x
        .Where(y => y.Date.Value > DateTime.Today)
        .OrderBy(y => y.Level.NumericValue)
        .FirstOrDefault(y => true)) // Tautology shouldn't have any effect, but it does!

The only way to work around this is splitting the FirstOrDefault(predicate) statement into Where(predicate).FirstOrDefault(). Just make sure to add a comment to your code explaining this decision, because ReSharper correctly suggests using a single FirstOrDefault(predicate) statement. But doing so will bug your query!

Answer 2

Not sure if that's the root cause of the problem, but there is a subtle difference between those two queries - the relative position of OrderBy and Where. In relational world, filter invalidates sorting done in the subquery. EF tries to compensate for this discrepancy by "lifiting" some of the orderbys after the filter, and perhaps is unable to lift this pattern properly. Try switching OrderBy and Where around in the second query:

Exams.GroupBy(x => x.SubjectID).Select(x => x.OrderBy(y => y.Level.NumericValue).Where(y => y.Date.Value > DateTime.Today).FirstOrDefault())

and see if you still get the same generated SQL as unaltered version. If you do, it's definitely a bug in EF (I expect generated sql to be identical to query 1 and different than original query 2). Otherwise it may just be the limitation of the order by lifting algorithm.

In general, if you want to make it easier for EF - it is better to place your OrderBy-s after your Where-s.

Answer 3

The answer to this question depends on the LINQ provider, here EF. The specific patterns that a LINQ provider supports are rarely documented completely. I am not aware of any EF documentation on this issue. It is unclear whether this is a bug or whether it is supposed to work.

The LINQ technology itself is not able make any guarantees that this would always work.

I think this is supposed to work, or at least highly desirable that it works. Create a short Github issue pointing to this question. The team is very responsive.

For the time being you have no choice but to choose the pattern that works.