How to include one specific row from another table with LINQ to Entities

Question

I have a database with these two tables: Customer and CustomerStatus. CustomerStatus is a so called readonly table. All changes to a customer's status result in an INSERT into that table. The current customer status is locatable via CustomerStatus.StatusTimestamp.

Now I'm looking for a LINQ to entities query that loads all Customers together with their current status. Something like

.Include("CustomerStatus.OrderByDescending(StatusTimestamp).FirstOrDefault()").

I haven't found a proper way with with EF 4.0.

My current workaround is very ugly and uses 2 steps:

step 1: LINQ query that loads all status (in the business/data layer):

var r = from c in db.Customers.Include("CustomerStatus") select c;

step 2: take the suitable status from each customer in the GUI (in a loop in a MVC3 view):

c.CustomerStatus.OrderByDescending(i => i.StatusTimestamp).FirstOrDefault()

Any idea how this can be done directly in one step?

Answer 1

EF is more powerful than you give it credit for. One of the things it can do is project to complex types within an IQueryable (ie, in one database hit). This should work:

var data = 
    from c in db.Customers
    select new { 
        Customer = c, 
        LastStatus = c.CustomerStatus  // assuming navigation property set up right
                      .OrderByDescending(s => s.StatusTimestamp)
                      .FirstOrDefault()
    };

Now data is an IQueryable<anonymous_type>, and each item has a Customer property giving the customer, and a LastStatus giving the latest-timestamped status, or null if there aren't any status records for the customer.

You can use a named type here too, it's just quicker for me to type it this way :).