Pierwotne zapytania SQL

Entity Framework Core pozwala na rozwijanie do nieprzetworzonych zapytań SQL podczas pracy z relacyjną bazą danych. Surowe zapytania SQL są przydatne, jeśli nie można wyrazić tego zapytania przy użyciu LINQ. Nieprzetworzone zapytania SQL są używane również wtedy, gdy użycie zapytania LINQ skutkuje nieefektywnym zapytaniem SQL. Surowe zapytania SQL mogą zwracać zwykłe typy jednostek lub mniejsze typy jednostek , które są częścią modelu.

Porada

Przykład tego artykułu można wyświetlić w witrynie GitHub.

Podstawowe nieprzetworzone zapytania SQL

Możesz użyć FromSqlRaw metody rozszerzenia, aby rozpocząć zapytanie LINQ na podstawie pierwotnego zapytania SQL. FromSqlRaw może być używany tylko w przypadku katalogów głównych zapytania, które są bezpośrednio w DbSet<> .

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

W celu wykonania procedury składowanej można użyć nieprzetworzonych zapytań SQL.

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

Przekazywanie parametrów

Ostrzeżenie

Zawsze używaj parametryzacja dla nieprzetworzonych zapytań SQL

Wprowadzając wszelkie wartości podane przez użytkownika w nieprzetworzonej kwerendzie SQL, należy zachować ostrożność, aby uniknąć ataków iniekcji SQL. Oprócz sprawdzania, czy takie wartości nie zawierają nieprawidłowych znaków, należy zawsze używać parametryzacja, które wysyła wartości odrębnie od tekstu SQL.

W szczególności nigdy nie przekazuj ciągu połączonego lub interpolowanego ( $"" ) z niezweryfikowanymi wartościami dostarczonymi przez użytkownika w FromSqlRaw lub ExecuteSqlRaw . FromSqlInterpolatedMetody i ExecuteSqlInterpolated umożliwiają używanie składni interpolacji ciągów w sposób chroniący przed atakami polegającymi na iniekcji SQL.

Poniższy przykład przekazuje pojedynczy parametr do procedury składowanej, dołączając symbol zastępczy parametru w ciągu zapytania SQL i dostarczając dodatkowy argument. Chociaż ta składnia może wyglądać podobnie do String.Format składni, podana wartość jest opakowana DbParameter i wygenerowaną nazwą parametru wstawioną, gdzie {0} symbol zastępczy został określony.

var user = "johndoe";

var blogs = context.Blogs
    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser {0}", user)
    .ToList();

FromSqlInterpolated jest podobna do FromSqlRaw , ale umożliwia użycie składni interpolacji ciągów. Podobnie jak FromSqlRaw , FromSqlInterpolated można używać tylko w przypadku katalogów głównych zapytań. Podobnie jak w poprzednim przykładzie, wartość jest konwertowana na DbParameter i nie jest narażona na iniekcję kodu SQL.

Uwaga

Przed wersjami 3,0 FromSqlRaw i FromSqlInterpolated były dwa przeciążenia o nazwie FromSql . Aby uzyskać więcej informacji, zobacz sekcję poprzednie wersje.

var user = "johndoe";

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

Możesz również skonstruować DbParameter i podać go jako wartość parametru. Ponieważ jest używany zwykły symbol zastępczy parametru SQL, a nie symbol zastępczy ciągu, FromSqlRaw można bezpiecznie użyć:

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

var blogs = context.Blogs
    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser @user", user)
    .ToList();

FromSqlRaw pozwala używać parametrów nazwanych w ciągu zapytania SQL, co jest przydatne, gdy procedura składowana ma parametry opcjonalne:

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

var blogs = context.Blogs
    .FromSqlRaw("EXECUTE dbo.GetMostPopularBlogsForUser @filterByUser=@user", user)
    .ToList();

Uwaga

Porządkowanie parametrów Entity Framework Core przekazuje parametry na podstawie kolejności SqlParameter[] tablicy. Podczas przekazywania wielu SqlParameter s kolejność w ciągu SQL musi być zgodna z kolejnością parametrów w definicji procedury składowanej. Niewykonanie tej czynności może spowodować wyjątki konwersji typu i/lub nieoczekiwane zachowanie podczas wykonywania procedury.

Tworzenie za pomocą LINQ

Możesz złożyć na początku wstępnego nieprzetworzonego zapytania SQL przy użyciu operatorów LINQ. EF Core traktuje ją jako podzapytanie i redaguje ją w bazie danych. Poniższy przykład używa nieprzetworzonego zapytania SQL, które wybiera z funkcji Table-Valued (TVF). A następnie redaguje przy użyciu LINQ, aby przeprowadzić filtrowanie i sortowanie.

var searchTerm = "Lorem ipsum";

var blogs = context.Blogs
    .FromSqlInterpolated($"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

IncludeMetoda może służyć do uwzględnienia powiązanych danych, podobnie jak w przypadku innych zapytań LINQ:

var searchTerm = "Lorem ipsum";

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

Redagowanie przy użyciu LINQ wymaga, aby pierwotne zapytanie SQL było możliwe do utworzenia, ponieważ EF Core będzie traktować dane SQL jako podzapytanie. Zapytania SQL, które mogą być składane na początku ze SELECT słowem kluczowym. Dodatkowo przekazanie danych SQL nie powinno zawierać żadnych znaków ani opcji, które nie są prawidłowe w podzapytaniu, na przykład:

  • Końcowy średnik
  • Na SQL Server, końcowa Wskazówka na poziomie zapytania (na przykład OPTION (HASH JOIN) )
  • Na SQL Server, ORDER BY klauzula, która nie jest używana z OFFSET 0 lub TOP 100 PERCENT w SELECT klauzuli

SQL Server nie zezwala na tworzenie wywołań procedur składowanych, więc każda próba zastosowania dodatkowych operatorów zapytań do takiego wywołania spowoduje nieprawidłowe użycie języka SQL. Użyj AsEnumerable lub AsAsyncEnumerable metody bezpośrednio po FromSqlRaw lub FromSqlInterpolated metod, aby upewnić się, że EF Core nie próbuje utworzyć przez procedurę składowaną.

Śledzenie zmian

Zapytania, które używają FromSqlRaw FromSqlInterpolated metod lub stosują dokładnie te same reguły śledzenia zmian, jak inne zapytania LINQ w EF Core. Na przykład, jeśli typ jednostki projekty zapytań, wyniki będą śledzone domyślnie.

Poniższy przykład używa nieprzetworzonego zapytania SQL, które wybiera z funkcji Table-Valued (TVF), a następnie wyłącza śledzenie zmian z wywołaniem AsNoTracking :

var searchTerm = "Lorem ipsum";

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

Ograniczenia

Istnieje kilka ograniczeń, które należy wziąć pod uwagę podczas korzystania z nieprzetworzonych zapytań SQL:

  • 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 zauważyć, że to zachowanie różni się od EF6. EF6 zignorował właściwość do mapowania kolumn dla nieprzetworzonych zapytań SQL i nazwy kolumn zestawu wyników musiały pasować do nazw właściwości.
  • Zapytanie SQL nie może zawierać powiązanych danych. Jednak w wielu przypadkach można utworzyć na górze zapytania przy użyciu Include operatora, aby zwrócić powiązane dane (zobacz m.in. powiązane dane).

Poprzednie wersje

EF Core w wersji 2,2 i wcześniejszej miały dwa przeciążenia metody o nazwie FromSql , które zadziałały w taki sam sposób jak nowsze FromSqlRaw i FromSqlInterpolated . Można łatwo przypadkowo wywołać metodę nieprzetworzonego ciągu, gdy zamiarem było wywołanie interpolowanej metody String i odwrotnie. Przypadkowe wywołanie nieprawidłowego przeciążenia może spowodować, że kwerendy nie są sparametryzowane, gdy powinny one być.