LINQ left join with only the row having maximum value of a column

Question

I have a table ProjectInformation as (left table)

ProjectID int
{fields}

the other table, ProjectUpdates (right table). This table has multiple record for 1 ProjectID added monthly.

ProjectID int
CreateDate date
{other fields that have records}

Their relation is 1-M. Sample Data

ProjectInformation
{ ProjectID = 1, DataA = "ABC"}
{ ProjectID = 2, DataA = "DEF"}
{ ProjectID = 3, DataA = "GHI"}

ProjectUpdates
{ProjectID = 1, CreateDate = "24/2/2014", DataB = "JKL"}
{ProjectID = 1, CreateDate = "25/1/2014", DataB = "MNL"}
{ProjectID = 1, CreateDate = "23/12/2014", DataB = "PQR"}
{ProjectID = 1, CreateDate = "23/11/2014", DataB = "STU"}
{ProjectID = 2, CreateDate = "24/2/2014", DataB = "VWX"}
{ProjectID = 2, CreateDate = "24/1/2014", DataB = "YZA"}
{ProjectID = 3, CreateDate = "21/12/2014", DataB = "BCD"}
{ProjectID = 3, CreateDate = "24/11/2014", DataB = "EFG"}
{ProjectID = 3, CreateDate = "24/10/2014", DataB = "HIJ"}
{ProjectID = 3, CreateDate = "24/8/2014", DataB = "KLM"}
{ProjectID = 3, CreateDate = "24/6/2014", DataB = "NOP"}

I want my LINQ query to return the following Data (1 row for each project)

Criteria: The row in left table will join with the one in the right which has the largest value of CreateDate for the project.

{ProjectID = 1, CreateDate = "24/2/2014", DataA ="ABC", DataB = "JKL"}
{ProjectID = 2, CreateDate = "24/2/2014", DataA ="DEF", DataB = "VWX"}
{ProjectID = 3, CreateDate = "21/12/2014", DataA ="GHI", DataB = "BCD"}

Answer 1

You can do it many ways. One of those way is:

var result = (from pi in projectInformations
              join pu in projectUpdates on pi.ProjectID equals pu.ProjectID into tpu
              from t in tpu.OrderByDescending(c => c.CreateDate).Take(1)
              select new { pi.ProjectID, pi.DataA, t.CreateDate, t.DataB }).ToList();

Answer 2

You can use a one to many association from ProjectInformation to ProjectUpdate and OrderByDescending/FirstOrDefault.

public class ProjectInformation {
  public int ProjectID { get; set; }
  public virtual ICollection<ProjectUpdate> ProjectUpdates { get; set; }
}

db.Project
  .Select(p => new {
    ProjectID = p.ProjectID,
    MostRecentProjectUpdate = p.ProjectUpdates.OrderByDescending(u => u.CreateDate).FirstOrDefault()
});

Note: Using OrderByDescending may be tricky if CreateDate is a string rather than a Date in your database.