Entity Framework fetches the most recent record for each record type
I have a table with values that looks like this:
+-------+------------+-----------+----------+
|Id |DateTime |SensorId |Value |
+-------+------------+-----------+----------+
SensorId is a foreign key to the sensor data table. This table of values will have 10 m + records.
I can run this sql command to return the most recent entry for each SensorId and it runs after 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 an entity framework in a C # application while maintaining (or improving) performance?
+3
source to share
1 answer
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 class instance ObjectContext
. ToList()
compiles the request.
+6
source to share