LIKE w Entity Framework
Logiczna warstwa LINQ To Entity
- 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ę.
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ą:
(
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:
{
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:
[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:
where c.Name.StartsWith("U")
select c;
Po stronie SQL otrzymamy następujące zapytanie:
[Extent1].[Name] AS [Name],
[Extent1].[FullName] AS [FullName],
[Extent1].[Population] AS [Population],
[Extent1].[Area] AS [Area]
FROM [dbo].[Countries] AS [Extent1]
WHERE [Extent1].[Name] LIKE 'U%'
Analogicznie postępujemy szukając wartości tekstowych kończących się wskazanym szablonem. Zamiast StartsWith korzystamy z metody EndsWith:
where c.Name.EndsWith("d")
select c;
Otrzymamy minimalnie inne zapytanie:
[Extent1].[Name] AS [Name],
[Extent1].[FullName] AS [FullName],
[Extent1].[Population] AS [Population],
[Extent1].[Area] AS [Area]
FROM [dbo].[Countries] AS [Extent1]
WHERE [Extent1].[Name] LIKE '%d'
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:
Minimalnie inny będzie plan wykonania metody EndsWith:
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:
where SqlFunctions.PatIndex("_o_a%", c.Name) > 0
select c;
Wygenerowane zostanie następujące zapytanie:
[Extent1].[Name] AS [Name],
[Extent1].[FullName] AS [FullName],
[Extent1].[Population] AS [Population],
[Extent1].[Area] AS [Area]
FROM [dbo].[Countries] AS [Extent1]
WHERE ( CAST(PATINDEX(N'_o_a%', [Extent1].[Name]) AS int)) > 0
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:
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:
where c.Name.StartsWith("[[]")
select c;
A teraz popatrzmy na wygenerowane w SQL Server zapytanie:
[Extent1].[Name] AS [Name],
[Extent1].[FullName] AS [FullName],
[Extent1].[Population] AS [Population],
[Extent1].[Area] AS [Area]
FROM [dbo].[Countries] AS [Extent1]
WHERE [Extent1].[Name] LIKE '~[~[]%' ESCAPE '~'
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 ServerEntity Framework
Brak komentarzy - bądź pierwszy