时间戳

首页 > 解决方案 > 实体框架查询 - 仅选择记录，直到另一个表中的时间戳

c# - 实体框架查询 - 仅选择记录，直到另一个表中的时间戳

问题描述

我有 2 张桌子。

第一个是History表格 - 不同设备接收的数据。

+----------+-------------+--------------------+
| DeviceId | Temperature | TimeStamp          |
+----------+-------------+--------------------+
| 1        | 31          | 15.08.2020 1:42:00 |
| 2        | 40          | 15.08.2020 1:43:00 |
| 1        | 32          | 15.08.2020 1:44:00 |
| 1        | 34          | 15.08.2020 1:45:00 |
| 1        | 20          | 15.08.2020 1:46:00 |
| 2        | 45          | 15.08.2020 1:47:00 |
+----------+-------------+--------------------+

第二个是DeviceStatusHistory表

+----------+---------+--------------------+
| DeviceId | Status  | TimeStamp          |
+----------+---------+--------------------+
| 1        | 1(OK)   | 15.08.2020 1:42:00 |
| 2        | 1(OK)   | 15.08.2020 1:43:00 |
| 1        | 1(OK)   | 15.08.2020 1:44:00 |
| 1        | 0(FAIL) | 15.08.2020 1:44:30 |
| 1        | 0(FAIL) | 15.08.2020 1:46:00 |
| 2        | 0(FAIL) | 15.08.2020 1:46:10 |
+----------+---------+--------------------+

由于 device1 从 15.08.2020 1:44:30 开始失败，我不希望它的记录在那个时间戳之后。

设备2也是如此。

因此，作为最终结果，我只想拥有所有设备的数据，直到它们获得第一个 FAIL 状态：

+----------+-------------+--------------------+
| DeviceId | Temperature | TimeStamp          |
+----------+-------------+--------------------+
| 1        | 31          | 15.08.2020 1:42:00 |
| 2        | 40          | 15.08.2020 1:43:00 |
| 1        | 32          | 15.08.2020 1:44:00 |
+----------+-------------+--------------------+

我尝试过这样的事情

var query = _context
            .History
            .Include(h => h.Device)
            .AsNoTracking()
            .Where(h => h.DeviceTimeStamp <= h.Device.DeviceStatusHistory.FirstOrDefault(st => st.Status == 0).TimeStamp);

问题是，如果一个设备永远不会出现故障，我就根本不知道它的历史。

标签： c#entity-framework-core

解决方案

Below SQL query works for your needs

select * from History h 
where h.TimeStamp < coalesce(
  (
  select min(TimeStamp) from DeviceStatusHistory where Status = 0 and DeviceId = h.DeviceId
) ,Curdate()
)

If we try to translate it to linq it will be like this :

.Where(h => h.DeviceTimeStamp < (h.Device.DeviceStatusHistory
                                 .Where(st => st.Status == 0) 
                                 .Min(st=> st.TimeStamp) ?? DateTime.Now))

SQL Fiddle

推荐阅读