Linq Order Subtitled

Question

My question is how to sort a Linq query by subcategory:

Applications in the table:

- app_id
- name

AppStatus table:

 - app_status_id
 - app_id
 - severity
 - status_date

I would like to have a query with all applications sorted by latest status:

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 someone help me with LINQ query for this.

I have already tried the following, but this did not work:

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

Edit:

I will clarify my question, he must first receive all applications with the most recent status (therefore, by status date), then he should order this list according to the severity of this last status.

Answer 1

Try the following:

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 the sort:

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