Entity Framework – własne funkcje w zapytaniach LINQ to Entities

2012-09-23

Temat rozpocznę od przykładowego kodu będącego wstępem do omawianego zagadnienia. Zacznijmy od napisania prostej funkcji konwertującej wartość wyrażoną w milach na wartość w kilometrach:

public static int MilesToKilometers(int valueInMiles)
{
    return Convert.ToInt32(valueInMiles * 1.609344);
}

Poniżej znajduje się zapytanie LINQ wykorzystujące utworzoną funkcję do wyszukania samochodów o maksymalnej prędkości przekraczającej 200 km/h (pole TopSpeed zawiera prędkość w milach). Oczywiście lepszym rozwiązaniem byłoby bezpośrednie porównanie z wartością w milach (czyli 124) bez użycia funkcji, ale na potrzeby prezentacyjne przyjmijmy taki wariant za dopuszczalny:

var cars = from car in context.Cars
           where MilesToKilometers(car.TopSpeed) > 200
           select car;

foreach (var c in cars)
    Console.WriteLine(c.Model);

Pytanie, czy powyższy kod wykona się poprawnie? Odpowiedź brzmi: to zależy. W przypadku LINQ to Objects (gdzie kolekcja Cars byłaby np. listą) jak najbardziej tak. W przypadku LINQ to Entities mimo poprawnej kompilacji, przy próbie wykonania zapytania otrzymamy następujący komunikat (w tym miejscu warto przypomnieć, że zapytanie LINQ wykonywane jest dopiero w momencie odczytu jego wyników, a więc błąd zobaczymy w pętli foreach):

LINQ to Entities does not recognize the method ‚Int32 MilesToKilometers(Int32)’ method, and this method cannot be translated into a store expression.

Aby wyjaśnić przyczynę tego błędu, należy zrozumieć różnicę w wykonywaniu zapytań LINQ na różnych źródłach danych. W przypadku LINQ to Objects dla każdego elementu kolekcji wykonywana jest metoda MilesToKilometers i na podstawie warunku logicznego element taki trafia (bądź nie) do wyników zapytania. Mechanizm LINQ to Entities działa inaczej. Zapytanie najpierw tłumaczone jest na język SQL, po czym zostaje wysłane do bazy danych w celu wykonania. Po otrzymaniu odpowiedzi z serwera możemy korzystać z wyników. Z opisanym schematem działania wiąże się ważna zasada, w zapytaniach LINQ to Entities możemy używać jedynie takich konstrukcji, które mogą być przetłumaczone na język SQL. Metoda MilesToKilometers nie spełnia tego warunku i stąd pojawienie się powyższego błędu. Nie oznacza to jednak, że jesteśmy pozbawieni możliwości stosowania własnych funkcji w zapytaniach LINQ to Entities. Rozwiązaniem problemu jest zdefiniowanie takiej funkcji w modelu koncepcyjnym Entity Framework. Aby to zrobić wystarczy otworzyć plik *.edmx za pomocą wbudowanego w Visual Studio edytora XML i w elemencie Schema sekcji ConceptualModels umieścić definicję własnej funkcji:

<Function Name="MilesToKilometers" ReturnType="Edm.Int32">
  <Parameter Name="valueInMiles" Type="Edm.Int32" />
  <DefiningExpression>
    Cast(Round(valueInMiles * 1.609344) as Edm.Int32)
  </DefiningExpression>
</Function>

Definicja funkcji składa się z elementu Function zawierającego atrybuty z jej nazwą oraz typem zwracanego wyniku. Element ten zawiera także elementy Parameter określające nazwy i typy przekazywanych do funkcji parametrów oraz element DefiningExpression zawierający implementację funkcji w języku Entity SQL.

Po umieszczeniu definicji funkcji w modelu koncepcyjnym należy dodać do aplikacji metodę zmapowaną do utworzonej funkcji. Mapowanie realizowane jest poprzez atrybut EdmFunction (przestrzeń System.Data.Objects.DataClasses), w którym podajemy namespace modelu (atrybut Namespace elementu Schema zawierającego definicję funkcji) oraz nazwę funkcji:

[EdmFunction("TestModel", "MilesToKilometers")]
public static int MilesToKilometers(int valueInMiles)
{
    throw new NotSupportedException("Direct calls are not supported.");
}

W tym przypadku metoda dostępna będzie jedynie z poziomu zapytań, przy bezpośrednim wywołaniu zgłoszony zostanie wyjątek. Nic nie stoi jednak na przeszkodzie aby metoda zwierała zarówno atrybut mapowania jak i implementację logiki.

Po wykonaniu opisanych czynności możemy korzystać z własnej funkcji w zapytaniach LINQ to Entities:

using (TestEntities context = new TestEntities())
{
    var cars = from car in context.Cars
               where MilesToKilometers(car.TopSpeed) > 200
               select car;

    foreach (var c in cars)
        Console.WriteLine(c.Model);
}

Wykorzystując narzędzie SQL Server Profiler możemy sprawdzić jakie zapytanie wysyłane jest do bazy danych po uruchomieniu powyższego kodu. Oto wynik:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Model] AS [Model],
[Extent1].[Engine] AS [Engine],
[Extent1].[MaxPower] AS [MaxPower],
[Extent1].[TopSpeed] AS [TopSpeed]
FROM [dbo].[Car] AS [Extent1]
WHERE  CAST( ROUND([Extent1].[TopSpeed] * cast(1.609344 as float(53)), 0) AS int) > 200

Jak widać, zmiana implementacji funkcji MilesToKilometers z języka C# na Entity SQL umożliwiła przetłumaczenie jej na język SQL i wykonanie po stronie bazy danych.

Więcej informacji o definiowaniu własnych funkcji: Define Custom Functions in the Conceptual Model, Call Model-Defined Functions in QueriesModel Defined Functions.

Reklamy

Posted on 2012-09-23, in .NET/C# and tagged , , . Bookmark the permalink. 2 Komentarze.

  1. Niestety jeszcze nie miałem okazji korzystać z EF. Korzystałem tylko z LINQ to SQL Classes. Stad rodzą mi się dwa pytania. Pierwsze – nie ma zagrożenia, że zdefiniowana funkcja w edmx zniknie podczas przegenerowania schematu edmx? Drugie – trochę słabe rozwiązanie, ponieważ w pewnym momencie funkcja w edmx może różnić się implementacją od funkcji z pliku cs. W tym wypadku lepszym rozwiązaniem wydaje się pobranie danych z bazy i późniejsza filtracja.

    • Ad1. Podczas aktualizacji modelu (Update Model from Database) nasza funkcja pozostanie w schemacie. Operacja ta ma wpływ jedynie na wskazane przez nas obiekty.

      Ad2. Jeżeli chodzi o różnice w implementacjach to masz rację, dlatego zalecanym rozwiązaniem jest to przedstawione przeze mnie w przykładzie, gdzie implementacja znajduje się jedynie w modelu. Podczas bezpośredniego wywołania metody zgłaszany jest wyjątek. Pobieranie wszystkich rekordów i filtrowanie ich po stronie aplikacji może mieć negatywny wpływ na wydajność. Jeżeli tabela ma bardzo dużo rekordów, a w funkcji użyjemy kolumny, na której jest indeks to zdecydowanie lepiej takie zapytanie wykonać po stronie bazy niż pobierać całą zawartość tabeli do aplikacji.

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Wyloguj / Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Wyloguj / Zmień )

Zdjęcie na Facebooku

Komentujesz korzystając z konta Facebook. Wyloguj / Zmień )

Zdjęcie na Google+

Komentujesz korzystając z konta Google+. Wyloguj / Zmień )

Connecting to %s

%d blogerów lubi to: