Pierwotne zapytania SQLRaw SQL Queries

Entity Framework Core umożliwia lista rozwijana umożliwiająca pierwotne zapytania SQL podczas pracy z usługą relacyjnej bazy danych.Entity Framework Core allows you to drop down to raw SQL queries when working with a relational database. Może to być przydatne, jeśli zapytanie, które należy wykonać, nie można wyrazić za pomocą LINQ lub przy użyciu zapytania LINQ wynikiem jest nieefektywne SQL wysyłane do bazy danych.This can be useful if the query you want to perform can't be expressed using LINQ, or if using a LINQ query is resulting in inefficient SQL being sent to the database. Pierwotne zapytania SQL może zwrócić typów jednostek, lub, począwszy od programu EF Core 2.1, typy zapytań będących częścią modelu.Raw SQL queries can return entity types or, starting with EF Core 2.1, query types that are part of your model.

Porada

Przykład użyty w tym artykule można zobaczyć w witrynie GitHub.You can view this article's sample on GitHub.

OgraniczeniaLimitations

Istnieją pewne ograniczenia, które należy zwrócić uwagę podczas korzystania z pierwotne zapytania SQL:There are a few limitations to be aware of when using raw SQL queries:

  • Zapytanie SQL musi zwracać dane dotyczące wszystkich właściwości typu obiekt lub kwerendę.The SQL query must return data for all properties of the entity or query type.

  • Nazwy kolumn w zestawie wyników muszą być zgodne nazwy kolumn, które 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 pamiętać, że to różni się od EF6 gdzie mapowania właściwości/kolumn została zignorowana dla pierwotne zapytania SQL i nazw musiały być zgodne z nazwami właściwości kolumny zestawu wyników.Note this is different from EF6 where property/column mapping was ignored for raw SQL queries and result set column names had to match the property names.

  • Zapytania SQL nie może zawierać powiązane dane.The SQL query cannot contain related data. Jednak w wielu przypadkach można utworzyć na podstawie zapytania za pomocą Include operator, aby zwrócić dane dotyczące (zobacz powiązanych danych w tym).However, in many cases you can compose on top of the query using the Include operator to return related data (see Including related data).

  • SELECT instrukcje przekazany do tej metody powinno być zazwyczaj konfigurowalna: Jeśli programu EF Core należy ocenić, operatory dodatkowych zapytań na serwerze (na przykład, aby przetłumaczyć operatorów LINQ zastosowane po FromSql), SQL podane są traktowane jak podzapytania.SELECT statements passed to this method should generally be composable: If EF Core needs to evaluate additional query operators on the server (for example, to translate LINQ operators applied after FromSql), the supplied SQL will be treated as a subquery. Oznacza to, SQL przekazywane nie powinna zawierać żadnych znaków lub opcje, które nie są prawidłowe w podzapytaniu, takich jak:This means that the SQL passed should not contain any characters or options that are not valid on a subquery, such as:

    • końcowe średnikamia trailing semicolon
    • W programie SQL Server, końcowe wskazówka poziomie zapytania (na przykład OPTION (HASH JOIN))On SQL Server, a trailing query-level hint (for example, OPTION (HASH JOIN))
    • W programie SQL Server ORDER BY klauzula, która nie jest powiązany z TOP 100 PERCENT w SELECT — klauzulaOn SQL Server, an ORDER BY clause that is not accompanied of TOP 100 PERCENT in the SELECT clause
  • Instrukcje SQL innych niż SELECT są rozpoznawane automatycznie jako innego niż konfigurowalna.SQL statements other than SELECT are recognized automatically as non-composable. W konsekwencji pełnych wyników procedury składowane zawsze są zwracane do klienta i dowolnych operatorów LINQ zastosowane po FromSql jest oceniana w pamięci.As a consequence, the full results of stored procedures are always returned to the client and any LINQ operators applied after FromSql are evaluated in-memory.

Podstawowe pierwotne zapytania SQLBasic raw SQL queries

Możesz użyć FromSql metodę rozszerzenia, aby rozpocząć zapytanie LINQ, na podstawie pierwotne zapytania SQL.You can use the FromSql extension method to begin a LINQ query based on a raw SQL query.

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

Pierwotne zapytania SQL może służyć do wykonywania procedury składowanej.Raw SQL queries can be used to execute a stored procedure.

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

Przekazywanie parametrówPassing parameters

Podobnie jak w przypadku dowolnego interfejsu API, który akceptuje SQL, jest ważne, aby zdefiniować parametry wszystkie dane wejściowe użytkownika mają ochronę przed ataku polegającego na iniekcji SQL.As with any API that accepts SQL, it is important to parameterize any user input to protect against a SQL injection attack. Można zawierają symbole zastępcze parametr ciągu zapytania SQL, a następnie podaj wartości parametrów jako dodatkowe argumenty.You can include parameter placeholders in the SQL query string and then supply parameter values as additional arguments. Możesz podać wartości parametrów zostaną automatycznie przekonwertowane na DbParameter.Any parameter values you supply will automatically be converted to a DbParameter.

Poniższy przykład przekazuje pojedynczy parametr do procedury składowanej.The following example passes a single parameter to a stored procedure. Chociaż może to wyglądać jak String.Format składni, podana wartość jest opakowana w miejsce dodaje parametr i nazwę parametru wygenerowanego {0} określono symbolu zastępczego.While this may look like String.Format syntax, the supplied value is wrapped in a parameter and the generated parameter name inserted where the {0} placeholder was specified.

var user = "johndoe";

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

To jest taka sama zapytania, ale przy użyciu składni interpolacji ciągu, który jest obsługiwany w programie EF Core 2.0 i nowszych:This is the same query but using string interpolation syntax, which is supported in EF Core 2.0 and above:

var user = "johndoe";

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

Można także skonstruować DbParameter i podać go jako wartość parametru.You can also construct a DbParameter and supply it as a parameter value. Dzięki temu można użyć nazwanych parametrów ciągu zapytania SQLThis allows you to use named parameters in the SQL query string

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

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

Tworzenie za pomocą LINQComposing with LINQ

Jeśli zapytanie SQL może być składana na w bazie danych, można utworzyć na podstawie początkowego pierwotne zapytania SQL przy użyciu operatorów LINQ.If the SQL query can be composed on in the database, then you can compose on top of the initial raw SQL query using LINQ operators. Zapytania SQL, które mogą się składać z tego za pomocą SELECT — słowo kluczowe.SQL queries that can be composed on being with the SELECT keyword.

W poniższym przykładzie użyto pierwotne zapytanie SQL, które wybiera z funkcji Table-Valued (TVF), a następnie komponuje się na nim za pomocą LINQ, aby wykonać filtrowanie i sortowanie.The following example uses a raw SQL query that selects from a Table-Valued Function (TVF) and then composes on it using LINQ to perform filtering and sorting.

var searchTerm = ".NET";

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

Tworzenie przy użyciu operatorów LINQ, może służyć do uwzględnienia powiązanych danych w zapytaniu.Composing with LINQ operators can be used to include related data in the query.

var searchTerm = ".NET";

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

Ostrzeżenie

Zawsze używaj parametryzacji pierwotne zapytania SQL: interfejsów API, które akceptują pierwotne SQL ciągów, takich jak FromSql i ExecuteSqlCommand Zezwalaj na wartości, które mają być łatwo przekazywane jako parametry.Always use parameterization for raw SQL queries: APIs that accept a raw SQL string such as FromSql and ExecuteSqlCommand allow values to be easily passed as parameters. Oprócz sprawdzania poprawności danych wejściowych użytkownika, należy zawsze używać parametryzacji dla każdej wartości pierwotne zapytania SQL/polecenia.In addition to validating user input, always use parameterization for any values used in a raw SQL query/command. Jeśli używasz ciągów dynamicznie tworzenie dowolnej części ciągu zapytania, a następnie ponosisz odpowiedzialność za sprawdzanie poprawności wszystkie dane wejściowe, aby zapewnić ochronę przed atakami polegającymi na iniekcji SQL.If you are using string concatenation to dynamically build any part of the query string then you are responsible for validating any input to protect against SQL injection attacks.