Dynamiczny SELECT w LINQ
Wybieranie wskazanej kolumny
Mapowania obiektowo-relacyjne mają swoje wady i zalety. Mają nam uprościć pracę, ale nie dzieje się to bez żadnych kosztów. Część instrukcji, które można wykonać w języku SQL, zwłaszcza te trudniejsze lub specyficzne dla konkretnego serwera bazy danych, są pomijane. Mapowania obiektowo-relacyjne zabezpieczają nas przed wykonaniem potencjalnie niebezpiecznych operacji, ale w pewnym sensie ograniczają nam swobodę. Ponadto, zwykłe kwerendy wymagają jawnego podania typu i jawnego wskazania właściwości (atrybutów reprezentujących kolumny z bazy danych). C# to język silnie typowany, a to silne typowanie przechodzi do LINQ.
Jak zatem w LINQ wybrać kolumnę dynamicznie, skoro musimy napisać obiekt.Właciwość? Tu z pomocą przychodzą drzewa wyrażeń.
Przedstawienie problemu
Załóżmy, że mamy taką oto tabelę:
| ID | Icon32x32 | Icon128x128 | FullSize |
|---|---|---|---|
| 1 | Ikona 32x32 | Ikona 128x128 | Pełny rozmiar |
| 2 | Ikona 32x32 | Ikona 128x128 | Pełny rozmiar |
| ... | ... | ... | ... |
Chcemy na interfejsie użytkownika przedstawić listę użytkowników, książek, samochodów czy czegokolwiek innego ale... w zależności od ustawień będą to małe ikonki, średnie ikonki lub lista pełnowymiarowych obrazków. Jak by to wyglądało w SQL? W zależności od potrzeby:
SELECT ID, Icon32x32 FROM Icons
--Dla ikony 128x128
SELECT ID, Icon128x128 FROM Icons
--Dla ikony FullSize
SELECT ID, FullSize FROM Icons
Co można w takim przypadku zrobić? Popatrzmy na przykład, który pokaże, w czym problem. Chcemy uzyskać teki oto rezultat:
{
int ID { get; set; }
byte[] RawData { get; set; }
public Icon(int id, Binary binary)
{
this.ID = id;
this.RawData = binary.ToArray();
}
public Icon()
{
}
}
Sęk w tym, żeby dane binarne pochodziły, w zależności od parametru, z kolumny Icon32x32, Icon128x128 lub FullSize. Przyjrzyjmy się podstawowemu zapytaniu:
{
int id = 1;
var result1 = from o in context.Icons
where o.Id == id
select new Icon(o.Id, o.Icon32x32);
Jak zamienić właściwość Icon32x32 na inną? Niby żaden problem, ale w takim przypadku musimy to zrobić w kodzie, na stałe. Nie da się napisać przecież czegoś takiego:
{
int id = 1;
var result1 = from o in context.Icons
where o.Id == id
select new Icon(o.Id, o."Icon32x32");
Problem nie został omówiony, ale został podkreślony. Ponawiam pytanie: co można zrobić? Jest kilka opcji. Większość skłania się ku jednej - dynamicznym zapytaniom SQL.
Czyste zapytania SQL
Czyste, czy jak kto woli dynamiczne zapytania SQL to ratunek dla większości zaawansowanych użytkowników SQL Server. Są takie zapytania SQL, których nie da się wykonać przy pomocy LINQ. LINQ dla SQL nie obługuje między innymi instrukcji MERGE oraz funkcji okna (wchodzących w skład standardu SQL), nie mówiąc już o instrukcjach typowych dla SQL Server (np. wspólne wyrażenia tablicowe, CTE, ang. common table expressions).
Użytkownicy LINQ dla znają zapewne mechanizm przekazywania parametrów do zapytań, który pokazałem poniżej:
var result2 = context.ExecuteQuery<Icon>("SELECT Id ID, Icon32x32 RawData FROM Icons WHERE Id={0}", id)
.FirstOrDefault();
Naturalne wydaje się zastosowanie takiej samej konstrukcji dla nazwy, kolumny:
//var result2_1 = context.ExecuteQuery<Icon>("SELECT Id ID, {0} RawData FROM Icons WHERE Id={1}", column, id)
// .FirstOrDefault();
Zostanie to jednak skonwertowane, zgodnie zresztą z regułami SQL, na instrukcję typu:
Parametr @param0 będzie wartością stałą! Nie wstawi się jako nazwa kolumny! Jeżeli stosujemy podejście z dynamicznym zapytaniem SQL, można to zrobić tak:
//Make sure it's safe string!
var result3 = context.ExecuteQuery<Icon>(string.Format("SELECT Id ID, {0} RawData FROM Icons WHERE Id={{0}}", column), id)
.FirstOrDefault();
Trochę dziwna konstrukcja, więc wyjaśniam: część, która odpowiedzialna jest za wygenerowanie właściwej kolumny, umieszczona jest w metodzie string.Format. Parametr, bo tak należy przekazywać parametry, jest w zewnętrznej metodzie ExecuteQuery(). Zabezpiecza to, przynajmniej w częsci odpowiedzialnej za parametr, za ataki typu SQL Injection. Jakie jest zagrożenie? Popatrzmy na następujący przykład:
"1 Dummy; DELETE FROM XXX;--", id);
//query = SELECT Id ID, 1 Dummy; DELETE FROM XXX;-- RawData FROM Icons WHERE Id=1
Przykład prosty i prymitywny, ale powinien dać do myślenia. Samo zagadnienie SQL Injection jest zbyt obszerne i być może zostanie omówione oddzielnie. Wersja z przekazywaniem tylko nazwy kolumny nie jest od tego zagrożenia wolna, dlatego należy zapewnić wszelkie środki zabezpieczające przed dostaniem się do metody niewłaściwego łańcucha znaków, zastosować typ wyliczeniowy jako parametr i obsłużyć go w środku, przebadać łańcuch wejściowy na obecność znaków innych niż litery (zakładając że nazwy kolumn składają się tylko z liter) lub zastosować dowolną inną, przemyślną metodę.
Nie wykluczam, że wiele osób taką metodę zastosuje, bo ma wiele zalet, w tym najważniejszą - jest prosta i przejrzysta. Pokażę jednak, że da się inaczej.
Drzewa wyrażeń
Drzewa wyrażeń to stara koncepcja, która została zaadaptowana na potrzeby LINQ. Drzewa stosowane były i są stosowane nadal w parserach i kompilatorach, bo stanowią naturalną reprezentację zależności, a przekształcenia drzewa, w ramach ściśle określonych zasad, pozwalają na pewne optymalizacje. Aby zrozumieć o czym mówię, posłużę się przykładowym wyrażeniem postaci wynik = 1 + x + 3. Jak on wygląda w postaci drzewa?
+
/ \
+ 3
/ \
1 x
Wiemy, że dodawanie jest przemienne, zatem możemy przestawiać dowolne gałęzie w ramach jednej operacji dodawania (i odejmowania jako operacji dodawania liczby ze znakiem ujemnym). Zamieńmy zatem x z 3:
+
/ \
+ x
/ \
1 3
W praktyce można zastosować rozwiązanie polegające na takim przekształcaniu drzewa, aby stałe lądowały na samym dole grupy podobnych, w sensie priorytetów, operacji. Co teraz robi kompilator? Widzi operację, którą może wykonać już na etapie kompilacji! Tworzy zatem równoważną matematyczie, ale wydajniejszą obliczeniowo instrukcję 4 + x. Także LINQ dla SQL takie operacje wykonuje i może, po przeanalizowaniu całego drzewa, wygenerować wydajniejsze zapytanie niż mogłoby się nam wydawać analizując kod. LINQ dla SQL korzysta z drzew wyrażeń. Przyjrzyjmy się przykładowej implementacji naszej metody do pobierania ikon wykorzystującej takie drzewa:
{
using (DBContext context = new DBContext())
{
//Base query
var query = from o in context.Icons
where o.Id == id
select o;
ConstructorInfo ci = typeof(Icon).GetConstructor(new Type[] { typeof(int), typeof(System.Data.Linq.Binary) });
var parameter = Expression.Parameter(query.ElementType, "p");
//Extract ID attribute - p.Id
var idAttribute = Expression.Property(parameter, "Id");
//Extract dynamic column attribute - p.[column]
var iconAttribute = Expression.Property(parameter, column);
//Call constructor - new Icon(p.Id, p.[column]
var ctor = Expression.New(ci, new Expression[] { idAttribute, iconAttribute });
//Prepare lambda expression - p => new Icon(p.Id, p.[column])
var lambda = Expression.Lambda(ctor, parameter);
//Call Select - [query.Expression].Select(p => new Icon(p.Id, p.[column]))
var method = Expression.Call(typeof(Queryable), "Select", new Type[] { query.ElementType, typeof(Icon) }, query.Expression, lambda);
//Execute whole statement
return query.Provider.CreateQuery<Icon>(method).FirstOrDefault();
}
}
Zauważmy, że punktem wyjścia jest bardzo proste wyrażenie, które teoretycznie pobiera dane z całej tabeli. Czy tak będzie, zaraz się przekonamy. Kolejne instrukcje budują drzewo wyrażeń, instrukcja po instrukcji. Mamy zatem wydobycie wartości właściwości z obiektu, wywołanie konstruktora z tymi właściwościami, stworzenie wyrażenia lambda i w końcu wywołanie instrukcji SELECT. Taki SELECT z bazowej instrukcji SELECT. Na koniec całe wyrażenie przekazujemy do klasy odpowiedzialnej za przetworzenie drzewa na instrukcję SQL. Zastanówmy się, co się dzieje z takim oto, podobnym zapytaniem w bazie danych:
(
SELECT * FROM Icons
) SubQuery
Czy SQL Server będzie przetwarzał dwa zapytania? Otóż nie. Zastosuje wspomniane wcześniej optymalizacje. Czy LINQ dla SQL będzie najpierw pobierał cały obiekt, a potem wydobywał z niego poszczególne pola? Znów odpowiedź brzmi: nie! Co jest przesyłane do bazy danych? Przyjrzyjmy się wygenerowanemu przez LINQ zapytaniu:
FROM [dbo].[Icons] AS [t0]
WHERE [t0].[Id] = @p0',N'@p0 int',@p0=1
Instrukcja TOP 1 pochodzi z metody FirstOrDefault() i instruuje Serwer SQL, że chcemy maksymalnie jeden rekord. Po liście kolumn w sekcji SELECT wyraźnie widać, że pobierane są tylko dwie kolumny, tak jak sobie założyliśmy.
Drzewa wyrażeń i SQL Injection
Czy metoda z drzewem wyrażeń jest bezpieczna na ataki SQL Injection? Trzeba wiedzieć, że LINQ w środku korzysta z mechanizmów .NET. Skoro podajemy mu konstruktor w postaci typu System.Reflection.ConstructorInfo, podajemy mu konkretne atrybuty, konkretne nazwy metod, które musi odnaleźć, to wydaje się, że możemy się czuć bezpieczni. Czy można wpisać nieprawidłową nazwę kolumny? Można się przekonać, że nawet drobna literówka spowoduje powstanie błędu. Wpisanie zamiast nazwy kolumny wartości 1 Dummy; DELETE FROM XXX;-- doprowadzi nas do wyjątku w formacie Nie zdefiniowano właściwości wystąpienia {0} dla typu {1}. Mamy zatem do czynienia z pełną, silną kontrolą typów!
Drzewa wyrażeń mają mnóstwo zastosowań, a ich możliwości idą daleko ponad to, co zostało zaprezentowane w tm skromnym wpisie.
Pomimo dużej elestyczności pokazanego przykładu, nie zachęcam do pisania funkcji przyjmującej typ string jako parametr. Zawsze lepiej użyć jakiegoś typu wyliczeniowego, który zawęzi nam zbiór możliwych wartości. Zbiór kolumn jest, co jasne, ograniczony. Jeżeli typy wyliczeniowe nie przekładają się wprost na nazwy kolumn, zawsze można użyć atrybutów. Nie zmuszajmy użytkowników metod do pamiętania nazw kolumn w bazie, jeżeli da się to zrobić inaczej. Rozbudowę metody zostawiam jako ćwiczenie do samodzielnego wykonania.
Brak komentarzy - bądź pierwszy