OrderBy().FirstOrDefault() vs. Where().OrderBy().FirstOrDefault()

entity-framework-6 linq-to-sql sql-server

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.

1
5
5/23/2017 11:51:26 AM

Accepted Answer

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.

1
7/2/2015 4:04:36 PM

Popular Answer

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!



Related Questions





Related

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