Entity Framework select most recent record for each type of record

Question

I have a table called values that looks like this:

+-------+------------+-----------+----------+
|Id     |DateTime    |SensorId   |Value     |
+-------+------------+-----------+----------+

SensorId is a foreign key to a table of the sensor details. There will be 10m+ records in this values table.

I can run this sql command to return the most recent record for each SensorId and it runs in about 0.3 seconds.

SELECT a.*
    FROM Values as a
    INNER JOIN (
        SELECT SensorId, MAX(ID) maxId 
        FROM Values
        GROUP BY SensorId
    ) b ON a.SensorId = b.SensorId 
        AND a.Id = b.maxId
ORDER BY a.SensorId ASC

How can I achieve the same output with entity framework in a c# application while maintaining (or improving) the performance?

Answer 1

With LINQ to Entities and lambdas you can do it like this:

dataContext.Values.GroupBy(p => p.SensorId)
     .Select(p => p.FirstOrDefault(w => w.Id == p.Max(m => m.Id)))  
     .OrderBy(p => p.SensorId).ToList()

where dataContext is your instance of ObjectContext class. ToList() compiles the query.