Linq to SQL-在特定日期之前获取每个设备的最新已知历史记录条目 [英] Linq to SQL - Getting the last know history entry for each divice, prior to a certain date

问题描述

我有一个与此条目类似的问题:

I have a question similar to this entry:

方法-i-查询SQL为每个用户的最新记录日期

...但是我在Linq中需要它.

... 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.

示例数据:

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

因此，如果我想获得2016年1月5日之前的最新已知状态，我希望得到以下结果:

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

不需要NULL条目，我只是为了清楚起见添加了它.同样，我需要在Linq中使用它.我可以使用代码中的foreach轻松地做到这一点，但是我不想对数据库进行700次调用.我希望这是相当有效的.

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.

谢谢！

推荐答案

类似的方法应该起作用:

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();

这篇关于Linq to SQL-在特定日期之前获取每个设备的最新已知历史记录条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！