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

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?

Answers


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 :).


Need Your Help

Proper way to implement async tasks for simple API calls? (android)

android android-asynctask

I 'm a new programmer working on my first android app. I ran into a "android.os.NetworkOnMainThreadException," did some research and realised I need to do network related stuff using async. However...

XPath selector by class AND index

xpath

I have the following HTML: