LINQ to Entities: OrderBy is lost when followed by FirstOrDefault by StriplingWarrior

Hello,
Thank you for taking the time to provide feedback. We addressed a lot of connect suggestions in our last release, but we weren’t able to address them all. We have copied this issue to our backlog and will consider it for a future release. You can view this suggestion on our public backlog – http://entityframework.codeplex.com/workitem/list/basic?keywords=DevDiv%20[Id=175920].
~Entity Framework Team

Hello StriplingWarrior,

Thanks again for the feedback and your support of the Entity Framework. We looked at this issue and we realized that it is due to a limitation of the LINQ to Entities logic that performs "OrderBy lifting". When this logic recognizes some specific patterns in the LINQ query we can rewrite the query to lift the OrderBy operations so that they are placed immediately before any order dependent operations such as FirstOrDefault, Skip or Take. By lifting OrderBy iperations in this way, LINQ to Entities can somewhat simulate the behavior of sequences (i.e. the data sources of LINQ to Objects) even if the actual data source does not natively preserve order on composition (relational database don’t preserve order: ORDER BY clauses inside a nested query can be ignored unless they are located at the same level or in the level immediately below order dependent operations).

LINQ to Entities does OrderBy lifting on a best-effort basis and does not try to identify every possible pattern in which a transformation could help preserve the order.

In the case of your query, the FirstOrDefault operation is applied inside a separate Select operation from where the OrderBy is:

var foo = context.MDAFinancialPeriods
     .Where(x => x.FinPeriodYear > 1000)
     .GroupBy(x => x.FinPeriodNo)
     .Select(x => new { Key = x.Key, Entries = x.OrderBy(y => y.FinPeriodDtStart)})
     .Select(x => new { x.Key, NextPeriod = x.Entries.FirstOrDefault() });

LINQ to Entities will not recognize this pattern as one in which it could lift the OrderBy, however if you put the OrderBy and the FirstOrDefault in the same Select, LINQ to Entities will produce the results you expect:

var foo = context.MDAFinancialPeriods
     .Where(x => x.FinPeriodYear > 1000)
     .GroupBy(x => x.FinPeriodNo)
     .Select(x => new { Key = x.Key, NextPeriod = x.OrderBy(y => y.FinPeriodDtStart).FirstOrDefault()});

We would like to improve our pattern matching logic so that we can detect the pattern in the first query, but given there is a workaround we think that the priority of making this improvement for the next release is lower than other improvements we are working on.

If you have any more information on your scenario and if there is any reason you cannot produce a query like the second example, feel free to reply to this thread.

Thanks,
Diego Vega
Entity Framework Team

StriplingWarrior,

Thanks for the feedback! We love it when people use Entity Framework and we're even more excited when they provide feedback. We do apologize for how long it's taken us to get back to this, but we are able to repro and we will do what we can to fix the problem. Thanks for bringing it to our attention!

Thanks for your feedback.

We are rerouting this issue to the appropriate group within the Visual Studio Product Team for

triage and resolution. These specialized experts will follow-up with your issue.

Dear Microsoft,

This bug already caused me to lose a lot of time, and I'm still trying to catch up from that. I did my best to provide enough details that the issue should be fairly simple to reproduce. The sample code I provided only deals with a single table, and only two fields on that table. The types of those fields are relatively obvious. I described the effect I observed on the SQL query that was produced. You should be able to validate this against something like the Northwind database with very little difficulty.

Unfortunately, I most likely won't have the time to put together a demo project to demonstrate this issue any time in the near future. I apologize for any inconvenience this may cause you.

Thank you for submitting feedback on Visual Studio 2010 and .NET Framework. In order to efficiently investigate and reproduce this issue, we are requesting additional information outlined below.

Could you please give us a demo project to demonstrate this issue so that we can conduct further research?

Please submit this information to us within 4 business days. We look forward to hearing from you with this information.

Microsoft Visual Studio Connect Support Team

Thank you for your feedback, we are currently reviewing the issue you have submitted. If this issue is urgent, please contact support directly(http://support.microsoft.com)