Spis treści:

Kategoria:C#SQL ServerEntity Framework


LEFT JOIN w Entity Framework

Złączenia wewnętrzne i zewnętrzne

W teorii baz danych istnieją dwie podstawowe grupy złączeń: wewnętrzne i zewnętrzne. Istnieją co prawda inne rodzaje podziałów i można poczytać o samozłączeniachSamozłączenie to złączenie tabeli samej ze sobą - może być wewnętrzne i zewnętrzne. Wykorzystywane na przykład do definicji struktur drzewiastych, złączeniach naturalnychTabele dopasowywane są na podstawie atrybutów. To złączenie wewnętrzne, w którym atrybuty o tej samej nazwie muszą się pokrywać., iloczynach kartezjańskichIloczyn kartezjański to złączenie szczególnego rodzaju. Nie ma żadnego predykatu złączenia, więc trudno mówić o sposobie łączenia. W rezultacie takiego złączenia powstają pary rekordów w postaci każdy z każdym., ale zdecydowana większość będzie należała do jednej z tych dwóch, wymienionych przeze mnie grup.

Podstawowa różnica jest taka: przy złączeniu wewnętrznym zwracane są tylko te pary rekordów, które spełniają warunek złączenia (predykat). Przy złączeniu zewnętrznym zwracane są pary rekordów spełniających predykat, ale także rekordy, dla których nie znaleziono dopasowania. To, które rekordy bez dopasowania będą zwracane, zależy od rodzaju złączenia zewnętrznego. Zasadniczo są to następujące rodzaje złączeń zewnętrznych:

  • Lewe (LEFT JOIN, LEFT OUTER JOIN) - jeżeli rekord po lewej stronie operatora nie ma dopasowania, zwracany jest w postaci pary, ale z prawej strony, zamiast dopasowanego rekordu, zwracane są wartości NULL.
  • Prawe (RIGHT JOIN, RIGHT OUTER JOIN) - jeżeli rekord po prawej stronie operatora nie ma dopasowania, zwracany jest w postaci pary, ale z lewej strony, zamiast dopasowanego rekordu, zwracane są wartości NULL.
  • Obustronne (FULL JOIN, FULL OUTER JOIN) - połączenie obu powyższych. Zwracane są wszystkie rekordy z dopasowaniem, rekordy z lewej bez dopasowania i rekordy z prawej bez dopasowania.

Słowo kluczowe OUTER w złączeniach zewnętrznych jest opcjonalne. Celowo pomijam inne rodzaje złączeń (SQL Server ma w swoim repertuarze jeszcze złączenia typu CROSS APPLY, OUTER APPLY), ale nie są one temtem tego wpisu. Zainteresowanych odsyłam do dokumentacji. Przejdźmy zatem do rzeczy.

Tabele wykorzystywane w testach

Tabele wykorzystywane w przykładzie zostały już przeze mnie kiedyś napisane (zobacz Entity Framework i rzeczywiste zapytania SQL Server). Tam też pokazałem, w którym momencie wykonywane są niejawne złączenia zewnętrzne. Zdarza się bowiem, że wykonując zwykłe złączenia otrzymamy w efekcie złączenia zewnętrzne. Zainteresowanych odsyłam do wskazanego artykułu. Dla leniwych jeszcze raz umieszczę skrypty:

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

INSERT INTO Person VALUES
('Tomasz''Wielki''wielki@malpa.pl', 1),
('Tomasz''Niewielki''niewielki@malpa.pl', 2),
('Krzysztof''Niebieski''niebieski@malpa.pl', 2)

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)

Na tak przygotowanej tabeli mozna juz ćwiczyć.

LEFT JOIN w LINQ To SQL i Entity Framework

We wspomnianym wcześniej artykule wspomniałem o wpływie relacji na generowane zapytanie. Jeżeli relacja jest typu wiele do jednego, generowane jest złączenie wewnętrzne. Jażeli relacja jest typu wiele do zera lub jednego - generowane jest złączenie zewnętrzne. W pokazanym przykładzie kolumna PositionID może przyjmować wartość NULL, dlatego jest to relacja do zera lub jednego. Aby otrzymać lewe złączenie wystarczy wobec tego napisać kod podobny do poniższego:

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

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

Do bazy SQL wysłane zostanie takie oto żądanie:

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

Nie zawsze można jednak polegać na definicji tabeli. Na szczęście istnieje inny sposób.

Jawne wskazanie złączenia zewnętrznego

Przypuśćmy, że chcemy pobrać wszystkie stanowiska i osoby zajmujące te stanowiska. Dla stanowiska nieobsadzonego chcemy zwrócić pusty rekord osoby. Popatrzmy na przykładowy sposób rozwiązania tego zadania:

var query = from pos in db.Position
            join per in db.Person on pos.ID equals per.PositionID into nullablePersons
            from nullPer in nullablePersons.DefaultIfEmpty()
            select new { pos.Name, PersonObject = nullPer};

foreach (varin query)
{
    if (p.PersonObject != null)
        Console.WriteLine("{0}: {1} {2}", p.Name, p.PersonObject.FirstName, p.PersonObject.LastName);
    else
        Console.WriteLine("{0}: BRAK", p.Name);
}

Warto zwrócić uwagę na dwa elementy:

  • Słowo kluczowe into - dane tabeli wrzucane są do symbolicznej zmiennej nullablePerson.
  • Funkcja DefaultIfEmpty - jeżeli rekord nie znalazł dopasowania, zwracana jest wartość domyślna.

Pokazane powyżej instrukcje wygenerują następujące zapytanie SQL:

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]
FROM [dbo].[Position] AS [Extent1]
LEFT OUTER JOIN [dbo].[Person] AS [Extent2] ON [Extent1].[ID] = [Extent2].[PositionID]

Otrzymaliśmy lewe złączenie zewnętrzne, ale nie jest ono optymalne w tym przypadku. Nie ma potrzeby pobierać innych pól klasy Person, więc lepiej je pominąć. Można to zrobić na przykład tak:

var query = from pos in db.Position
            join per in db.Person on pos.ID equals per.PositionID into nullablePersons
            from nullPer in nullablePersons.DefaultIfEmpty()
            select new { pos.Name, nullPer.FirstName, nullPer.LastName};

Tym razem otrzymamy znacznie lepsze zapytanie, pozbawione większości niepotrzebnych pól. Do zapytania dorzucony zostanie tylko identyfikator stanowiska, wykorzystywany wewnętrznie przez Entity Framework:

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

Wykorzystanie właściwości nawigacyjnej

Istnieje nieco skrócony sposób uzyskania tego samego rezultatu:

var query = from pos in db.Position
        from per in pos.Person.DefaultIfEmpty()
        select new { pos.Name, per.FirstName, per.LastName };

Wydaje się, że baza danych wykona w tym przypadku pełny iloczyn kartezjański. Entity Framework jest jednak na tyle sprytne, że wykona lewe złączenie zewnętrzne, takie samo jak poprzednia instrukcja. Skrócony zapis ma jedną drobną wadę - działa tylko na właściwościach nawigacyjnych. Pierwszy przykład jest bardziej elastyczny i nie wymaga żadnego pokrewieństwa pomiędzy łączonymi tabelami.

Podsumowanie

Prawidłowe zrozumienie sposobu łączenia tabel jest kluczowe w wielu zapytaniach SQL. Z jednej strony złączenie wewnętrzne może odrzucać rekordy, dla których nie znaleziono dopasowania. Z drugiej - zastosowanie lewego złączenia może doprowadzić do powstawania znacznie większych zbiorów danych niż oczekujemy. Jeszcze większe zagrożenie pojawia się w przypadku łączenia więcej niż dwóch tabel. Zasadą jest, że jeżeli w zapytaniu zastosujemy choć jedno złączenie zewnętrzne, należy ten rodzaj złączenia stosować konsekwentnie do końca zapytania. Pamiętajmy, że złączenie zewnętrzne może generować wartości NULL, a porównania z wartością NULL przez silniki baz danych interpretowane jest jako zdanie fałszywe (rekordy będą odrzucane). Na koniec jeszcze słowo o prawym złączeniu wewnętrznym. Nie rozwijałem tego tematu, bo uznałem, że nie ma takiej potrzeby. Prawe złączenie może być zrealizowane za pomocą lewego złączenia - wystarczy tylko zamienić kolejność tabel biorących udział w operacji. Przyjęło się, że w programowaniu baz danych stosuje się lewe złączenia zewnętrzne. Frekwencyjnie takie lewe złączenia zdominowały swój odwrotny odpowiednik. Trochę w złym guście jest używanie prawych złączeń zewnętrznych. A już zupełnie w złym guście jest używanie w jednym zapytaniu i prawych i lewych złączeń. Nie warto sobie komplikować życia.

Kategoria:C#SQL ServerEntity Framework

, 2013-12-20

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
Dzieki za rozjasnienie zagadnienia upsert. wlasnie sie ucze programowania :).
Co się stanie gdy spróbuję wyszukać:
SELECT * FROM NV_Airport WHERE Code='SVO'
SELECT * FROM V_Airport WHERE Code=N'SVO'
(odwrotnie są te N-ki)
Będzie konwersja czy nie znajdzie żadnego rekordu?