Zapytania SQL

  • Artykuł

Program Entity Framework Core umożliwia tworzenie list rozwijanych do zapytań SQL podczas pracy z relacyjną bazą danych. Zapytania SQL są przydatne, jeśli żądane zapytanie nie może być wyrażone przy użyciu LINQ lub jeśli zapytanie LINQ powoduje, że program EF generuje nieefektywny kod SQL. Zapytania SQL mogą zwracać zwykłe typy jednostek lub typy jednostek bez klucza, które są częścią modelu.

Napiwek

Przykład z tego artykułu można zobaczyć w witrynie GitHub.

Podstawowe zapytania SQL

Możesz użyć FromSql polecenia , aby rozpocząć zapytanie LINQ na podstawie zapytania SQL:

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.Blogs")
    .ToList();

Uwaga

FromSql wprowadzono w programie EF Core 7.0. W przypadku korzystania ze starszych wersji użyj zamiast tego.FromSqlInterpolated

Zapytania SQL mogą służyć do wykonywania procedury składowanej, która zwraca dane jednostki:

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogs")
    .ToList();

Uwaga

FromSql można używać tylko bezpośrednio w obiekcie DbSet. Nie można składać się z dowolnego zapytania LINQ.

Przekazywanie parametrów

Ostrzeżenie

Zwróć szczególną uwagę na parametryzację podczas korzystania z zapytań SQL

W przypadku wprowadzania wartości dostarczonych przez użytkownika do zapytania SQL należy zachować ostrożność, aby uniknąć ataków polegających na wstrzyknięciu kodu SQL. Iniekcja SQL występuje, gdy program integruje wartość ciągu dostarczonego przez użytkownika z zapytaniem SQL, a wartość podana przez użytkownika jest spreparowana w celu zakończenia ciągu i wykonania innej złośliwej operacji SQL. Aby dowiedzieć się więcej o wstrzyknięciu kodu SQL, zobacz tę stronę.

Metody FromSql i FromSqlInterpolated są bezpieczne przed wstrzyknięciem kodu SQL i zawsze integrują dane parametrów jako oddzielny parametr SQL. FromSqlRaw Jednak metoda może być podatna na ataki polegających na wstrzyknięciu kodu SQL, jeśli jest ona niewłaściwie używana. Aby uzyskać więcej szczegółów zobacz poniższą sekcję.

Poniższy przykład przekazuje pojedynczy parametr do procedury składowanej przez dołączenie symbolu zastępczego parametru w ciągu zapytania SQL i podanie dodatkowego argumentu:

var user = "johndoe";

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToList();

Chociaż ta składnia może wyglądać jak zwykła interpolacja ciągów języka C#, podana wartość jest owinięta w obiekcie DbParameter i wprowadzona nazwa wygenerowanego parametru{0}, w którym określono symbol zastępczy. FromSql Zapewnia to bezpieczeństwo przed atakami polegającymi na wstrzyknięciu kodu SQL i wysyła wartość wydajnie i poprawnie do bazy danych.

Podczas wykonywania procedur składowanych przydatne może być użycie nazwanych parametrów w ciągu zapytania SQL, zwłaszcza gdy procedura składowana ma opcjonalne parametry:

var user = new SqlParameter("user", "johndoe");

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser @filterByUser={user}")
    .ToList();

Jeśli potrzebujesz większej kontroli nad wysyłanym parametrem bazy danych, możesz również utworzyć obiekt DbParameter i podać go jako wartość parametru. Dzięki temu można ustawić dokładny typ bazy danych parametru lub aspekty, takie jak jego rozmiar, precyzja lub długość:

var user = new SqlParameter("user", "johndoe");

var blogs = context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToList();

Uwaga

Przekazane parametry muszą dokładnie odpowiadać definicji procedury składowanej. Zwróć szczególną uwagę na kolejność parametrów, dbając o to, aby nie przegapić lub zagubić żadnego z nich - lub rozważyć użycie notacji nazwanych parametrów. Upewnij się również, że typy parametrów odpowiadają i że ich aspekty (rozmiar, precyzja, skala) są ustawione zgodnie z potrzebami.

Dynamiczny język SQL i parametry

FromSql a jego parametryzacja powinna być używana wszędzie tam, gdzie to możliwe. Istnieją jednak pewne scenariusze, w których język SQL musi być dynamicznie fragmentowany, a nie można używać parametrów bazy danych. Załóżmy na przykład, że zmienna języka C# przechowuje nazwę właściwości, która ma być filtrowana. Może być kuszące użycie zapytania SQL, takiego jak następujące:

var propertyName = "User";
var propertyValue = "johndoe";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM [Blogs] WHERE {propertyName} = {propertyValue}")
    .ToList();

Ten kod nie działa, ponieważ bazy danych nie zezwalają na parametryzowanie nazw kolumn (ani żadnej innej części schematu).

Najpierw należy wziąć pod uwagę implikacje dynamicznego konstruowania zapytania — za pośrednictwem języka SQL lub w inny sposób. Zaakceptowanie nazwy kolumny od użytkownika może pozwolić im wybrać kolumnę, która nie jest indeksowana, dzięki czemu zapytanie działa bardzo wolno i przeciąża bazę danych; lub może zezwolić im na wybranie kolumny zawierającej dane, których nie chcesz ujawniać. Z wyjątkiem naprawdę dynamicznych scenariuszy zwykle lepiej jest mieć dwa zapytania dla dwóch nazw kolumn, zamiast używać parametryzacji do zwijania ich do pojedynczego zapytania.

Jeśli zdecydujesz, że chcesz dynamicznie skonstruować bazę danych SQL, musisz użyć elementu FromSqlRaw, który umożliwia interpolowanie danych zmiennych bezpośrednio do ciągu SQL, zamiast używać parametru bazy danych:

var columnName = "Url";
var columnValue = new SqlParameter("columnValue", "http://SomeURL");

var blogs = context.Blogs
    .FromSqlRaw($"SELECT * FROM [Blogs] WHERE {columnName} = @columnValue", columnValue)
    .ToList();

W powyższym kodzie nazwa kolumny jest wstawiana bezpośrednio do języka SQL przy użyciu interpolacji ciągów języka C#. Twoim obowiązkiem jest zapewnienie, że ta wartość ciągu jest bezpieczna, czyszcząc ją, jeśli pochodzi z niebezpiecznego źródła; Oznacza to wykrywanie znaków specjalnych, takich jak średniki, komentarze i inne konstrukcje SQL, i ucieczka ich prawidłowo lub odrzucanie takich danych wejściowych.

Z drugiej strony wartość kolumny jest wysyłana za pośrednictwem DbParameterelementu i dlatego jest bezpieczna w obliczu wstrzyknięcia kodu SQL.

Ostrzeżenie

Należy zachować ostrożność podczas korzystania z elementu FromSqlRawi zawsze upewnij się, że wartości pochodzą z bezpiecznego źródła lub są prawidłowo oczyszczone. Ataki polegających na wstrzyknięciu kodu SQL mogą mieć katastrofalne konsekwencje dla aplikacji.

Komponowanie z LINQ

Możesz utworzyć na początku początkowego zapytania SQL przy użyciu operatorów LINQ; Program EF Core traktuje bazę danych SQL jako podzapytywanie i komponuje je w bazie danych. W poniższym przykładzie użyto zapytania SQL wybranego z funkcji Table-Valued (TVF). Następnie komponuje się na nim przy użyciu LINQ do filtrowania i sortowania.

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Where(b => b.Rating > 3)
    .OrderByDescending(b => b.Rating)
    .ToList();

Powyższe zapytanie generuje następujący kod SQL:

SELECT [b].[BlogId], [b].[OwnerId], [b].[Rating], [b].[Url]
FROM (
    SELECT * FROM dbo.SearchBlogs(@p0)
) AS [b]
WHERE [b].[Rating] > 3
ORDER BY [b].[Rating] DESC

Operator Include może służyć do ładowania powiązanych danych, podobnie jak w przypadku dowolnego innego zapytania LINQ:

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .Include(b => b.Posts)
    .ToList();

Komponowanie z LINQ wymaga, aby zapytanie SQL było komponowalne, ponieważ program EF Core traktuje podany kod SQL jako podzapytywanie. Komponowalne zapytania SQL zwykle zaczynają się od słowa kluczowego SELECT i nie mogą zawierać funkcji SQL, które nie są prawidłowe w podzapytaniu, takich jak:

  • Średnik końcowy
  • W programie SQL Server końcowa wskazówka na poziomie zapytania (na przykład OPTION (HASH JOIN))
  • W programie SQL Server klauzulaORDER BY, która nie jest używana z klauzulą OFFSET 0 OR TOP 100 PERCENT w klauzuli SELECT

Program SQL Server nie zezwala na tworzenie wywołań procedury składowanej, dlatego każda próba zastosowania dodatkowych operatorów zapytań do takiego wywołania spowoduje nieprawidłowy kod SQL. Użyj AsEnumerable lub AsAsyncEnumerable bezpośrednio po FromSql lub FromSqlRaw , aby upewnić się, że program EF Core nie próbuje utworzyć procedury składowanej.

Śledzenie zmian

Zapytania używające FromSql lub FromSqlRaw zgodne z tymi samymi regułami śledzenia zmian co dowolne inne zapytanie LINQ w programie EF Core. Jeśli na przykład typy jednostek projektów zapytań, wyniki są domyślnie śledzone.

W poniższym przykładzie użyto zapytania SQL wybranego z funkcji Table-Valued (TVF), a następnie wyłącza śledzenie zmian za pomocą wywołania metody AsNoTracking:

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM dbo.SearchBlogs({searchTerm})")
    .AsNoTracking()
    .ToList();

Wykonywanie zapytań względem typów skalarnych (innych niż jednostka)

Uwaga

Ta funkcja została wprowadzona w programie EF Core 7.0.

Chociaż FromSql jest to przydatne w przypadku wykonywania zapytań dotyczących jednostek zdefiniowanych w modelu, SqlQuery umożliwia łatwe wykonywanie zapytań dotyczących typów skalarnych, innych niż jednostki za pośrednictwem języka SQL, bez konieczności tworzenia list rozwijanych do interfejsów API dostępu do danych niższego poziomu. Na przykład następujące zapytanie pobiera wszystkie identyfikatory z Blogs tabeli:

var ids = context.Database
    .SqlQuery<int>($"SELECT [BlogId] FROM [Blogs]")
    .ToList();

Operatory LINQ można również tworzyć za pośrednictwem zapytania SQL. Jednak ponieważ usługa SQL staje się podzapytaniem, do którego kolumna wyjściowa musi być odwoływała się do programu SQL EF, musisz nazwać kolumnę Valuewyjściową . Na przykład następujące zapytanie zwraca identyfikatory, które są powyżej średniej identyfikatora:

var overAverageIds = context.Database
    .SqlQuery<int>($"SELECT [BlogId] AS [Value] FROM [Blogs]")
    .Where(id => id > context.Blogs.Average(b => b.BlogId))
    .ToList();

FromSql może być używany z dowolnym typem skalarny obsługiwanym przez dostawcę bazy danych. Jeśli chcesz użyć typu, który nie jest obsługiwany przez dostawcę bazy danych, możesz użyć konfiguracji przed konwencją, aby zdefiniować dla niego konwersję wartości.

SqlQueryRaw umożliwia dynamiczną konstrukcję zapytań SQL, podobnie jak FromSqlRaw w przypadku typów jednostek.

Wykonywanie niezwiązanych z wykonywaniem zapytań SQL

W niektórych scenariuszach może być konieczne wykonanie bazy danych SQL, która nie zwraca żadnych danych, zazwyczaj w przypadku modyfikowania danych w bazie danych lub wywoływania procedury składowanej, która nie zwraca żadnych zestawów wyników. Można to zrobić za pomocą polecenia ExecuteSql:

using (var context = new BloggingContext())
{
    var rowsModified = context.Database.ExecuteSql($"UPDATE [Blogs] SET [Url] = NULL");
}

Spowoduje to wykonanie podanego kodu SQL i zwrócenie liczby zmodyfikowanych wierszy. ExecuteSql chroni przed wstrzyknięciem kodu SQL przy użyciu bezpiecznej parametryzacji, podobnie jak FromSql, i ExecuteSqlRaw umożliwia dynamiczną konstrukcję zapytań SQL, podobnie jak FromSqlRaw w przypadku zapytań.

Uwaga

Przed programem EF Core 7.0 czasami konieczne było użycie ExecuteSql interfejsów API do przeprowadzenia "zbiorczej aktualizacji" w bazie danych, jak powyżej. Jest to znacznie bardziej wydajne niż wykonywanie zapytań dotyczących wszystkich pasujących wierszy, a następnie używanie ich SaveChanges do modyfikowania. Program EF Core 7.0 wprowadził funkcję ExecuteUpdate i ExecuteDelete, co umożliwiło wyrażenie wydajnych operacji aktualizacji zbiorczych za pośrednictwem LINQ. Zaleca się używanie tych interfejsów API zawsze, gdy jest to możliwe, zamiast ExecuteSql.

Ograniczenia

Podczas zwracania typów jednostek z zapytań SQL należy pamiętać o kilku ograniczeniach:

  • Zapytanie SQL musi zwracać dane dla wszystkich właściwości typu jednostki.
  • Nazwy kolumn w zestawie wyników muszą być zgodne z nazwami kolumn, do których są mapowane właściwości. Należy pamiętać, że to zachowanie różni się od ef6; Program EF6 zignorował mapowanie właściwości na kolumnę dla zapytań SQL, a nazwy kolumn zestawu wyników musiały być zgodne z tymi nazwami właściwości.
  • Zapytanie SQL nie może zawierać powiązanych danych. Jednak w wielu przypadkach można utworzyć na podstawie zapytania, używając Include operatora do zwracania powiązanych danych (zobacz Dołączanie powiązanych danych).