LinqToEntities code for Query needing one entity returned foreach root node?

Question

I think I have got myself into a muddle with this one. My database looks like:

Locations [root]
    Inspections [level1]
       InspectionItems [level2]
           InspectionReadings [level3]      
    Areas [level1]
       Inspections [level2]
           InspectionItems [level3]
               InspectionReadings [level4]

each table is linked by Guid to the previous table and the Inspections table has nullable AreaID so that it can be a direct child of Locations.

What I need is

for each Location 
    {take all InspectionReadings entities
     where location == location
     sort date descending 
     return the top Entity details}
add the Location details and the InspectionReading details into a new table
return new table

the result should be a datagrid with a list of locations and their latest inspection reading date. each location should only appear once.

What I have managed is (this is in my DomainService.cs)

        public IQueryable<LocationStatusList> GetLocationStatus()
        {
         var status = (from a in ObjectContext.Locations
                      from b in ObjectContext.InspectionReadings
                      orderby b.DateTaken descending, a.Name 
                      select new LocationStatusList()
                      {
                          ID = b.ID,
                          LocationName = a.Name,
                          LastInspectionDate = b.DateTaken ?? DateTime.MinValue, // the data is nullable so it needs a value to return in that case
                          StatusNumber = b.Status ?? -1 // the data is nullable so it needs a value to return in that case
                      });
         return status;
        }

which returns the entire InspectionItems entities with their relevant location and although I've tried I can't find a way of doing what I need.

I would like to do all the code for this in the DomainService class but the only way I can think at the moment is to give the query each location name as a parameter from a viewmodel, return a single entity and create a new list and add each single entity.

Surely this can all be done in a LINQ query?

Answer 1

Final version, if I get it right:

var status = (from a in ObjectContext.Locations
              select new {Location = a, LastReading = a.Inspections.SelectMany(i=>i.InspectionReadings).OrderBy(r=>r.PostDate).FirstOrDefault()};