Optymalizacja zapytań LINQ w Entity Framework Core

Ostatnio natrafiłem na problem dotyczący zapytania LINQ wykonywanego na bazie za pomocą EF Core. Teoretycznie z poziomu LINQ wszystko wyglądało OK. A jednak zapytanie wykonywało się na tyle długo, że dostawało time out. Oryginalnie zapytanie wyglądało następująco:

public Dictionary<string, DateTime>; Execute(ICollection<string> orderIds)
{

return context.Transactions
.AsNoTracking()
.Where(t => orderIds.Contains(t.OrderId))
.GroupBy(t => t.OrderId)
.ToDictionary(x => x.Key, x => x.Max(t => t.Date));
}

Generowany SQL wyglądał jak mały potworek. Zawierał wszystkie pola z tabeli Transactions, a jest ich aż 44!

SELECT [t].[TransactionId], [t].[OrderId], [t].[Date], 
[t].[Field3], [t].[Field4], [t].[Field5], [t].[Field6], [t].[Field7], [t].[Field8], 
[t].[Field9], [t].[Field10], [t].[Field11], [t].[Field12], [t].[Field13], [t].[Field14], [t].[Field15], [t].[Field16], [t].[Field17], [t].[Field18], [t].[Field19], 
[t].[Field20], [t].[Field21], [t].[Field22], [t].[Field23], [t].[Field24], [t].[Field25], [t].[Field26], [t].[Field27], [t].[Field28], [t].[Field29], [t].[Field30], [t].[Field31], [t].[Field32], [t].[Field33], [t].[Field34], [t].[Field35], [t].[Field36], [t].[Field37], [t].[Field38], [t].[Field39], [t].[Field40], [t].[Field41], [t].[Field42], [t].[Field43], [t].[Field44], [t].[Field45] 

FROM [biz].[Transaction] AS [t] WHERE [t].[OrderId] IN ( /* lista orderIds */ ) ORDER BY [t].[ExternalOrderId]

Rozwiązanie

Po co nam wszystkie pola, skoro do stworzenia dictionary i tak używamy tylko dwóch z nich. Pierwszą rzeczą było więc dodanie select’a tylko na dwa pola:

public Dictionary<string, DateTime?> Execute(ICollection<string> orderIds)
{
    return context.Transactions
                          .AsNoTracking()
                          .Select(x => new { x.OrderId, x.Date})
                          .Where(t => orderIds.Contains(t.OrderId))
                          .GroupBy(t => t.OrderId)
                          .ToDictionary(x => x.Key, x => x.Max(t => t.Date));
}

Po dokładniejszym przyjrzeniu możemy się domyśleć, że oryginalnym zamiarem było zrobienie GROUP BY. Jednak w finalnym zapytaniu go nie widzimy. Jest to pewna… hmm, ułomność EF Core. GroupBy wywoływany jest już na odebranych danych. Zróbmy więc poprawnego GROUP BY w SQLu za pomocą LINQ. To będzie wymagało trochę gimnastyki:

 public Dictionary Execute(ICollection orderIds)
 {
 return context.Transactions
               .AsNoTracking()
               .Where(t => orderIds.Contains(t.OrderId))
               .GroupBy(t => new { t.OrderId, t.Date } )
               .Select(x => new { x.Key.OrderId, x.Key.Date })
               .ToList()
               .GroupBy(t => t.OrderId )
               .ToDictionary(x => x.Key, x => x.Max(t => t.Date));
 }
 

Na początku konstruuję zapytanie LINQ które przekształcane jest w porządane przez nas zapytanie SQL:

SELECT [t].[OrderId], [t].[Date] FROM [biz].[Transaction] AS [t] 
WHERE [t].[OrderId] IN ( /* lista orderIds */ ) 
GROUP BY [t].[ExternalOrderId], [t].[CustodianTreatmentDate]

Lepiej niż na początku, prawda? Następnie Za pomocą .ToList() wykonuję faktyczne zapytanie na bazie. Następnie działam już z otrzymanymi danymi w pamięci. Grupuję po OrderId (może być kilka dat dla jednego OrderId) a następnie przepisuję wynik do Dictionary, gdzie klucz to OrderId a wartość to max z Date (czyli najnowsza data). Jaki jest zysk. Już pokazuję. Po lewej zapytanie które było na początku (już z SELECT’em tylko dwóch potrzebnych pól). Po prawej zaś wynik końcowego zapytania:

Id	new query			old query
1	1013364	2019-06-06		1013364	2019-06-06
2	1013365	2019-06-06		1013365	2019-06-06
3	1016868	2019-06-24		1016868	2019-06-24
4	1025340	2019-08-13		1016868	2019-06-24
5	1026542	2019-08-21		1025340	2019-08-13
6	1026554	2019-08-21		1025340	2019-08-13
7				1026542	2019-08-21
8				1026554	2019-08-21
9				1026554	2019-08-21
10				1026554	2019-08-21
11				1026554	2019-08-21
12				1026554	2019-08-21

Podsumowanie

Jak widać różnica jest kolosalna. A zakładając, że na taką samą datę i OrderId możemy mieć nawet i 100 wpisów, a pobieramy 1000 Transakcji, musimy przesłać aż 1000*100=100 000 rekordów. Dzięki temu, że shackowaliśmy EF Core, generuje już poprawne zapytanie i dzięki temu dostaniemy tylko 1000 wierszy bez duplikatów.