Linq Order By a subtable

Question

Hello,

My question is how to sort a Linq query by a sub table:

Table Apps:

- app_id
- name

Table AppStatus:

 - app_status_id
 - app_id
 - severity
 - status_date

I would like to have a query with all the apps, sorted by the last status severity:

app_id  name
1   first
2   second
3   third

app_status_id   app_id  severity    status_date
1   1   5   12-4-2010
2   1   2   15-4-2010
3   2   7   10-4-2010
4   3   3   13-4-2010

Now i want it sorted like:
app_id  name
3   third
1   first
2   second

Can anyone help me with a LINQ query for this.

I tried the following already, but that didn't work:

    var apps = from apps in dc.Apps
                         orderby apps.AppStatus.LastOrDefault().severity
                         select apps;

Edit:

I'll refine my question, it should first get all the apps with the latest status (so by date of the status), then it should order that list by the severity of that last status.

Answer 1

Try this:

var apps = from apps in dc.Apps
           orderby apps.AppStatus.Max(a => a.severity)
           select apps;

Edited based on your comment:

var apps = from apps in dc.Apps
           where apps.AppStatus.Count() > 0 // to prevent null reference below
           orderby apps.AppStatus.Where(a1 => a1.status_date ==
               apps.AppStatus.Max(a2 => a2.status_date)
           ).Single().severity
           select apps;

Answer 2

Try joining then sorting:

var apps = from a in dc.Apps
           join s in dc.AppStatus on a.app_id == s.app_id
           orderby s.severity
           select a;

Answer 3

from app in myDC.Apps
let severity =
(
  from status in app.AppStatus
  orderby status.StatusDate descending
  select status.Severity
).First()
order by severity
select app