Pierwotne zapytania SQLRaw SQL Queries

Entity Framework Core pozwala na rozwijanie do nieprzetworzonych zapytań SQL podczas pracy z relacyjną bazą danych.Entity Framework Core allows you to drop down to raw SQL queries when working with a relational database. Surowe zapytania SQL są przydatne, jeśli nie można wyrazić tego zapytania przy użyciu LINQ.Raw SQL queries are useful if the query you want can't be expressed using LINQ. Nieprzetworzone zapytania SQL są używane również wtedy, gdy użycie zapytania LINQ skutkuje nieefektywnym zapytaniem SQL.Raw SQL queries are also used if using a LINQ query is resulting in an inefficient SQL query. Surowe zapytania SQL mogą zwracać zwykłe typy jednostek lub mniejsze typy jednostek , które są częścią modelu.Raw SQL queries can return regular entity types or keyless entity types that are part of your model.

Porada

Przykład tego artykułu można wyświetlić w witrynie GitHub.You can view this article's sample on GitHub.

Podstawowe nieprzetworzone zapytania SQLBasic raw SQL queries

Możesz użyć FromSqlRaw metody rozszerzenia, aby rozpocząć zapytanie LINQ na podstawie pierwotnego zapytania SQL.You can use the FromSqlRaw extension method to begin a LINQ query based on a raw SQL query. FromSqlRaw może być używany tylko w przypadku katalogów głównych zapytania, które są bezpośrednio w DbSet<> .FromSqlRaw can only be used on query roots, that is directly on the DbSet<>.

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

W celu wykonania procedury składowanej można użyć nieprzetworzonych zapytań SQL.Raw SQL queries can be used to execute a stored procedure.

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

Przekazywanie parametrówPassing parameters

Ostrzeżenie

Zawsze używaj parametryzacja dla nieprzetworzonych zapytań SQLAlways use parameterization for raw SQL queries

Wprowadzając wszelkie wartości podane przez użytkownika w nieprzetworzonej kwerendzie SQL, należy zachować ostrożność, aby uniknąć ataków iniekcji SQL.When introducing any user-provided values into a raw SQL query, care must be taken to avoid SQL injection attacks. 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.In addition to validating that such values don't contain invalid characters, always use parameterization which sends the values separate from the SQL text.

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 .In particular, never pass a concatenated or interpolated string ($"") with non-validated user-provided values into FromSqlRaw or 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.The FromSqlInterpolated and ExecuteSqlInterpolated methods allow using string interpolation syntax in a way that protects against SQL injection attacks.

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.The following example passes a single parameter to a stored procedure by including a parameter placeholder in the SQL query string and providing an additional 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.While this syntax may look like String.Format syntax, the supplied value is wrapped in a DbParameter and the generated parameter name inserted where the {0} placeholder was specified.

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.FromSqlInterpolated is similar to FromSqlRaw but allows you to use string interpolation syntax. Podobnie jak FromSqlRaw , FromSqlInterpolated można używać tylko w przypadku katalogów głównych zapytań.Just like FromSqlRaw, FromSqlInterpolated can only be used on query roots. Podobnie jak w poprzednim przykładzie, wartość jest konwertowana na DbParameter i nie jest narażona na iniekcję kodu SQL.As with the previous example, the value is converted to a DbParameter and isn't vulnerable to SQL injection.

Uwaga

Przed wersjami 3,0 FromSqlRaw i FromSqlInterpolated były dwa przeciążenia o nazwie FromSql .Prior to version 3.0, FromSqlRaw and FromSqlInterpolated were two overloads named FromSql. Aby uzyskać więcej informacji, zobacz sekcję poprzednie wersje.For more information, see the previous versions section.

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.You can also construct a DbParameter and supply it as a parameter value. Ponieważ jest używany zwykły symbol zastępczy parametru SQL, a nie symbol zastępczy ciągu, FromSqlRaw można bezpiecznie użyć:Since a regular SQL parameter placeholder is used, rather than a string placeholder, FromSqlRaw can be safely used:

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:FromSqlRaw allows you to use named parameters in the SQL query string, which is useful when a stored procedure has optional parameters:

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.Parameter Ordering Entity Framework Core passes parameters based on the order of the SqlParameter[] array. Podczas przekazywania wielu SqlParameter s kolejność w ciągu SQL musi być zgodna z kolejnością parametrów w definicji procedury składowanej.When passing multiple SqlParameters, the ordering in the SQL string must match the order of the parameters in the stored procedure's definition. Niewykonanie tej czynności może spowodować wyjątki konwersji typu i/lub nieoczekiwane zachowanie podczas wykonywania procedury.Failure to do this may result in type conversion exceptions and/or unexpected behavior when the procedure is executed.

Tworzenie za pomocą LINQComposing with LINQ

Możesz złożyć na początku wstępnego nieprzetworzonego zapytania SQL przy użyciu operatorów LINQ.You can compose on top of the initial raw SQL query using LINQ operators. EF Core traktuje ją jako podzapytanie i redaguje ją w bazie danych.EF Core will treat it as subquery and compose over it in the database. Poniższy przykład używa nieprzetworzonego zapytania SQL, które wybiera z funkcji Table-Valued (TVF).The following example uses a raw SQL query that selects from a Table-Valued Function (TVF). A następnie redaguje przy użyciu LINQ, aby przeprowadzić filtrowanie i sortowanie.And then composes on it using LINQ to do filtering and sorting.

var searchTerm = ".NET";

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:Above query generates following 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:The Include method can be used to include related data, just like with any other LINQ query:

var searchTerm = ".NET";

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.Composing with LINQ requires your raw SQL query to be composable since EF Core will treat the supplied SQL as a subquery. Zapytania SQL, które mogą być składane na początku ze SELECT słowem kluczowym.SQL queries that can be composed on begin with the SELECT keyword. Dodatkowo przekazanie danych SQL nie powinno zawierać żadnych znaków ani opcji, które nie są prawidłowe w podzapytaniu, na przykład:Further, SQL passed shouldn't contain any characters or options that aren't valid on a subquery, such as:

  • Końcowy średnikA trailing semicolon
  • Na SQL Server, końcowa Wskazówka na poziomie zapytania (na przykład OPTION (HASH JOIN) )On SQL Server, a trailing query-level hint (for example, OPTION (HASH JOIN))
  • Na SQL Server, ORDER BY klauzula, która nie jest używana z OFFSET 0 lub TOP 100 PERCENT w SELECT klauzuliOn SQL Server, an ORDER BY clause that isn't used with OFFSET 0 OR TOP 100 PERCENT in the SELECT clause

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.SQL Server doesn't allow composing over stored procedure calls, so any attempt to apply additional query operators to such a call will result in invalid 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ą.Use AsEnumerable or AsAsyncEnumerable method right after FromSqlRaw or FromSqlInterpolated methods to make sure that EF Core doesn't try to compose over a stored procedure.

Śledzenie zmianChange Tracking

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.Queries that use the FromSqlRaw or FromSqlInterpolated methods follow the exact same change tracking rules as any other LINQ query in EF Core. Na przykład, jeśli typ jednostki projekty zapytań, wyniki będą śledzone domyślnie.For example, if the query projects entity types, the results will be tracked by default.

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 :The following example uses a raw SQL query that selects from a Table-Valued Function (TVF), then disables change tracking with the call to AsNoTracking:

var searchTerm = ".NET";

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

OgraniczeniaLimitations

Istnieje kilka ograniczeń, które należy wziąć pod uwagę podczas korzystania z nieprzetworzonych zapytań SQL:There are a few limitations to be aware of when using raw SQL queries:

  • Zapytanie SQL musi zwracać dane dla wszystkich właściwości typu jednostki.The SQL query must return data for all properties of the entity type.
  • Nazwy kolumn w zestawie wyników muszą być zgodne z nazwami kolumn, do których są mapowane właściwości.The column names in the result set must match the column names that properties are mapped to. Należy zauważyć, że to zachowanie różni się od EF6.Note this behavior is different from 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.EF6 ignored property to column mapping for raw SQL queries and result set column names had to match the property names.
  • Zapytanie SQL nie może zawierać powiązanych danych.The SQL query can't contain related data. 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).However, in many cases you can compose on top of the query using the Include operator to return related data (see Including related data).

Poprzednie wersjePrevious versions

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 .EF Core version 2.2 and earlier had two overloads of method named FromSql, which behaved in the same way as the newer FromSqlRaw and FromSqlInterpolated. Można łatwo przypadkowo wywołać metodę nieprzetworzonego ciągu, gdy zamiarem było wywołanie interpolowanej metody String i odwrotnie.It was easy to accidentally call the raw string method when the intent was to call the interpolated string method, and the other way around. Przypadkowe wywołanie nieprawidłowego przeciążenia może spowodować, że kwerendy nie są sparametryzowane, gdy powinny one być.Calling wrong overload accidentally could result in queries not being parameterized when they should have been.