How to Achieve Group Results by Max Date in Entity Framework

Question

I have the following data structure (it's an append only table for mapping content to a page zone in our proprietary CMS system).

  - RowID INT IDENTITY
  - PageID INT FK
  - ZoneID INT
  - ContentID FK
  - DateAdded DATETIME

I am using Entity Framework, and want to use LINQ to Entities to get a list of these records where the ZoneID is aggregated up to the most recent date. Essentially, there should be only one result for each ZoneID, and the query should filter on pageID.

Thanks in advance for the help!

Answer 1

Found my own answer...

var query = from zone in 
            this.Context.Zones
            where zone.PageID == pageID
            group zone by new { zone.PageID, zone.ZoneID } into zoneGroup
            let maxDate = zoneGroup.Max(x => x.DateCreated)
            select zoneGroup.Where(x => x.DateCreated== maxDate);


foreach (var result in query)
{
     // ... do something  
}