Write LinqToEntity to retrive rows by latest date and unitId

Question

I work on my web api project, I use entity framework 6. I need to write LinqToEntity to retrive rows by latest date and unitId.

For example:

Here is my table:

 Id|UnitId  | NumberP | DateMeasure
  1|14      |num1     |2008-01-20 13:10:22.000
  2|32      |num2     |2010-05-20 13:11:38.000
  3|48      |num3     |2011-08-20 13:10:18.000
  4|85      |num4     |2015-11-20 14:10:22.000 

  5|14      |num5     |2010-05-20 13:11:38.000
  6|32      |num6     |2010-05-20 13:12:38.000
  7|85      |num7     |2014-11-20 17:20:32.000

And here is records that I need to get from table using LinqToEntity:

 Id|UnitId  | NumberP | DateMeasure
  3|48      |num3     |2011-08-20 13:10:18.000
  4|85      |num4     |2015-11-20 14:10:22.000 
  5|14      |num5     |2010-05-20 13:11:38.000
  6|32      |num2     |2010-05-20 13:12:38.000

Here what I tried:

  var t2 = from s in unitOfWork.SensorsMeasure.GetAll()
                 group s by s.SensorUnitId into g 
                 select new
                 {


                 };

After I group it how can I order it by date?

How can I create LinqToEntity query to get the desired result?

Answer 1

You can use let clause to get the element of the each group that you are interested in:

var t2 = from s in unitOfWork.SensorsMeasure.GetAll()
         group s by s.SensorUnitId into g
         let latest = g.OrderByDescending(s => s.DateMeasure).FirstOrDefault() 
         select new
         {
             UnitId = g.Key,
             NumberP = latest.NumberP,
             DateMeasure = latest.DateMeasure
         };