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

Question

I have a question similar to this entry:

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

... but I need it in Linq.

In short for the people who do not care to read the other link:

I have a history table with devices. I need the latest entry for each device before a given date. So I do not need only a specific value, but the 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 I would like to have the last known states before 2016-01-05, I would expect the 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 is not required, I just added it for clarity. Again, I would need this in Linq. I could easily do this with a foreach in code, but I do not want to make 700 calls to the database. I would like this to be fairly efficient.

Thanks!

What have you tried so far? Also, I can't say for 100% certainty, but I think LINQ would use some sort of looping mechanism - it would just be more succinct-looking in the code. — Tim, Jun 10 '16 at 22:25
I do have it working in SQL, probably not efficient, but working. But I have no idea how to translate it to linq. The sql I have is the following: `select * from DeviceHistory as dh right join (select (select top 1 Id from DeviceHistory where DeviceId = s.Id and LastUpdate <= '2016-06-11 00:00:00' order by LastUpdate desc) as HistoryId from Devices as s) as dhId on dh.Id = dhId.HistoryId where not Id is null order by LastUpdate desc` — Stitch10925, Jun 13 '16 at 06:54

score 1 · Accepted Answer · answered Jun 10 '16 at 22:29

1

Something like this 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 will give us null for devices that
                           //don't have a status entry before the date
    .AsEnumerable()
    .Where(x => x != null) //remove null values (optional)
    .ToList();

answered Jun 10 '16 at 22:29

Yacoub Massad

27,509
2
36
62

What if same DeviceId's have LastUpdatedDate ? , .FirstOrDefault() will not bring that one , unless he whats it that way. – Amit Pore Jun 11 '16 at 11:09
@AmitPore, I am not sure I understand what you mean. Can you elaborate? – Yacoub Massad Jun 11 '16 at 11:42
Sorry I meant same deviceid having same lastupdateddate – Amit Pore Jun 11 '16 at 12:16
In this case, the query would return one of them – Yacoub Massad Jun 11 '16 at 13:06
In my case it is impossible for the same device to have multiple entries with the same last updated date – Stitch10925 Jun 13 '16 at 07:32
Awesome! Surprisingly the solution is less complex than I expected. The results seem to be fine. – Stitch10925 Jun 20 '16 at 07:03

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

1 Answers1

Linked