Thanks EF4. Now i can put my orderby clause anywhere

Well most folks wouldn’t have a clue as to what the title of my posting says. This is a problem that stems from EF v1  but is now solved for the most part. in EF4.0. In version 1 of EF, it was recommended that you put your order by operation as the last operation in the query. If you do not put order by as the last operation in the query query, order by would be ignored in some cases.

In the code below we are returning products sorted by their unit price. Notice in our query, order by is not the last operation on the query but our results still come back sorted by the UnitPrice.

SQL query for the above linq query.

In the above sql generated, the order by clause is present.

Now let’s look a a query where order by is not preserved in EF v1.

the sql for the above linq query does not contain any order by clause as shown below.

Let me explain why the order by got lost in the above query. Generally, order by operation applies to the next operation in the query. If the next operation is a filtering operation which does not care about the ordering of the results, EF silently ignores the order by clause.  Like in the above case our orderby operation is followed by a where clause and therefore EF ignored it. However if you look at the first query order by works fine because it is a select operation which is a valid operation to perform after an order by. Code below shows how we rewrite the query such that order by is performed after the where clause.

I do want to mention to readers that this problem is not only related to where clause, it can also be found when you have nested from clauses, oftype operator. Pretty much anything that changes the output  of the query. For instance you apply an order by on products entity and then you output categories.

In EF4, this problem is solved by use of OrderBy lifting where EF on your behalf automatically moves the order by clause as the last operation in the query.  So on what type of queries would you see this as actually fixed. In cases where you are ordering and then filtering EF will apply the ordering after the filtering. Code below shows an example that works in EF4.0 along with its sql query.

Notice our linq query contains order by clause before the filter but the EF4 generates the right sql for us.

If you read my title, i did also mention that in some cases it is still not fixed. Specially when you change the output of the query. Below is an example of linq query where order by operation is still not preserved.

The sql for the above query is shown below.

In the above case order by was ignored. One would argue why it was ignored? Well the problem i think with the above query is use of nested from clause. At first you do from orders and order the results by shipcity and then you go and run another from clause with OrderDetails. With another from clause there is no guarantee that outer most project would be orders, it could be order details like for instance you could do this.

from o in db.Orders
orderby o.ShipCity
from od in o.Order_Details
where od.Product.Supplier.City == “London”
select od;

Notice in this case, i am actually returning OrderDetails and not the original Orders entity which has the ShipCity column that i sorted by. If EF would have kept the order by in this query, it would not make sense because you are not even returning orders, you are returning OrderDetails. This is the reason why in the case above where i am actually returning orders, order by was ignored. This is because with nested from clause, there is not guarantee which object will be returned as part of the select statement. Thus EF silently ignores it.

In short, just remember that EF in some cases will store ignore your order by clause unless you do it at the end. So why don’t you make it a habit of applying ordering as the last operation.