c# - Linq to SQL - Getting the last know history entry for each divice, prior to a certain date -

i have question similar entry:

how-do-i-query-sql-for-a-latest-record-date-for-each-user

... need in linq.

in short people not care read other link:

i have history table devices. need latest entry each device before given date. not need specific value, whole row.

example data:

id | deviceid | state   | lastupdateddate 0  | 1        | online  | 2016-01-05 10:23:45 1  | 2        | offline | 2016-01-04 00:05:33 2  | 1        | offline | 2016-01-01 06:13:25 3  | 1        | online  | 2016-01-07 11:02:06 4  | 3        | offline | 2016-01-03 18:00:25 5  | 4        | online  | 2016-01-08 03:00:05 4  | 3        | offline | 2016-01-08 04:27:21 

so, if have last known states before 2016-01-05, expect following result:

id    | deviceid | state   | lastupdateddate 1     | 2        | offline | 2016-01-04 00:05:33 2     | 1        | offline | 2016-01-01 06:13:25 4     | 3        | offline | 2016-01-03 18:00:25  null  | 4        | null    | null 

the null entry not required, added clarity. again, need in linq. foreach in code, not want make 700 calls database. efficient.

thanks!

something should work:

datetime date = //... (e.g. 2016-01-05)  var result =     entries //e.g., context.devicehistoryentries     .groupby(x => x.deviceid)     .select(gr =>         gr         .where(x => x.lastupdateddate < date)         .orderbydescending(x => x.lastupdateddate)         .firstordefault()) //this give null devices                            //don't have status entry before date     .asenumerable()     .where(x => x != null) //remove null values (optional)     .tolist();