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.

Comments

  • Anonymous
    February 26, 2009
    Hi,what about the following query?from o in ctx.Orders.Include("Customer")where o.customer.IsMoreThan30DaysInArrearsorderby o.OrderDate descendingselect othis will get the orders ordered and the customers altogether.Martin

  • Anonymous
    February 26, 2009
    Martin,Well that is one approach.But it has a few draw backs, the code that uses those results needs to deal with orders that are interleaved.For example it is very easy to imagine, an Order for Customer1, an Order for Customer2 and then an Order for Customer1 again.You could of course iterate over the results and create an enumeration of all the distinct customers in the enumeration of orders and return that.I'm guessing the orders collection for each of those customers will probably be sorted, but I haven't tried that so, it is nothing more than a guess.Alex

  • Anonymous
    February 27, 2009
    Hi,if one wants the Customers be not interleaved then maybe use groupby clause?The goalshaving the orders ordered by OrderDatehaving the Customers consecutive using only Entities doing it with one roundtrip to the store are contradictory, as there is NO direct solution, unless one introduces an (half)order on the Orders navigation property of Customer.As a workaround one can use the groupby clause (introducing sort of a Dictionary<Customer>) or create an anonymous type as You did.Relying on side effects or undocumented behaviour should not be recommended.If one wants the Orders collection be sorted, then feel free with the following statementforeach (var c in lateCustomers){   foreach (var od in (     from o in c.Orders.AsEnumerable()// AsEnumerable will prevent the roundtrip to the store (applying a different extension method)     orderby o.OrderDate descending       select o))   {       // do something with the orders...   }}But be aware, that in this case, the ENTIRE orders collection of EVERY lateCustomer will be duplicated in the loop.One can have its own "OrderBy" by creating extension methods on IQueryable<Order> (which is the closed type of the orders collection)Martin

  • Anonymous
    February 27, 2009
    Martin,Nice use of AsEnumerable()! Good to see that you obviously know your stuff. The rest of your solution looks good.I agree that using implementation side-effects is not wise, and I wasn't recommending relying on side-effects... I thought I made that pretty clear. It was in the extra credit section, not the answer section!Reading the list of goals you percieve makes it clear to me that I didn't spend enough time stating the problem originally.The orders don't need to be 'globally' ordered they need to be ordered for each customer. And for that there is a possible solution. Although there is no guarantees it will work in .NET 4.0 because it is a side-effect of implementation details.Alex.

  • Anonymous
    March 25, 2009
    Hopefully if you're reading this you've noticed that I've started a series of Tips recently. The Tips

  • Anonymous
    April 10, 2010
    Will all the includes be still be intact?i mean say for example if the order has another orderDatails table and customers having an address tableOrders.Include("OrderDetails");Customers.Include("Address");Will the above solution include the above tables along with the Address and OrderDetailsThanks