Filtering and Ordering Include

Lot of times in your application, you have a requirement to eagerly load related collection but you want to load the collection partially and also have the data sorted when before it is materialized at the client layer. Currently in EF, there is no direct way to filter and sort and Include but there is a workaround. In this blog post, I will show you how you can use anonymous type to paritally load a collection in the correct order you desire.

Suppose you have a model consisting of Customers and Order as shown below.

You want to retrieve a customer and its related orders. However you only want to retrieve orders which have freight greater then 10 dollars and order the orders collection by Ship Date. Code below shows the query we need.

var db = new NorthwindEntities();
var custs = from c in db.Customers
            where c.CustomerID == "ALFKI"
            select new
            {
                Customer = c,
                Orders = c.Orders.Where(o => o.Freight > 10.0M).OrderBy(o => o.ShippedDate)
            };
var cust = custs.FirstOrDefault().Customer;

foreach (var order in cust.Orders)
{
    Console.WriteLine("OrderID:{0} Freight:{1} Ship Date:{2}",
        order.OrderID,order.Freight,order.ShippedDate);
}

In the code above, we are creating an anonymous type with two properties Customer and Orders. On the Orders property, I am sorting and filtering the orders collection. When we materialize the above query using FirstOrDefault, EF actually sees that orders loaded in the context points to the customer that also exist in the context, so it fixes the object graph for us, giving us the same behavior which an Include would give us.

We hope that next version of EF, would give us the ability to filter,sort and do other stuff on related collection before it gets materialized to the client.