Entity Framework i rzeczywiste zapytania SQL Server

Warstwa logiczna dostępu do danych

Entity Framework to jedno z wielu narzędzi typu O/RM - mapowania obiektowo-relacyjnego. Narzędzie, jak by o nim nie mówić, bardzo przydatne. Znacznie ułatwia operowanie na wszelkiego rodzaju strukturach bazodanowych z poziomu kodu zarządzanego platformy .NET. Ułatwia tak mocno, że wielu programistów zapomina o jednym ważnym składniku pod tą piękną i przyjazną warstwą dostępu do danych. Zapomina o tym, że gdzieś tam jest jeszcze baza danych.

Różne operacje wykonywane na obiektach Entity Framework (to samo dotyczy LINQ To SQL) mogą, ale nie muszą, kierować do bazy odpowiednie zapytania SQL. Problem w tym, że tych zapytań nie widać od razu. Zdarza się, że z pozoru prosta operacja generuje dziesiątki, setki, nawet tysiące zapytań do bazy. Widziałem osobiście pojedyncze operacje generujące dziesiątki tysięcy zapytań. Programiści, zwłaszcza początkujący, nie są świadomi swoich błędów - do czasu, aż przyjdzie jakiś administrator i spyta: #$%@$, kto tak zamula bazę?

Błedy są niedostrzegalne, bo wszystkie dane na klienta są grzecznie zwracane i wszystkie są prawidłowe. Z punktu widzenia użytkownika wszystko wygląda dobrze. O ile danych jest mało (środowisko testowe), serwer bazodanowy jest mocny (czesto winę widzi się w sprzęcie) - aplikacja działa sprawnie. Nadchodzi jednak taka chwila, w której ktoś mówi: Stop! Zacznijcie pisać jak ludzie!

Mam jeszcze jeden cel. Od zawsze istnieje wojna pomiędzy administratorami baz i programistami, osobami myślącymi relacyjnie i zbiorowo, z osobami myślącymi proceduralnie i funkcyjnie. Programiści .NET, ale nie tylko, wszyscy korzystający z mechanizmów mapowania obiektowo-relacyjnego powinni zrozumieć sposób myślenia i dostrzec różnicę pomiędzy tradycyjnym zapytaniem, a zapytaniem generowanym przez ulubiony O/RM. Nie doprowadzi to do pokoju, ale może zakres działań wojennych ulegnie zmniejszeniu.

Instrukcje, które generują zapytania do bazy

Na początek warto zdać sobie sprawę, że istnieje grupa instrukcji, które zawsze wygenerują zapytanie do bazy. Są to:

instrukcje konwertujące dane do kolekcji: ToList, ToArray, ToDictionary, ToLookup,
instrukcje sprawdzające istnienie lub nieistnienie danych: Any, All,
instrukcje pobierające pojedyncze rekordy: First, FirstOrDefault, Last, LastOrDefault, Single, SingleOrDefault,
wyliczenie sekwencji instrukcją foreach.

Każda z tych instrukcji oznacza wysłanie, mniej lub bardziej kosztownego, zapytania SQL. Oprócz wypisanych instrukcji istnieje jeszcze szereg innych operacji, które również mogą powodować komunikację z serwerem baz danych.

Testowa tabela

Większość pokazanych w dalszej części przykładów będzie się odnosiła do jednaj tabeli w bazie. Tabela zdefiniowana jest następująco:

CREATE TABLE Person
(
  ID int IDENTITY PRIMARY KEY,
  FirstName nvarchar(20),
  LastName nvarchar(20),
  Email nvarchar(20),
  PositionID int
)

Zapytania można kierować do pustej bazy i nawet wtedy widać ich strukturę, ale w niektórych przypadkach do zaobserwowania pewnych zjawisk potrzebne będą odpowiednie dane. Jeżeli nie zaznaczę tego w tekście, w testowej tabeli będą się znajdowały następujące rekordy:

ID	FirstName	LastName	Email	PositionID
1	Tomasz	Wielki	wielki@malpa.pl	1
2	Tomasz	Niewielki	niewielki@malpa.pl	2
3	Krzysztof	Niebieski	niebieski@malpa.pl	2

Instrukcja SELECT z prostego zapytania

Zanim przejdę do bardziej zaawansowanych konstrukcji przyjrzyjmy się prostej instrukcji SELECT wygenerowanej przez równie prostą instrukcję Entity Framework:

using (var db = new TestEntities())
{
    var query = from p in db.Person
                select new { p.FirstName, p.LastName };

    var result = query.ToList();
}

Od strony C# i NET wszystko jest ładne i czytelne. Administrator bazy danych dostrzeże jednak coś innego. Według niego będzie to trochę nielogiczne zapytanie:

SELECT
1 AS [C1],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName]
FROM [dbo].[Person] AS [Extent1]

Oprócz aliasów kolumn i tabel, które potrafią mocno zanieczyścić nawet proste zapytania, pojawia się tutaj jeszcze coś. Chodzi o pierwszą kolumnę, która zawsze zwraca 1. Po co ona jest? Z punktu widzenia bazy danych to dodatkowa operacja, z punktu widzenia aplikacji jest niedostępna. Oznacza to, że jest to jakiś wewnętrzny mechanizm Entity Framework.

Gdybyśmy podobne zapytanie pisali sami, wyglądałoby ono mniej więcej tak:

SELECT FirstName, LastName
FROM Person

Warto sobie zestawić te dwa zapytania obok siebie i porównać ich czytelność. Myślę, że nie trzeba tu nic mówić. Skoro już tak proste zapytanie potrafi tyle namieszać - co z innymi?

Wspomniałem o magicznej pierwszej kolumnie, która zawsze zwraca 1 i o jej powiązaniu z kluczem głównym. Popatrzmy na kolejny przykład, w którym pobierany jest również unikalny identyfikator:

var query = from p in db.Person
select new { p.ID, p.FirstName, p.LastName };

A teraz popatrzmy na wygenerowane zapytanie:

SELECT
[Extent1].[ID] AS [ID],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName]
FROM [dbo].[Person] AS [Extent1]

Widać, że kolumna ze stałą w magiczny sposób zniknęła. I dobrze. W praktyce najczęściej pobiera się rekordy razem z identyfikatorami (lub innymi kolumnami pełniącymi funkcję klucza głównego), więc ten problem nie powinien się zbyt często pojawiać. Klucz jest potrzebny w celu wykonania późniejszej aktualizacji rekordu - jak moglibyśmy namierzyć i zidentyfikować rekord do modyfikacji lub usunąć go bez czegoś unikalnego? Taką rolę pełni właśnie klucz główny.

Złączenie wewnętrzne

Złączenie wewnętrzne, INNER JOIN, lub krócej, JOIN, to najpowszechniej wykorzystywany rodzaj złączenia. Popatrzmy na przykładowe złączenie wewnętrzne w Entity Framework:

var query = from p1 in db.Person
join p2 in db.Person on p1.ID equals p2.ID + 1
select new { ID1 = p1.ID, ID2 = p2.ID };

var result = query.ToList();

Złączenie trochę bez sensu, ale nie chciałem wprowadzać dodatkowych tabel. Zerknijmy teraz na wygenerowane zapytanie SQL:

SELECT
[Extent1].[ID] AS [ID],
[Extent2].[ID] AS [ID1]
FROM [dbo].[Person] AS [Extent1]
INNER JOIN [dbo].[Person] AS [Extent2] ON [Extent1].[ID] = ([Extent2].[ID] + 1)

Zapytanie jest optymalne i nie ma się co nad nim rozwodzić. Dałoby się je zapisać nieco czytelniej, ale nie to stanowi problem. Słowo kluczowe equals oznacza równość. Nie ma natomiast odpowiednika do złączeń nierównościowych (zwane złączeniami teta, theta). Co począć?

Złączenia nierównościowe theta

Aby wykonać złączenie nierównościowe można wykorzystać pewną własność logicznej kolejności przetwarzania zapytań SQL i furtkę zostawioną przez Entity Framework i LINQ To SQL:

var query = from p1 in db.Person
            from p2 in db.Person
            where p1.ID == p2.ID + 1
            select new { ID1 = p1.ID, ID2 = p2.ID };

Wygenerowane zapytanie będzie może trochę inne niż planowane, ale logicznie równoważne zwykłemu złączeniu nierównościowemu. Oba zapytania - Entity Framework oraz równoważne, napisane ręcznie, zostały pokazane poniżej:

--Zapytanie Entity Framework
SELECT
[Extent1].[ID] AS [ID],
[Extent2].[ID] AS [ID1]
FROM [dbo].[Person] AS [Extent1]
CROSS JOIN [dbo].[Person] AS [Extent2]
WHERE [Extent1].[ID] = ([Extent2].[ID] + 1)

--Zapytanie logicznie równoważne
SELECT P1.ID, P2.ID
FROM Person P1
JOIN Person P2 ON P1.ID = P2.ID + 1

Podobną techniką można załatwić w zasadzie wszystkie złączenia funkcyjne.

Złączenie wewnętrzne wykorzystujące składnię obiektową

Aby pokazać takie złączenie należy nieco rozbudować przykład i dorzucić drugą tabelę. Skrypt, który należy wykonać na bazie pokazałem na poniższym listingu:

CREATE TABLE Position
(
ID int IDENTITY PRIMARY KEY,
Name varchar(20) NOT NULL
)

INSERT INTO Position VALUES
('Murarz'), ('Tynkarz'),('Zegarmistrz')

ALTER TABLE Person
ADD CONSTRAINT FK_Person_Position
FOREIGN KEY (PositionID)
REFERENCES Position(ID)

Przy okazji wrzuciłem do tabeli kilka rekordów:

ID	Name
1	Murarz
2	Tynkarz
3	Zegarmistrz

Do przetestowania zapytań te kilka rekordów powinno wystarczyć.

Po zmodyfikowaniu struktury bazy danych należy odświeżyć kontekst bazy danych w Entity Framework, a następnie wykonać już nieco bardziej rzeczywiste, obiektowe złączenie dwóch różnych tabel:

var query = from p in db.Person
select new { p.FirstName, p.LastName, p.Position.Name };

Pokazane powyżej instrukcje równoważne są następującemu zapytaniu SQL:

SELECT
1 AS [C1],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent2].[Name] AS [Name]
FROM [dbo].[Person] AS [Extent1]
LEFT OUTER JOIN [dbo].[Position] AS [Extent2] ON [Extent1].[PositionID] = [Extent2].[ID]

Otrzymaliśmy lewe złączenie. Czy o takie nam chodziło? Czy nie chodziło nam czasem o złączenie wewnętrzne? Popatrzmy na nieco inną konstrukcję złączenia:

var query = from p in db.Person
join pos in db.Position on p.PositionID equals pos.ID
select new { p.FirstName, p.LastName, pos.Name };

Tym razem otrzymamy nieco inny rezultat. Jawnie podaliśmy, że chodzi nam o złączenie wewnętrzne i takie też otrzymamy:

SELECT
[Extent2].[ID] AS [ID],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent2].[Name] AS [Name]
FROM [dbo].[Person] AS [Extent1]
INNER JOIN [dbo].[Position] AS [Extent2] ON [Extent1].[PositionID] = [Extent2].[ID]

Skąd zatem taka rozbieżność? Czy każde obiektowe odwołanie zostanie przetłumaczone na lewe złączenie zewnętrzne? Wiemy dobrze że JOIN i LEFT JOIN to nie jest to samo. Celowo pokazałem taki przykład, aby w sposób płynny wskazać zależność rodzaju złączenia od sposobu definicji relacji w bazie. Warto zapamiętać sobie tę zależność, bo jest to jeden z najczęściej popełnianych błędów:

Jeżeli kolumna klucza obcego przyjmuje wartość NULL, wykonywane jest lewe złączenie.
Jeżeli kolumna klucza obcego nie może przyjmować wartości NULL, wykonywane jest złączenie wewnętrzne.

Aby pokazać różnicę wykonam drobną modyfikację tabeli i założę więzy NOT NULL:

ALTER TABLE Person
ALTER COLUMN PositionID int NOT NULL

Teraz, gdy wykonamy złączenie obiektowe (po wcześniejszej aktualizacji kontekstu bazy danych), otrzymamy zwykły JOIN:

--var query = from p in db.Person
-- select new { p.FirstName, p.LastName, p.Position.Name };

SELECT
[Extent1].[PositionID] AS [PositionID],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent2].[Name] AS [Name]
FROM [dbo].[Person] AS [Extent1]
INNER JOIN [dbo].[Position] AS [Extent2] ON [Extent1].[PositionID] = [Extent2].[ID]

Warto przy okazji zwrócić uwagę na jedną dodatkową kolumnę, PositionID, wykorzystywaną przez wewnętrzne mechanizmy Entity Framework. Nie jest to wielki narzut, bo jest to kolumna, po której następuje złączenie. Silnik bazy danych i tak tę kolumnę musi pobrać - skanując tabelę lub z odczytując wartość z pasującego indeksu. Nie ma to zatem wpływu na sposób dostrajania bazy danych.

Pułapki zapytań obiektowych

Zapytania obiektowe są bardzo ładne i mogą kusić. Pokazałem już, że należy się strzec nieprawidłowych złączeń wynikających z niedopatrzenia lub błędnej definicji typu klucza obcego. Lewe złączenie zamiast złączenia wewnętrznego może czasem doprowadzić do otrzymania niespodziewanych rezultatów lub wyjątku NullReferenceException. Pamiętajmy, że dowolne porównanie z NULL w SQL da w wyniku wartość logiczną false.

Obiektowy dostęp do pól to jednak nie tylko ryzyko nieprawidłowych złączeń. Jest coś, co potrafi zdusić nawet najlepiej zaprojektowaną bazę umieszczoną na najlepszym serwerze. Popatrzmy na poniższy przykład i spróbujmy powiedzieć, co w nim podejrzanego. Od razu powiem, że kod kompiluje się i pozwala wypisać imię, nazwisko i stanowisko danej osoby:

var query = from p in db.Person
select p;

foreach (var p in query)
{
Console.WriteLine("{0} {1}: {2}", p.FirstName, p.LastName, p.Position.Name);
}

Wydaje się, że jest to czysty, elegancki i dobry kod. Tak się wydaje, dopóki nie zerkniemy na generowane przez ten niewielki fragment zapytania do bazy:

SELECT
[Extent1].[ID] AS [ID],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Email] AS [Email],
[Extent1].[PositionID] AS [PositionID]
FROM [dbo].[Person] AS [Extent1]

exec sp_executesql N'SELECT
[Extent1].[ID] AS [ID],
[Extent1].[Name] AS [Name]
FROM [dbo].[Position] AS [Extent1]
WHERE [Extent1].[ID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=1

exec sp_executesql N'SELECT
[Extent1].[ID] AS [ID],
[Extent1].[Name] AS [Name]
FROM [dbo].[Position] AS [Extent1]
WHERE [Extent1].[ID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=2

Podałem przykład ekstremalny, bo taki łatwiej zapamiętać. Po pierwsze, pobierany jest cały obiekt Person. Jeżeli w bazie danych znajduje się jakiś indeks pokrywający na imieniu i nazwisku (być może na jeszcze innych polach), nie będzie mógł być użyty. Zrealizowane zostanie pełne skanowanie tabeli lub indeks CLUSTERED. Nie to jest jednak najgorsze. Dla każdej z osób - oddzielnym zapytaniem - pobierana jest informacja o stanowisku. W pokazanym przypadku istniejące osoby zajmują tylko dwa stanowiska. Nietrudno sobie wyobrazić sytuację, w której takich różnych stanowisk jest kilkanaście i tyle też pytań zostanie wysłanych do bazy. Trzeba też wiedzieć, że jest to zapytanie wyjątkowo proste. Gdy zapytania się komplikują, dane pobierane są z kilku tabel - sytuacja może wyglądać dramatycznie. Setki, tysiące zapytań bombardują bazę danych, a ta, grzecznie wykonuje każde polecenie. Bezszelestnie, nie dając żadnych oznak - dopóki system nie urośnie i baza przestanie nadążać z ciągle rosnącą liczbą zadań do przetworzenia. Wtedy być może ktoś się tym zainteresuje.

Zapytania zwracające kolekcje

Przyjrzyjmy się jeszcze, jak Entity Framework tłumaczy zapytania zwracające kolekcje. Przypuśćmy, że chcemy pobrać wszystkie stanowiska i listę osób, które takie stanowiska zajmują. Zapytanie będzie wyglądało mniej więcej tak:

Pokazana instrukcja wygeneruje następujący kod SQL:

SELECT
[Project1].[ID] AS [ID],
[Project1].[Name] AS [Name],
[Project1].[C1] AS [C1],
[Project1].[ID1] AS [ID1],
[Project1].[FirstName] AS [FirstName],
[Project1].[LastName] AS [LastName],
[Project1].[Email] AS [Email],
[Project1].[PositionID] AS [PositionID]
FROM ( SELECT
    [Extent1].[ID] AS [ID],
    [Extent1].[Name] AS [Name],
    [Extent2].[ID] AS [ID1],
    [Extent2].[FirstName] AS [FirstName],
    [Extent2].[LastName] AS [LastName],
    [Extent2].[Email] AS [Email],
    [Extent2].[PositionID] AS [PositionID],
    CASE WHEN ([Extent2].[ID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    FROM [dbo].[Position] AS [Extent1]
    LEFT OUTERJOIN [dbo].[Person] AS [Extent2] ON [Extent1].[ID] = [Extent2].[PositionID]
)  AS [Project1]
ORDER BY [Project1].[ID] ASC, [Project1].[C1] ASC

Zapytanie może nieco przerażać. To jest jedno z tych miejsc, w którym pojawia się wielki dysonans pomiędzy programistą .NET a administratorem bazy danych SQL. Programista napisał krótkie, przejrzyste zapytanie, administrator otrzymał zaś długie zapytanie z tabelą pochodną, dodatkową kolumną wyliczaną po złączeniu, sortowaniem. Skąd to wszystko się bierze? Kolumna wyliczana pomaga w osiągnięciu znanego już mechanizmu rozróżniania wartości NULL reprezentujących pola rekordu od wartości NULL powstałych w wyniku zewnętrznego złączenia. Sortowanie jest bardziej zagadkowe. Pamiętajmy, że Entity Framework musi skonstruować obiekty. Obiekty mają postać drzewa (Stanowisko-Osoby), a SQL zwraca wyniki w postaci tabelarycznej. Po środku musi być jakiś mechanizm, algorytm, który to wszystko razem poskłada.

Sortowanie jest potrzebne właśnie do tego prostego algorytmu:

Jako klucz przyjmij identyfikator rekordu, który w wersji obiektowej będzie zawierał kolekcję dzieci. Przeglądaj kolejne rekordy zwrócone z bazy danych.
(*)Zapamiętaj klucz bieżącego rekordu.
Jeżeli kolumny reprezentujące rekord dziecka nie zawierają danych (przyda się magiczna kolumna C1), zwróć rekord rodzica z pustą listą i skocz do (*). Jeżeli dane są to:
- Dopóki klucz jest ten sam pobieraj kolejne rekordy i dodawaj do kolekcji kolejne rekordy dzieci.
- Jeżeli klucz jest inny, zwróć skumulowany rekord z uzupełnioną kolekcją i skocz do (*).

Algorytm wymaga odpowiedniego ułożenia rekordów - takie same klucze muszą być obok siebie. To zapewnia sortowanie. Algorytm należy rozbudować tak, aby odpowiednio interpretować koniec listy rekordów zwracanych z bazy danych.

Podsumowanie

Kończąc pierwszą część (tak, będzie ich więcej), zachęcam do przejrzenia swoich własnych zapytań i generowanych przez nie instrukcji SQL. Zachęcam też do dzielenia się ciekawymi spostrzeżeniami w komentarzach. Pamiętam, jak kiedyś czytałem o sposobie komunikacji w .NET Remoting i o zdalnej obiektowości. Tam też istniał problem zbyt dużego ukrywania implementacji. Korzystający z obiektu nie był świadomy tego, że pod płaszczykiem zwykłego przypisania wartości do właściwości kryło się wywołanie sieciowe. Nie wiedział, że tak prosta operacja wymaga serializacji, transmisji i deserializacji po drugiej stronie i że te operacje mogą angażować cały system operacyjny, włącznie z mechanizmami zabezpieczeń sieci. Nie był świadomy zagrożenia, bo myślał, że to zwykły obiekt. To ukrywanie implementacji, tak pożądane w programowaniu obiektowym, okazywało się zgubne. W pewnym sensie ma to też swoje odbicie w Entity Framework. Trudno dobrze pisać instrukcje pobierające dane bez choćby drobnej znajomości mechanizmów bazodanowych i trudno napisać wydajny system nie zwracając uwagi na pewne, opisane tutaj, szczegóły. Mam nadzieję, że choć trochę otworzyłem oczy na niektóre problemy, które mogą się pojawić.

Kategoria:C#SQL Server Entity Framework

Paweł Dyl, 2013-12-20

Spis treści: