score:1

Accepted answer

I may have bad understood, but basically, you want the last created date for each distinct Lot ? If yes, then 

db.Reports
  .GroupBy(r => r.Lot)
  .Select(g => g.OrderByDescending(x => x.createdDate).FirstOrDefault());

or to do like you (but I'm not sure it's the easiest way)

var maxDates = Reports.GroupBy(r => r.Lot)
            .Select(x => x.Max(g => g.createdDate).ToList();

var result = db.Reports.Where (m => maxDates.Contains(m.createdDate));

EDIT

Your example is not that clear (I change a little bit the names for clarity).

With code first, you should have something like that (idea is the same)

a class Report

public class Report {
   public int Id {get;set;}
   public int Lot {get;set;}
   public Datetime CreatedDate {get;set;}
   public virtual Category Category {get;set;} //Navigation property
}

a class Category

public class Category {
    public int Id {get;set;}
    public string Title {get;set;}
    public virtual IList<Report> ReportList {get;set;} //Navigation property
}

and eventually a "result class"

public class ReportList {
    public string Title {get;set;}
    public List<Report> ReportList {get;set;}
}

Then query could become

db.Reports
  .GroupBy(r => r.Lot)
  .Select(g => g.OrderByDescending(x =x.createdDate).FirstOrDefault())
  .GroupBy(m => m.Category.Id)
  .Select(g => new  ReportList {
    Title = g.FirstOrDefault().Category.Title,
    ReportList = g.OrderBy(x => x.Lot)
  });

score:2 

var recentDates = Reports
    .GroupBy(r=>r.SeedLot, r=>r.CreatedDate)
    .Select(rg=>rg.Max());

var result =
    from r in Reports
    join d in recentDates
        on r.createdDate equals d
    select r;

Related Articles