LINQ Query To Join Two Tables and Select Most Recent Records from Table B corresponding to Table A

Viewed 4172

I have two tables. Table One contains a list of Areas, and Table Two contains a list of Samples, with each Sample row containing Area_ID as a Foreign Key.

I need to retrieve all the records in my Area table with only the most recent corresponding Sample Status. I have this query, but it just returns one Area with the most recent sample from the Sample table:

var result = (
    from a in db.area
    join c in db.sample
    on a.location_id equals c.location_id

    select new 
    {
        name = a.location_name,
        status = c.sample_status,
        date = c.sample_date
    }).OrderByDescending(c => c.date).FirstOrDefault();
1 Answers
Related