LINQ Query for GroupBy and Max in a Single Query

Question

I have the following LINQ query but i want to modify it that I want to group by staffId and pick only those records whose ObservationDate is Max for each staffId.

from ob in db.TDTObservations.OfType<TDTSpeedObservation>()
select new
{
   Id = ob.ID,
   AcademicYearId = ob.Teachers.FirstOrDefault().Classes.FirstOrDefault().AcademicYearID,
   observationDate = ob.ObservationDate,
   schoolId = ob.Teachers.FirstOrDefault().Classes.FirstOrDefault().SchoolID,
   staffId=ob.Teachers.FirstOrDefault().ID
};

See http://stackoverflow.com/questions/4775125/linq-query-with-grouping-and-ranking — Tassadaque, Mar 21 '13 at 11:06

score 1 · Accepted Answer · answered Mar 21 '13 at 11:09

var observations = 
    from ob in db.TDTObservations.OfType<TDTSpeedObservation>()
    select new  {
       Id = ob.ID,
       AcademicYearId = ob.Teachers.FirstOrDefault().Classes.FirstOrDefault().AcademicYearID,
       observationDate = ob.ObservationDate,
       schoolId = ob.Teachers.FirstOrDefault().Classes.FirstOrDefault().SchoolID,
       staffId=ob.Teachers.FirstOrDefault().ID
    };

var result = from o in observations
             group o by o.staffId into g
             select g.OrderByDescending(x => x.observationDate).First();

LM358 · Answer 2 · 2016-05-30T10:40:46.800

0

what about this: hereby you first group your entries (Teachers) by their ID together and then from each group (grp) you pick that one with the latest ObservationDate

var observations = from d in db.TDTObservations.OfType<TDTSpeedObservation>()
                   group d by d.Teachers.FirstOrDefault().ID into grp
                   select grp.OrderByDescending(g => g.ObservationDate).FirstOrDefault();

edited May 30 '16 at 10:40

answered May 30 '16 at 09:50

LM358

141
9

Could you please elaborate more your answer adding a little more description about the solution you provide? – abarisone May 30 '16 at 10:08

LINQ Query for GroupBy and Max in a Single Query

2 Answers2