Requête Entity Framework - sélectionnez uniquement l'enregistrement jusqu'à l'horodatage d'une autre table

Question

J'ai 2 tables.

La première est la table History - données reçues par différents appareils.

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

La deuxième est la table 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 |
+----------+---------+--------------------+

Depuis que le périphérique 1 commence à échouer à partir du 15.08.2020 1:44:30, je ne veux pas son enregistrement qui va après ce timestamp.

La même chose pour le périphérique 2.

Donc, en résultat final, je veux uniquement les données de tous les appareils jusqu'à ce qu'ils obtiennent le premier statut 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 |
+----------+-------------+--------------------+

J'ai essayé quelque chose comme ceci

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

Le problème est que si un appareil ne tombe jamais en panne, je n'obtiens pas son historique du tout.

Answer 1

J'ai du code non testé, pouvez-vous essayer. Je pense que la logique devrait fonctionner pour vous.

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

Answer 2

Fondamentalement, vous avez besoin d'une condition "n'existe pas", c'est-à-dire obtenir tous les enregistrements History pour lesquels n'existe pas d'enregistrement DeviceStatusHistory avec le même DeviceId, Status == 0 et un Timestamp supérieur.

Ce qui, avec les propriétés de navigation en LINQ, s'exprime avec la condition !Any(...) plutôt qu'avec FirstOrDefault():

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

Answer 3

La requête SQL ci-dessous fonctionne pour vos besoins

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

Si on essaye de le traduire en linq, cela ressemblera à ceci :

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

SQL Fiddle