How to sort related entities with eager loading in ADO.NET Entity Framework

Question

Greetings,

Considering the Northwind sample tables Customers, Orders, and OrderDetails I would like to eager load the related entities corresponding to the tables mentioned above and yet I need ot order the child entities on the database before fetching entities.

Basic case:

var someQueryable = from customer in northwindContext.Customers.Include("Orders.OrderDetails") 
select customer;

but I also need to sort Orders and OrderDetails on the database side (before fetching those entities into memory) with respect to some random column on those tables. Is it possible without some projection, like it is in T-SQL? It doesn't matter whether the solution uses e-SQL or LINQ to Entities. I searched the web but I wasn't satisfied with the answers I found since they mainly involve projecting data to some anonymous type and then re-query that anonymous type to get the child entities in the order you like. Also using CreateSourceQuery() doesn't seem to be an option for me since I need to get the data as it is on the database side, with eager loading but just by ordering child entities. That is I want to do the "ORDER BY" before executing any query and then fetch the entities in the order I'd like. Thanks in advance for any guidance. As a personal note, please excuse the direct language since I am kinda pissed at Microsoft for releasing the EF in such an immature shape even compared to Linq to SQL (which they seem to be getting away slowly). I hope this EF thingie will get much better and without significant bugs in the release version of .NET FX 4.0.

Answer 1

You are confusing two different problems. The first is how to materialize entities in the database, the second is how to retrieve an ordered list. The EntityCollection type is not an ordered list. In your example, customer.Orders is an EntityCollection.

On the other hand, if you want to get a list in a particular order, you can certainly do that; it just can't be in a property of type EntityCollection. For example:

from c in northwindContext.Customers
orderby c.SomeField
select new {
    Name = c.Name,
    Orders = from o in c.Orders
             orderby c.SomeField
             select new {
                SomeField = c.SomeField
             }
}

Note that there is no call to Include. Because I am projecting, it is unnecessary.

The Entity Framework may not work in the way you expect, coming from a LINQ to SQL background, but it does work. Be careful about condemning it before you understand it; deciding that it doesn't work will prevent you from learning how it does work.

Answer 2

Actually I have Tip that addresses exactly this issue.

Sorting of related entities is not 'supported', but using the projection approach Craig shows AND relying on something called 'Relationship Fixup' you can get something very similar working:

If you do this:

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

foreach(var anon in projection )
{
   anon.Orders //is sorted (because of the projection)
   anon.Customer.Orders // is sorted too! because of relationship fixup
}

Which means if you do this:

var customers = projection.AsEnumerable().Select(x => x.Customer);

you will have customers that have sorted orders!

See the tip for more info.

Hope this helps

Alex

Answer 3

Thank you both. I understand that I can use projection to achieve what I wanted but I thought there might be an easy way to do it since in T-SQL world it's perfectly possible with a few nested queries (or joins) and order bys. On the other hand seperation of concerns sounds reasonable and we are in the entity domain now so I will use the way you two both recommended though I have to admit this is easier and cleaner to achieve in LINQ to SQL by using AssociateWith.

Kind regards.