CODEWITHSUNDEEP
c#entity-framework-core
amplifier
amplifier

Reputation: 1833

Entity Framework query - select only record until timestamp from another table

I have 2 tables.

The 1st one is History table - data received by different devices.

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

The 2nd one is DeviceStatusHistory table

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

Since the device1 starts failing from 15.08.2020 1:44:30, I don't want its record that goes after that timestamp.

The same for the device2.

So as a final result I want to have only data of all devices until they get first FAIL status:

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

I tried something like this

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

The problems is if a device never fails, I don't get its history at all.

Upvotes: 3

Views: 214

Answers (3)

Eldar
Eldar

Reputation: 10790

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

Upvotes: 1

Ivan Stoev
Ivan Stoev

Reputation: 205759

Basically you need "not exists" condition, i.e. get all History records for which does not exist DeviceStatusHistory record with the same DeviceId, Status == 0 and greater Timestamp.

Which with navigation properties in LINQ is expressed with !Any(...) condition rather than FirstOrDefault():

.Where(h => !h.Device.DeviceStatusHistory.Any(st =>
    st.Status == 0 && st.TimeStamp > h.DeviceTimeStamp));

Upvotes: 1

Andy Song
Andy Song

Reputation: 4684

I have some untested code, can you try it. I think the logic should work for you.

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

Upvotes: 1

Related Questions