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. Dies kann nützlich sein, wenn die durchzuführende Abfrage nicht mit LINQ formuliert werden kann oder wenn die Verwendung einer LINQ-Abfrage dazu führt, dass eine ineffiziente SQL-Abfrage an die Datenbank gesendet wird.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.

Tipp

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

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:

  • SQL-Abfragen können nur für die Rückgabe von Entitätstypen verwendet werden, die Teil Ihres Modells sind.SQL queries can only be used to return entity types that are part of your model. In unserem Backlog gibt es eine Erweiterung zum Aktivieren der Rückgabe von Ad-hoc-Typen aus unformatierten SQL-Abfragen.There is an enhancement on our backlog to enable returning ad-hoc types from raw SQL queries.

  • Die SQL-Abfrage muss Daten für sämtliche Eigenschaften des Entitäts- oder Abfragetyps zurückgeben.The SQL query must return data for all properties of the entity or query 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 dies von EF 6 unterscheidet. Dort wurde die Zuordnung von Eigenschaften/Spalten bei unformatierten SQL-Abfragen ignoriert, und Spaltennamen in einem Resultset mussten mit den Eigenschaftsnamen übereinstimmen.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.

  • Die SQL-Abfrage darf keine zugehörigen Daten enthalten.The SQL query cannot 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).

  • An diese Methode übergebene SELECT-Anweisungen sollten im Allgemeinen zusammensetzbar sein: Wenn EF Core weitere Abfrageoperatoren auf dem Server auswerten muss (z.B. nach FromSql angewendete LINQ-Operatoren verschieben muss), wird die bereitgestellte SQL-Abfrage wie eine Unterabfrage behandelt.SELECT statements passed to this method should generally be composable: If EF Core needs to evaluate additional query operators on the server (e.g. to translate LINQ operators applied after FromSql), the supplied SQL will be treated as a subquery. Das heißt, dass die übergebene SQL-Abfrage keine Zeichen oder Optionen enthalten sollte, die in einer Unterabfrage ungültig sind, wie z.B.:This means that the SQL passed should not contain any characters or options that are not valid on a subquery, such as:

    • ein nachfolgendes Semikolona trailing semicolon
    • Auf SQL Server ein nachfolgender Hinweis auf Abfrageebene-Hinweis, z.B. OPTION (HASH JOIN)On SQL Server, a trailing query-level hint, e.g. OPTION (HASH JOIN)
    • Auf SQL Server eine ORDER BY-Klausel, die nicht durch TOP 100 PERCENT in der SELECT-Klausel ergänzt wirdOn SQL Server, an ORDER BY clause that is not accompanied of TOP 100 PERCENT in the SELECT clause
  • Andere SQL-Anweisungen als SELECT werden automatisch als nicht zusammensetzbar erkannt.SQL statements other than SELECT are recognized automatically as non-composable. Die vollständigen Ergebnisse gespeicherter Prozeduren werden daher immer an den Client zurückgegeben, und sämtliche LINQ-Operatoren, die nach FromSql angewendet werden, werden speicherintern ausgewertet.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.

Grundlegende unformatierte SQL-AbfragenBasic raw SQL queries

Sie können die FromSql-Erweiterungsmethode verwenden, um eine LINQ-Abfrage basierend auf einer unformatierten SQL-Abfrage zu starten.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();

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
    .FromSql("EXECUTE dbo.GetMostPopularBlogs")
    .ToList();

Übergeben von ParameternPassing parameters

Wie bei jeder API, die von SQL akzeptiert wird, ist es wichtig, sämtliche Benutzereingaben zum Schutz vor Angriffen durch die Einschleusung von SQL-Befehlen zu parametrisieren.As with any API that accepts SQL, it is important to parameterize any user input to protect against a SQL injection attack. Sie können in der SQL-Abfragezeichenfolge Platzhalter für Parameter einschließen und anschließend Parameterwerte als zusätzliche Argumente bereitstellen.You can include parameter placeholders in the SQL query string and then supply parameter values as additional arguments. Sämtliche von Ihnen bereitgestellte Parameterwerte werden automatisch in einen DbParameter konvertiert.Any parameter values you supply will automatically be converted to a DbParameter.

Im folgenden Beispiel wird ein einzelner Parameter an eine gespeicherte Prozedur übergeben.The following example passes a single parameter to a stored procedure. Obwohl dies String.Format-Syntax ähnelt, ist der bereitgestellte Wert in einen Parameter eingeschlossen. Der Name des generierten Parameters wird dort eingefügt, wo der Platzhalter {0} angegeben wurde.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();

Dies ist die gleiche Abfrage, jedoch mit einer Zeichenfolgeninterpolationssyntax, die ab EF Core 2.0 unterstützt wird: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();

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. Dadurch können Sie benannte Parameter in der SQL-Abfragezeichenfolge verwenden.This 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();

Zusammensetzen mit LINQComposing with LINQ

Wenn die SQL-Abfrage in der Datenbank zusammengesetzt werden kann, können Sie die erste unformatierte SQL-Abfrage mit LINQ-Operatoren zusammensetzen.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. Zusammensetzbare SQL-Abfragen beginnen mit dem Schlüsselwort SELECT.SQL queries that can be composed on being with the SELECT keyword.

Im folgenden Beispiel wird eine unformatierte SQL-Abfrage verwendet, die eine Auswahl aus einer Tabellenwertfunktion trifft und diese anschließend mit LINQ zur Durchführung einer Filterung und Sortierung zusammensetzt.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();

Das Zusammensetzen mit LINQ-Operatoren dient zum Einschließen zugehöriger Daten in der Abfrage.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();

Warnung

Für unformatierte SQL-Abfragen immer Parametrisierung verwenden: APIs, die eine unformatierte SQL-Zeichenfolge wie FromSql und ExecuteSqlCommand akzeptieren, lassen zu, dass Werte problemlos als Parameter übergeben werden.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. Zusätzlich zum Überprüfen der Benutzereingabe sollten Sie die Parametrisierung für sämtliche Werte verwenden, die in unformatierten SQL-Abfragen/-Befehlen enthalten sind.In addition to validating user input, always use parameterization for any values used in a raw SQL query/command. Wenn Sie mit der Zeichenfolgenverkettung auf dynamische Weise Teile der Abfragezeichenfolge erstellen, müssen Sie zum Schutz vor Angriffen durch die Einschleusung von SQL-Befehlen jede Eingabe überprüfen.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.