Entity Linq - Retrieve record and only the first child record in a one to many relationship.

Question

I have an entity called "Requests" which has a navigation called "StatusHistories"

I need to retrieve all of the Requests where the last StatusHistory is "Open"

StatusHistory has the fields StartDate (the highest one of these would be the last StatusHistory) Status (for this presume status contains the string "Open" or "Closed") RecordID (this is an Identity field in SQL Server, so it too could be used to find the last one, but I'd rather not)

Thanks.

Answer 1

var result = from r in Requests
             where <condition> 
             select r.field1, r.field2, (from s in StatusHistory
                                         where <join codition>
                                         order by s.StartDate descending
                                         select s.field).FirstOrDefault()