Unformatierte SQL-AbfragenRaw SQL Queries

Mit Entity Framework Core können Sie bei der Arbeit mit einer relationalen Datenbank die Struktur unformatierter SQL-Abfragen maximieren.Entity Framework Core allows you to drop down to raw SQL queries when working with a relational database. Unformatierte SQL-Abfragen sind nützlich, wenn die gewünschte Abfrage nicht mithilfe von LINQ ausgedrückt werden kann.Raw SQL queries are useful if the query you want can't be expressed using LINQ. Unformatierte SQL-Abfragen werden auch verwendet, wenn eine LINQ-Abfrage zu einer ineffizienten SQL-Abfrage führt.Raw SQL queries are also used if using a LINQ query is resulting in an inefficient SQL query. Unformatierte SQL-Abfragen können reguläre Entitätstypen oder schlüssellose Entitätstypen zurückgeben, die Teil des Modells sind.Raw SQL queries can return regular entity types or keyless entity types that are part of your model.

Tipp

Das in diesem Artikel verwendete Beispiel finden Sie auf GitHub.You can view this article's sample on GitHub.

Grundlegende unformatierte SQL-AbfragenBasic raw SQL queries

Sie können die FromSqlRaw-Erweiterungsmethode verwenden, um eine LINQ-Abfrage basierend auf einer unformatierten SQL-Abfrage zu starten.You can use the FromSqlRaw extension method to begin a LINQ query based on a raw SQL query. FromSqlRaw kann nur für Abfragestämme verwendet werden, die sich direkt in der DbSet<>-Klasse befinden.FromSqlRaw can only be used on query roots, that is directly on the DbSet<>.

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

Unformatierte SQL-Abfragen können für die Ausführung einer gespeicherten Prozedur verwendet werden.Raw SQL queries can be used to execute a stored procedure.

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

Übergeben von ParameternPassing parameters

Warnung

Für unformatierte SQL-Abfragen immer Parametrisierung verwendenAlways use parameterization for raw SQL queries

Wenn Sie von Benutzern bereitgestellte Werte in eine unformatierte SQL-Abfrage einführen, muss darauf geachtet werden, dass Angriffe durch Einschleusung von SQL-Befehlen vermieden werden.When introducing any user-provided values into a raw SQL query, care must be taken to avoid SQL injection attacks. Überprüfen Sie nicht nur, ob solche Werte keine ungültigen Zeichen enthalten, sondern verwenden Sie auch immer die Parametrisierung, die die Werte getrennt vom SQL-Text sendet.In addition to validating that such values don't contain invalid characters, always use parameterization which sends the values separate from the SQL text.

Übergeben Sie insbesondere niemals eine verkettete oder interpolierte Zeichenfolge ($"") mit nicht validierten und von Benutzern bereitgestellten Werten in FromSqlRaw oder ExecuteSqlRaw.In particular, never pass a concatenated or interpolated string ($"") with non-validated user-provided values into FromSqlRaw or ExecuteSqlRaw. Dank der FromSqlInterpolated- und ExecuteSqlInterpolated-Methode kann die Zeichenfolgeninterpolationssyntax so eingesetzt werden, dass sie vor Angriffen durch Einschleusung von SQL-Befehlen schützt.The FromSqlInterpolated and ExecuteSqlInterpolated methods allow using string interpolation syntax in a way that protects against SQL injection attacks.

Im folgenden Beispiel wird ein einzelner Parameter an eine gespeicherte Prozedur übergeben, indem ein Parameterplatzhalter in die SQL-Abfragezeichenfolge eingeschlossen und ein zusätzliches Argument bereitgestellt wird.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. Diese Syntax sieht zwar aus wie eine String.Format-Syntax, jedoch wird der angegebene Wert in einer DbParameter-Klasse eingeschlossen und der generierte Parametername wird dort eingefügt, wo der {0}-Platzhalter angegeben wurde.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 ähnelt FromSqlRaw, lässt jedoch die Verwendung der Zeichenfolgeninterpolationsyntax zu.FromSqlInterpolated is similar to FromSqlRaw but allows you to use string interpolation syntax. FromSqlInterpolated kann wie FromSqlRaw nur für Abfragestämme verwendet werden.Just like FromSqlRaw, FromSqlInterpolated can only be used on query roots. Wie im vorherigen Beispiel wird der Wert in eine DbParameter-Klasse konvertiert und ist nicht anfällig für die Einschleusung von SQL-Befehlen.As with the previous example, the value is converted to a DbParameter and isn't vulnerable to SQL injection.

Hinweis

Vor Version 3.0 waren FromSqlRaw und FromSqlInterpolated zwei Überladungen namens FromSql.Prior to version 3.0, FromSqlRaw and FromSqlInterpolated were two overloads named FromSql. Weitere Informationen finden Sie im Abschnitt zu vorherigen Versionen.For more information, see the previous versions section.

var user = "johndoe";

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

Sie können auch einen DbParameter erstellen und diesen als Parameterwert bereitstellen.You can also construct a DbParameter and supply it as a parameter value. Da anstelle eines Zeichenfolgeplatzhalters ein regulärer SQL-Parameterplatzhalter verwendet wird, kann FromSqlRaw sicher verwendet werden: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();

Mit FromSqlRaw können Sie benannte Parameter in der SQL-Abfragezeichenfolge verwenden. Dies ist nützlich, wenn eine gespeicherte Prozedur optionale Parameter aufweist: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();

Hinweis

Parameterreihenfolge: Entity Framework Core übergibt Parameter basierend auf der Reihenfolge des SqlParameter[]-Arrays.Parameter Ordering Entity Framework Core passes parameters based on the order of the SqlParameter[] array. Beim Übergeben mehrerer SqlParameter muss die Reihenfolge in der SQL-Zeichenfolge mit der Reihenfolge der Parameter in der Definition der gespeicherten Prozedur übereinstimmen.When passing multiple SqlParameters, the ordering in the SQL string must match the order of the parameters in the stored procedure's definition. Ist dies nicht der Fall, führt dies möglicherweise zu Typkonvertierungsausnahmen und/oder unerwartetem Verhalten beim Ausführen der Prozedur.Failure to do this may result in type conversion exceptions and/or unexpected behavior when the procedure is executed.

Zusammensetzen mit LINQComposing with LINQ

Mithilfe von LINQ-Operatoren können Sie die zunächst unformatierten SQL-Abfragen zusammensetzen.You can compose on top of the initial raw SQL query using LINQ operators. EF Core behandelt diese als Unterabfragen und fügt sie in der Datenbank zusammen.EF Core will treat it as subquery and compose over it in the database. Im folgenden Beispiel wird eine unformatierte SQL-Abfrage verwendet, die aus einer Tabellenwertfunktion auswählt.The following example uses a raw SQL query that selects from a Table-Valued Function (TVF). Anschließend wird sie mithilfe von LINQ zusammengesetzt, um die Filterung und Sortierung durchzuführen.And then composes on it using LINQ to do filtering and sorting.

var searchTerm = "Lorem ipsum";

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

Mit der obigen Abfrage wird folgendes SQL generiert: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

Die Include-Methode kann verwendet werden, um verwandte Daten einzuschließen, genauso wie bei jeder anderen LINQ-Abfrage:The Include method can be used to include related data, just like with any other LINQ query:

var searchTerm = "Lorem ipsum";

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

Für die Zusammensetzung mit LINQ muss Ihre unformatierte SQL-Abfrage zusammensetzbar sein, da EF Core das angegebene SQL als Unterabfrage behandelt.Composing with LINQ requires your raw SQL query to be composable since EF Core will treat the supplied SQL as a subquery. Zusammensetzbare SQL-Abfragen beginnen mit dem Schlüsselwort SELECT.SQL queries that can be composed on begin with the SELECT keyword. Außerdem sollte übergebenes SQL keine Zeichen oder Optionen enthalten, die für eine Unterabfrage ungültig sind, zum Beispiel:Further, SQL passed shouldn't contain any characters or options that aren't valid on a subquery, such as:

  • Ein nachfolgendes SemikolonA trailing semicolon
  • Auf SQL Server ein nachfolgender Hinweis auf Abfrageebene, z.B. OPTION (HASH JOIN)On SQL Server, a trailing query-level hint (for example, OPTION (HASH JOIN))
  • Eine ORDER BY-Klausel in SQL Server, die nicht mit OFFSET 0 ODER TOP 100 PERCENT in der SELECT-Klausel verwendet wirdOn SQL Server, an ORDER BY clause that isn't used with OFFSET 0 OR TOP 100 PERCENT in the SELECT clause

SQL Server erlaubt die Zusammensetzung von Aufrufen gespeicherter Prozeduren nicht. Daher resultieren Versuche, zusätzliche Abfrageoperatoren auf solche Aufrufe anzuwenden, in ungültigem 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. Verwenden Sie die AsEnumerable- oder AsAsyncEnumerable-Methode direkt nach der FromSqlRaw- oder FromSqlInterpolated-Methode, um sicherzustellen, dass EF Core nicht ersucht, eine gespeicherte Prozedur zusammenzusetzen.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.

Change TrackingChange Tracking

Abfragen, die die FromSqlRaw- oder FromSqlInterpolated-Methode verwenden, befolgen genau dieselben Änderungsnachverfolgungsregeln wie jede andere LINQ-Abfrage in 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. Wenn beispielsweise die Abfrage Entitätstypen projiziert, werden die Ergebnisse standardmäßig nachverfolgt.For example, if the query projects entity types, the results will be tracked by default.

Im folgenden Beispiel wird eine unformatierte SQL-Abfrage verwendet, die aus einer Tabellenwertfunktion (TVF) auswählt und anschließend die Änderungsnachverfolgung mit dem Aufruf auf AsNoTracking deaktiviert: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 = "Lorem ipsum";

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

EinschränkungenLimitations

Bei der Verwendung unformatierter SQL-Abfragen sind einige wenige Einschränkungen zu beachten:There are a few limitations to be aware of when using raw SQL queries:

  • Die SQL-Abfrage muss Daten für sämtliche Eigenschaften des Entitätstyps zurückgeben.The SQL query must return data for all properties of the entity type.
  • Die Spaltennamen im Resultset müssen mit den Spaltennamen übereinstimmen, denen Eigenschaften zugewiesen sind.The column names in the result set must match the column names that properties are mapped to. Beachten Sie, dass sich dieses Verhalten von EF6 unterscheidet.Note this behavior is different from EF6. EF6 ignoriert die Zuordnung von Eigenschaften zu Spalten bei unformatierten SQL-Abfragen, und Spaltennamen von Resultsets mussten mit den Eigenschaftennamen übereinstimmen.EF6 ignored property to column mapping for raw SQL queries and result set column names had to match the property names.
  • Die SQL-Abfrage darf keine zugehörigen Daten enthalten.The SQL query can't contain related data. In vielen Fällen können Sie die Abfrage jedoch mit dem Operator Include zusammensetzen, damit zugehörige Daten zurückgegeben werden (siehe Einschließen zugehöriger Daten).However, in many cases you can compose on top of the query using the Include operator to return related data (see Including related data).

Vorherige VersionenPrevious versions

In EF Core Version 2.2 und früher gab es zwei Überladungen für eine Methode namens FromSql, die sich wie die neueren Methoden FromSqlRaw und FromSqlInterpolated verhielten.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. Es war leicht, versehentlich die unformatierte Zeichenfolgenmethode aufzurufen, wenn eigentlich ein Aufruf der interpolierten Zeichenfolgenmethode beabsichtigt war oder umgekehrt.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. Versehentliche Aufrufe der falschen Überladung konnten dazu führen, dass Abfragen nicht parametrisiert wurden, wenn sie parametrisiert werden sollten.Calling wrong overload accidentally could result in queries not being parameterized when they should have been.