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

firstordefault order by linq
tolist orderby
firstordefault vs where
linq orderby
iqueryable orderby
linq orderby ascending
order by in entity framework
vb net order by

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.

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.

OrderBy() / .OrderByDescending() with .FirstOrDefault()/.First(), Last() or OrderByDescending(). same amount of time*, but you're right in thinking that FirstOrDefault is faster than LastOrDefault . (Note that doing a whole sort to take just the top item is far more inefficient than using Last  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.

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!

Is FirstOrDefault/First and OrderByDescending, quicker than , OrderBy().FirstOrDefault() #2521. Closed. TheVanice opened this issue on Jul 2,​  Returns the only element of a sequence, or a default value if the sequence is empty; this method throws an exception if there is more than one element in the sequence. whereas FirstOrDefault from MSDN (presumably when using an OrderBy() or OrderByDescending() or none at all), Returns the first element of a sequence

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.

OrderBy().FirstOrDefault(<condition>) vs. Where(<condition , And what about if listOfItems is empty ? Max() throws an exceptions, then you change to: var i = (from item in listOfItems order by item descending Examples. The following code example demonstrates how to use FirstOrDefault<TSource>(IEnumerable<TSource>, Func<TSource,Boolean>) by passing in a predicate. In the second call to the method, there is no element in the array that satisfies the condition.

To resolve call the ToList() method

LinQ: Max() vs OrderBy()-FirstOrDefault(), FirstOrDefault(). Returns the first element of a sequence, or the first element matching the provided predicate . If the sequence contains no elements, or no  OrderBy sorts the values of a collection in ascending or descending order. It sorts the collection in ascending order by default because ascending keyword is optional here. Use descending keyword to sort collection in descending order. Example: OrderBy in Query Syntax C# IList<Student> studentList = new List<Student> () { new Student

.Where(<condition>).FirstOrDefault() will return a IEnumerable list from your condition, then it gets the first or default value from that list. Where as the second .FirstOrDefault(<condition>) will apply the condition and get the first value that matches your condition. So the two will execute differently

C# Language, FirstOrDefault<TSource>(IEnumerable<TSource>, Func<TSource,Boolean>) firstMonth1); // Setting the default value to 1 by using DefaultIfEmpty() in the  Because IOrderedEnumerable<TElement> inherits from IEnumerable<T>, you can call OrderBy or OrderByDescending on the results of a call to OrderBy, OrderByDescending, ThenBy or ThenByDescending. Doing this introduces a new primary ordering that ignores the previously established ordering. This method compares keys by using the default comparer

Enumerable.FirstOrDefault Method (System.Linq), Use OrderByDecending() method for it. OrderByDescending. OrderByDescending sorts the collection in descending order. OrderByDescending is valid only with  This is an extension of already answered question by Jon Skeet that you can find here.. The desired result is following: A 100 A 80 B 80 B 50 B 40 C 70 C 30

LINQ Single vs SingleOrDefault vs First vs FirstOrDefault, tolist () orderby c# firstordefault sort firstordefault vs where linq orderby order by in mvc firstordefault with where clause in linq linq query with where and order by LINQ - FirstOrDefault() then Select() Ask Question Asked 7 years, 1 month ago. Active 7 years, 1 month ago. Viewed 51k times 27. 3. I have the

OrderBy & OrderByDescending - Sorting Operators, I'm writing a query that uses FirstOrDefault after an OrderBy query, which should check if it isn't null first then use some data in it. Is there a better way than writing it like this: Is there a better way than writing it like this:

Comments
  • Maybe you should react to the answers that have been posted. They have explained the issue, I think.
  • All of this query stays in the IQueryable domain. IEnumerable is not a concern here.
  • Good point i didn't go read up up just spoke off the top of my head. IEnumerable msdn.microsoft.com/en-us/library/vstudio/… does have a implementation for firstordefault though, the where is where my answer was lacking in this respect.