EF Core - pobieranie tylko najnowszych z powiązanych właściwości

0

Mam sobie w bazie danych tabele (obiekty) typu Sensor, które są powiązane relacją z obiektami SensorValue. I tych SensorValue może być dużo (obecnie dziesiątki tysięcy dla jednego Sensor). Żeby przedstawić użytkownikowi wiele rzeczy potrzebuję najczęściej tylko najnowszych (najświeższych) SensorValue, więc robienie czegoś w stylu context.Sensors.Include(x => x.Values).ToList() chyba nie ma sensu.

I obecnie robię to sobie tak, że ręcznie buduję nową listę obiektów, na podstawie listy sensorów bez użycia Include() oraz na podstawie pobrania tylko ostatnich 50 (dobrane całkowicie arbitralnie) wartości z każdego z sensorów.

public IEnumerable<Sensor> GetAllSensorsWithValues()
{
	var sensors = GetAllSensors();
	var result = new List<Sensor>();

	foreach (var item in sensors)
	{
		result.Add(GetSensorWithLast50Values(item.Name));
	}

	return result;
}

public IEnumerable<Sensor> GetAllSensors()
{
	return _context.Sensors.ToList();
}

public Sensor GetSensorWithLast50Values(string name)
{
	var sensor = _context.Sensors.First(x => x.Name == name);
	sensor.Values = _context.SensorValues
		.Where(x => x.Sensor == sensor)
		.OrderByDescending(x => x.Timestamp)
		.Take(50).ToList();

	return sensor;
}

Główne pytanie: czy to można zrobić lepiej/ładniej/sensowniej?

1

Spróbuj może tak:

_context.Sensors.Select(x => new
{
  Sensor = x.Sensor,
  LastValues = x.Sensor.Values.OrderByDescending(val => val.Timestamp).Take(50).ToList()
}).ToList();

Ale musisz sprawdzić, czy EF puści to jednym zapytaniem, czy zrobi select n+1, bo po EF Core można się wszystkiego spodziewać :D

0

Jak wyglądają indeksy na tych tabelach ? PS: metoda GetSensorWithLast50Values mogła by ten limit przyjmować z parametru i wtedy nie musiałaby się tak dziwnie nazywać :D

0

@mad_penguin:

Spróbuj może tak:

Fajne. Wykonałem nieco zmodyfikowaną koncepcję (bo chodziło mi o to, aby model się w zasadzie nie zmieniał - aby właściwość Values dalej sobie była, dalej istniała jakby była normalną relacją, ale dostawała tylko fragmenty wyników):

var sensors = _context.Sensors.Include(x => x.Values).Select(x => new Sensor
{
    Description = x.Description,
    InternalId = x.InternalId,
    Name = x.Name,
    Type = x.Type,
    Values = x.Values.OrderByDescending(val => val.Timestamp).Take(50).ToList()
}).ToList();

Co spowodowało taki wynik:

      The Include operation for navigation '[x].Values' is unnecessary and was ignored because the navigation is not reachable in the final query results. See https://go.microsoft.com/fwlink/?linkid=850303 for more information.
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT `x`.`Description`, `x`.`InternalId`, `x`.`Name`, `x`.`Type`
      FROM `Sensors` AS `x`
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (51ms) [Parameters=[@_outer_Name='?' (Size = 255)], CommandType='Text', CommandTimeout='30']
      SELECT `val`.`Id`, `val`.`Data`, `val`.`SensorName`, `val`.`Timestamp`
      FROM `SensorValues` AS `val`
      WHERE @_outer_Name = `val`.`SensorName`
      ORDER BY `val`.`Timestamp` DESC
      LIMIT 50
/// i tak pięć razy, bo 5 sensorów

I w związku z tym pierwszym logiem druga wersja pozbywa się Include() i działa nadal tak samo jak powinna.

Czyli w zasadzie: mamy coś, co robi to samo (n + 1 zapytań), tylko ładniej, bo w jednym ładnym LINQ, czyli w sumie spełnia moje założenia :-) Dzięki za pomoc! Oczywiście pytanie - czy da się to zrobić lepiej, ale w sumie to nie wiem - jak się zastanawiam nad SQL-em takim to nie widzę fajnego rozwiązania.

@WeiXiao:

A gdyby ostatnie X SensorValue trzymać w aplikacji (słowniczek<sensor, list<values>> lub innej, takiej "lotnej" tabeli w db?

Cache (InMemory) wejdzie właśnie gdzieś tutaj, aby każdorazowe wejście na Dashboard nie robiło nawet tych 5 selectów, skoro te wyniki i tak się aktualizują co ~15 minut.

@error91:

Jak wyglądają indeksy na tych tabelach ?

Jest założony indeks na kolumnę Name w Sensor (ona jest PK), oraz na Id i Name w SensorValue (odpowiednio PK i FK).

2

@Ktos: Przede wszystkim to da się zrobić jednym zapytaniem pomijając przy tym N+1. Niestety albo stety trzeba napisać procedurę bo EF tego na pewno nie ogarnie. W zależności jak unikalne są Twoje timestampy to przychodzą mi do głowy dwa zapytania:

SELECT T.SensorName,
       T.[Value],
       T.[Timestamp]
FROM (SELECT SUB.SensorName,
             SUB.[Value],
             SUB.[Timestamp],
             ROW_NUMBER() OVER(PARTITION BY SUB.SensorName ORDER BY SUB.[Timestamp] DESC) AS rn
      FROM dbo.SensorValues AS SUB) AS T
WHERE T.rn <= 50;
SELECT [Values].* FROM dbo.Sensors AS S
CROSS APPLY (SELECT TOP (50) SensorName,
                             [Value],
                             [Timestamp]
              FROM dbo.SensorValues AS SV
              WHERE SV.SensorName = S.Name
              ORDER BY Timestamp DESC, ID DESC) AS [Values];

Oba muszą być wsparte indexem

CREATE UNIQUE NONCLUSTERED INDEX [Idx_SensorValues_SensorName_Timestamp_Id]
  ON dbo.SensorValues (SensorName, [Timestamp] DESC, Id DESC)
  INCLUDE([Value]);

Popatrz na plany zapytań ale u mnie to zapytanie z CROSSAPPLY działało lepiej przy tabeli z 4 mln wierszy. Wykonywało się w mniej niż sekundę i miało o wiele mniej logical readów.

1 użytkowników online, w tym zalogowanych: 0, gości: 1