Tip 1 - How to sort Relationships in Entity Framework

Question:

One of the questions you often see in the Entity Framework Forums is around sorting related items.

For example imagine if you want to query customers, and return only those that have accounts more than 30 days in arrears, and at the same time retrieve their orders.

And you need those orders sorted by Order date, so you can see the most recent orders first, which allows you to look for things like suspicious patterns easily.

Answer:

Most of you probably know that the EF can eagerly load a relationship using Include(). For example something like this:

var lateCustomers = from c in ctx.Customers.Include("Orders")
                    where c.IsMoreThan30DaysInArrears
                    select c;

But unfortunately this will return each customer's orders in random order.

So how do you sort them? Well strictly speaking there is no answer.

But there are a few little things you can do, like do the include in the select clause:

var lateCustomers = from c in ctx.Customers
                where c.IsMoreThan30DaysInArrears
                select new {
       Customer = c,
                    Orders = c.Orders.OrderByDescending(
o => o.OrderDate
)
                }; 

What this does is use the standard LINQ semantics to ask for an enumeration of anonymous types, which are made up of two things: the Customer and a sorted copy of the Customer's Orders. 

The Entity Framework supports this just fine, so problem solved...

Bonus Credit:

... well sort of. 

I'm not using my entities to get to the data, I'm using something else, an anonymous type.  

Which is not really ideal.  

You really want to access the customers orders through the customer.Orders property. Interestingly though there is something else going on here, namely Fix-up.  

In the Entity Framework Object Services automatically ties things together that are related in a process called Fix-up.  

Fix-up generally occurs once the second of two related entities enter the context. 

So because I've loaded both the Customer and the Orders (via the projection), Fix-up will ensure that the customer.Orders property will contain those orders too. And this is despite the fact that I didn't do an Include("Orders") in my query. 

Which begs the question: Will Customer.Orders be sorted too? 

Unfortunately the official answer is no.  

This is not something we support, we haven't spent time the necessary time testing it, making sure it works in every scenario. You know all that important QA stuff. 

But here is the kicker, it seems to work anyway... 

Why? 

My guess is that it is a side effect of the ordering in the query and how the code is written that materializes entities from the underlying DataReader and finally does Fix-up.  

Now in theory you can use this behavior to your advantage... but, and this is a big BUT, please understand this isn't a recommendation.   

Relying on implicit side effects of a particular implementation is always a little risky.