LIKE w Entity Framework

Logiczna warstwa LINQ To Entity

Generałowi urodził się wnuk, więc postanowił wysłać swego adiutanta do szpitala, aby się dowiedział czegoś więcej. Po powrocie generał pyta:
- I jak wygląda?
Na to adiutant:
- Jest bardzo ładny... Podobny do Pana generała.
- Podać więcej szczegółów!
- Melduję, że niski, łysy i bez przerwy drze mordę.

Jakiś czas temu pojawiło się kilka wpisów na temat fizycznych zapytań generowanych przez LINQ To Entity do bazy. Pozornie proste instrukcje Entity Framework mogą generować złożone zapytania SQL. Jest nawet gorzej - pozornie proste operacje mogą generować dziesiątki lub tysiące drobnych, niezależnych zapytań (zobacz między innymi wpis Entity Framework i rzeczywiste zapytania SQL Server). W zapytaniach wykorzystujących operator LIKE z SQL Server również czyhają pułapki, a o kilku innych rzeczach warto wiedzieć. To dlatego postanowiłem nieco rozjaśnić temat.

Entity Framework nie ma funkcji LIKE?

Zauważyłem, że co jakiś czas wśród osób pracujących z Entity Framework pojawia się pytanie w stylu: Jak zrobić w tym Entity LIKE?. Problem wynika z tego, że wśród metod próżno szukać czegoś na kształt LIKE i nie pomoże tutaj nawet IntelliSense. Wiem, że zasadą programowania obiektowego jest ukrywanie wewnętrznej implementacji, ale z drugiej strony - kto skojarzy, że LIKE to Contains? Przejdźmy do rzeczy i stwórzmy sobie tabelę testową:

CREATE TABLE Countries
(
  Name varchar(30)
    CONSTRAINT PK_Countries PRIMARY KEY,
  FullName nvarchar(100) NOT NULL,
  [Population] int NOT NULL,
  Area int NOT NULL
)

INSERT INTO Countries VALUES('Congo', N'Republic of the Congo', 4366266, 342000);
INSERT INTO Countries VALUES('Poland', N'Republic of Poland', 38544513, 312679);
INSERT INTO Countries VALUES('USA', N'United States of America', 316962000, 9826675);
INSERT INTO Countries VALUES('United Kingdom', N'United Kingdom of Great Britain and Northern Ireland', 63181775, 243610);
INSERT INTO Countries VALUES('Germany', N'Federal Republic of Germany', 80523700, 357021);
INSERT INTO Countries VALUES('Spain', N'Kingdom of Spain', 46704314, 505992);

Tabela reprezentuje listę państw z nazwami, pełnymi nazwami, liczbą mieszkańców i powierzchnią. Taki dotatkowy zastrzyk wiedzy, gdyby kogoś to interesowało. Popatrzmy teraz na zapytanie generujące po stronie SQL warunek LIKE:

using (var ctx = new CountriesContext())
{
    var query = from c in ctx.Countries
                where c.FullName.Contains("Republic")
                select c;

    var result = query.ToList();
}

Gdybyśmy teraz obejrzeli zapytanie wygenerowane przez Entity Framework zobaczylibyśmy co następuje:

SELECT
    [Extent1].[Name] AS [Name],
    [Extent1].[FullName] AS [FullName],
    [Extent1].[Population] AS [Population],
    [Extent1].[Area] AS [Area]
    FROM [dbo].[Countries] AS [Extent1]
    WHERE [Extent1].[FullName] LIKE N'%Republic%'

Warto zatem zapamiętać: LIKE w bezpośrednim przełożeniu to Contains.

Modyfikowanie szablonu dopasowania

Funkcja Contains domyślnie dokłada znaki '%' po obu stronach porównywanej wartości. Stosując operator LIKE nie zawsze o to nam chodzi. A gdybyśmy tak zechcieli wyszukać wszystkie wartości rozpoczynające się od wskazanej sekwencji? Do wartości w SQL dodalibyśmy znak procenta na końcu, w Entity Framework należy skorzystać z metody StartsWith. Popatrzmy na poniższy fragment:

var query = from c in ctx.Countries
where c.Name.StartsWith("U")
select c;

Po stronie SQL otrzymamy następujące zapytanie:

Analogicznie postępujemy szukając wartości tekstowych kończących się wskazanym szablonem. Zamiast StartsWith korzystamy z metody EndsWith:

var query = from c in ctx.Countries
where c.Name.EndsWith("d")
select c;

Otrzymamy minimalnie inne zapytanie:

Pokazane zapytania są bardzo podobne, dlatego kolejny podpunkt jest tak ważny.

Wewnątrz SQL - plany wykonania i indeksy

Nie jest prawdą, że Entity Framework, podobnie jak inne narzędzia typu O/RM, zwalania nas z obowiązku myślenia o bazie danych. W małych projektach nie jest to istotne, w dużych - prędzej czy później dadzą o sobie znać algorytmy realizujące, przesyłane przy pomocy SQL, zadania. Te z pozoru podobne zapytania mogą mieć mocno odmienne plany wykonania i tak też jest w tym przypadku. Kolumna z nazwą (Name) jest kluczem głównym. Klucz główny jest zawsze skojarzony z odpowiednim indeksem. To ten indeks robi różnicę (podobne rozważania można przeprowadzić na dowolnej kolumnie tekstowej z indeksem). Indeks sprawia, że wartości zaczynające się od tej samej litery będą zawsze obok siebie, w skupieniu. To pozwala silnikowi bazy danych przejrzeć tylko wąski zakres wszystkich rekordów. Silnik odczyta tylko te strony pamięci, w których ten zawężony obszar się znajduje. Nie musi czytać wszystkiego. Rekordy kończące się na tę samą literę są rozrzucone i najczęściej nie ma innej możliwości niż przeskanowanie (odczytanie kolejno) wszystkich stron tabeli. W pokazanym przykładzie tych rekordów jest bardzo mało i nie dostrzeżemy tego gołym okiem. Dostrzeżemy to jednak analizując plany wykonania. Popatrzmy na plan metody StartsWith:

Obrazek reprezentujący mniej kosztowny operator Clustered Index Seek w planie wykonania — Plan wykonania zapytania z warunkiem LIKE i filtrem na początku.

Minimalnie inny będzie plan wykonania metody EndsWith:

Obrazek reprezentujący bardziej kosztowny operator Clustered Index Scan w planie wykonania — Plan wykonania zapytania z warunkiem LIKE i filtrem na końcu.

Różnica niby drobna, ale przełożona na kilkadziesiąt tysięcy lub nawet miliony rekordów potrafi mocno dokuczyć. Lepiej od razu wiedzieć niż potem poprawiać. Warto też wspomnieć, że bardziej finazyjne metody dopasowania szablonu również będą najczęściej wymagały pełnego przeskanowania tabeli. O jakie to inne metody chodzi?

Wzorzec ciągu znaków

Bardziej doświadczeni programiści baz danych znają możliwości operatora LIKE i wiedzą, że pokazane powyżej operacje to tylko czubek góry lodowej. Takie są niestety ograniczenia Entity Framework i innych narzędzi typu O/RM. Kilka ciekawszych warunków z zastosowaniem operatora LIKE można znaleźć we wpisie SQL Server i wyszukiwanie znaków specjalnych w tekście lub Znaki specjalne SQL w zapytaniach LIKE. Smutne to, ale na tym etapie Entity Framework klęka. Na szczęście jest funkcja alternatywna, PAT_INDEX, która, odpowiednio użyta, zastąpi nam LIKE. Popatrzmy na poniższy fragment:

var query = from c in ctx.Countries
where SqlFunctions.PatIndex("_o_a%", c.Name) > 0
select c;

Wygenerowane zostanie następujące zapytanie:

Zapytanie staje się, co tu dużo mówić, śmieszne. Obserwując to od strony bazy danych trudno tak na pierwszy rzut oka zrozumieć intencje programisty Entity Framework. Niestety - jest to cena jaką się czasami płaci za narzędzia O/RM.

Entity Framework 5.0 i 6.0

Metody z klasy SqlFunctions reprezentują mapowania typów .NET na instrukcje SQL. W wersjach Entity Framework do 5.0 takie metody oznaczane były atrybutem EdmFunction (EdmFunctionAttribute). Od wersji 6.0 atrybut EdmFunction oznaczony jest jako przestarzały, a jego następcą mianowano atrybut DbFunction (DbFunctionAttribute). Sprawia to, że metody działające w Entity Framework 5.0 i przekopiowane do Entity Framework 6.0 mogą zwyczajnie przestać działać informując nas takim oto komunikatem:

LINQ to Entities does not recognize the method 'System.Nullable`1[System.Int32] PatIndex(System.String, System.String)' method, and this method cannot be translated into a store expression.

W takiej systuacji można tymczasowo zamienić przestrzeń nazw System.Data.Objects.SqlClient na System.Data.Entity.SqlServer i mieć nadzieję, że sprawa zostanie szybko uporządkowana. To kolejna cena, którą płaci się za ślepe korzystanie z wszelakich nowości. Uzupełniając informacje należy napisać, że przestrzenie znajdują się odpowiednio w bibliotekach System.Data.Entity.dll i EntityFramework.SqlServer.dll dla dostawcy SQL Server.

Przyznam, że tak prywatnie jestem zaskoczony takimi rozbieżnościami w tłumaczeniu zapytań Entity Framework na odpowiedniki SQL z operatorem LIKE. Jest to o tyle ciekawe, że filtrowanie przy pomocy LIKE jest jedną z najbardziej podstawowych operacji na bazach danych, zdefiniowaną w standardzie i wspieraną przez większość, o ile nie wszystkich ważniejszych graczy na rynku baz danych.

Znaki specjalne LIKE w zapytaniach Entity Framework

Składnia LIKE jest bardziej skomplikowana niż się to na pierwszy rzut oka wydaje (zostało to lepiej opisane na podanych wcześniej stronach). Co jednak zrobi Entity Framework gdy zechcemy go nieco oszukać? Popatrzmy na poniższy przykład:

var query = from c in ctx.Countries
where c.Name.StartsWith("[[]")
select c;

A teraz popatrzmy na wygenerowane w SQL Server zapytanie:

Widać wyraźnie, że SQL Server nie daje się w żaden sposób oszukać. Wszystkie podejrzane znaki poprzedzone są znakiem ucieczki kasującym ich ponadprzeciętne zdolności.

Obsługa operatora LIKE w Entity Framework może się wydawać dziwna, ale to tylko kwestia przyzwyczajenia. Chciałoby się czasami konstrukcji typowych dla SQL i tej prostoty. Nie ma jednak nic za darmo. Narzędzia O/RM mają działać na różnych bazach, mają ukrywać złożoność języka SQL - definiują zatem podzbiór najczęściej wykonywanych konstrukcji, opakowując to w funkcje znane i wykorzystywane powszechnie w świecie obiektowym. To, że na tej granicy powstają zgrzyty... to normalne.

Kategoria:C#SQL Server Entity Framework

Paweł Dyl, 2013-12-20

Spis treści: