SQL-Abfragen

Entity Framework Core ermöglicht Ihnen, beim Arbeiten mit einer relationalen Datenbank auf SQL-Abfragen zurückzugreifen. SQL-Abfragen sind nützlich, wenn die gewünschte Abfrage nicht mit LINQ ausgedrückt werden kann oder eine LINQ-Abfrage EF dazu veranlasst, ineffizienten SQL-Code zu generieren. SQL-Abfragen können reguläre oder schlüssellose Entitätstypen zurückgeben, die Teil Ihres Modells sind.

Einfache SQL-Abfragen

Sie können FromSql verwenden, um eine LINQ-Abfrage basierend auf einer SQL-Abfrage zu beginnen:

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

SQL-Abfragen eignen sich zur Ausführung einer gespeicherten Prozedur, die Entitätsdaten zurückgibt:

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

Übergeben von Parametern

Im folgenden Beispiel wird ein einzelner Parameter an eine gespeicherte Prozedur übergeben, indem ein Parameterplatzhalter in die SQL-Abfragezeichenfolge aufgenommen und ein zusätzliches Argument angegeben wird:

var user = "johndoe";

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

Diese Syntax sieht zwar aus wie eine reguläre Zeichenfolgeninterpolation in C# aus, jedoch wird der angegebene Wert von DbParameter umschlossen, und der generierte Parametername wird dort eingefügt, wo der Platzhalter {0} angegeben wurde. Dies schützt FromSql vor Angriffen durch Einschleusung von SQL-Befehlen und sendet den Wert effizient und korrekt an die Datenbank.

Bei Ausführung gespeicherter Prozeduren kann es sinnvoll sein, benannte Parameter in der SQL-Abfragezeichenfolge zu verwenden, insbesondere wenn die gespeicherte Prozedur optionale Parameter hat:

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

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

Wenn Sie mehr Kontrolle über den zu sendenden Datenbankparameter benötigen, können Sie auch einen DbParameter erstellen und ihn als Parameterwert angeben. Auf diese Weise können Sie den gewünschten Datenbanktyp des Parameters oder Facetten wie Größe, Genauigkeit oder Länge festlegen:

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

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

Dynamisches SQL und Parameter

FromSql und die Parametrisierung sollten nach Möglichkeit verwendet werden. Es gibt jedoch bestimmte Szenarien, in denen der SQL-Code dynamisch zusammengesetzt werden muss und Datenbankparameter nicht verwendet werden können. Angenommen, eine C#-Variable enthält den Namen einer Eigenschaft, nach der gefiltert werden soll. Es könnte verlockend sein, eine SQL-Abfrage wie die folgende auszuführen:

var propertyName = "User";
var propertyValue = "johndoe";

var blogs = context.Blogs
    .FromSql($"SELECT * FROM [Blogs] WHERE {propertyName} = {propertyValue}")
    .ToList();

Dieser Code funktioniert nicht, da Datenbanken die Parametrisierung von Spaltennamen (oder anderen Teilen des Schemas) nicht zulassen.

Zunächst ist es wichtig, die Auswirkungen der dynamischen Erstellung einer Abfrage – über SQL oder auf andere Weise – zu bedenken. Wenn Sie einen Spaltennamen von einem Benutzer akzeptieren, kann dieser eine Spalte auswählen, die nicht indiziert ist, sodass die Abfrage extrem langsam ausgeführt wird und Ihre Datenbank überlastet. Oder er kann eine Spalte auswählen, die Daten enthält, die Sie nicht verfügbar machen möchten. Außer in wirklich dynamischen Szenarien ist es in der Regel besser, für zwei Spaltennamen mit zwei Abfragen zu arbeiten, als sie durch Parametrisierung in einer einzigen Abfrage zusammenzufassen.

Wenn Sie Ihren SQL-Code dynamisch erstellen möchten, müssen Sie FromSqlRaw verwenden. Damit können Sie variable Daten direkt in die Zeichenfolge des SQL-Codes interpolieren, anstatt einen Datenbankparameter zu verwenden:

var columnName = "Url";
var columnValue = new SqlParameter("columnValue", "http://SomeURL");

var blogs = context.Blogs
    .FromSqlRaw($"SELECT * FROM [Blogs] WHERE {columnName} = @columnValue", columnValue)
    .ToList();

Im obigen Code wird der Name der Spalte mithilfe der Zeichenfolgeninterpolation von C# direkt in den SQL-Code eingefügt. Es liegt in Ihrer Verantwortung, sicherzustellen, dass diese Zeichenfolge sicher ist, und sie zu bereinigen, wenn sie aus einer unsicheren Quelle stammt. Das bedeutet, dass Sie Sonderzeichen wie Semikolons, Kommentare und andere SQL-Konstrukte aufspüren und sie entweder ordnungsgemäß mit Escapezeichen versehen oder solche Eingaben zurückweisen.

Andererseits wird der Wert der Spalte über einen DbParameter gesendet und ist daher sicher vor der Einschleusung von SQL-Befehlen.

Zusammensetzen mit LINQ

Sie können die ursprüngliche SQL-Abfrage mithilfe von LINQ-Operatoren ergänzen. EF Core behandelt dann Ihre SQL-Abfrage als Unterabfrage und stellt sie in der Datenbank zusammen. Im folgenden Beispiel wird eine SQL-Abfrage verwendet, die Daten in einer Tabellenwertfunktion auswählt. Anschließend wird sie mithilfe von LINQ zusammengesetzt, um die Filterung und Sortierung durchzuführen.

var searchTerm = "Lorem ipsum";

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

Mit der obigen Abfrage wird der folgende SQL-Code generiert:

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

Einschließen zugehöriger Daten

Der Operator Include kann wie bei jeder anderen LINQ-Abfrage zum Laden zugehöriger Daten verwendet werden:

var searchTerm = "Lorem ipsum";

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

Für die Zusammensetzung mit LINQ muss Ihre SQL-Abfrage zusammensetzbar sein, da EF Core die bereitgestellte SQL-Abfrage als Unterabfrage behandelt. Zusammensetzbare SQL-Abfragen beginnen in der Regel mit dem Schlüsselwort SELECT und dürfen keine SQL-Features enthalten, die in einer Unterabfrage nicht gültig sind, wie z. B.:

• Ein nachfolgendes Semikolon
• Auf SQL Server ein nachfolgender Hinweis auf Abfrageebene, z.B. OPTION (HASH JOIN)
• Eine ORDER BY-Klausel in SQL Server, die nicht mit OFFSET 0 ODER TOP 100 PERCENT in der SELECT-Klausel verwendet wird

SQL Server erlaubt die Zusammensetzung von Aufrufen gespeicherter Prozeduren nicht. Daher resultieren Versuche, zusätzliche Abfrageoperatoren auf solche Aufrufe anzuwenden, in ungültigem SQL. Verwenden Sie AsEnumerable oder AsAsyncEnumerable direkt nach FromSql oder FromSqlRaw, um sicherzustellen, dass EF Core nicht ersucht, eine gespeicherte Prozedur zusammenzusetzen.

Change Tracking

Abfragen, die FromSql oder FromSqlRaw verwenden, befolgen genau dieselben Änderungsnachverfolgungsregeln wie jede andere LINQ-Abfrage in EF Core. Wenn beispielsweise die Abfrage Entitätstypen projiziert, werden die Ergebnisse standardmäßig nachverfolgt.

Im folgenden Beispiel wird eine SQL-Abfrage verwendet, die Daten einer Tabellenwertfunktion auswählt und anschließend die Änderungsnachverfolgung mit dem Aufruf von AsNoTracking deaktiviert:

var searchTerm = "Lorem ipsum";

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

Abfragen skalarer (Nicht-Entitäts-) Typen

Während FromSql für die Abfrage von Entitäten nützlich ist, die in Ihrem Modell definiert sind, ermöglicht SqlQuery die einfache Abfrage skalarer Nicht-Entitätstypen über SQL, ohne dass Sie auf Datenzugriffs-APIs auf niedrigerer Ebene zurückgreifen müssen. Die folgende Abfrage ruft z. B. alle IDs aus der Tabelle Blogs ab:

var ids = context.Database
    .SqlQuery<int>($"SELECT [BlogId] FROM [Blogs]")
    .ToList();

var ids = context.Database
    .SqlQuery<int>($"""
                    SELECT "BlogId" FROM "Blogs"
                    """)
    .ToList();

var ids = context.Database
    .SqlQuery<int>($"""
                    SELECT "BlogId" FROM "Blogs"
                    """)
    .ToList();

Sie können auch LINQ-Operatoren über Ihrer SQL-Abfrage zusammensetzen. Da Ihr SQL-Code jedoch eine Unterabfrage wird, auf deren Ausgabespalte der hinzugefügte SQL EF-Code verweisen muss, müssen Sie die Ausgabespalte Value nennen. Die folgende Abfrage gibt beispielsweise die IDs zurück, die über dem ID-Durchschnitt liegen:

var overAverageIds = context.Database
    .SqlQuery<int>($"SELECT [BlogId] AS [Value] FROM [Blogs]")
    .Where(id => id > context.Blogs.Average(b => b.BlogId))
    .ToList();

var overAverageIds = context.Database
    .SqlQuery<int>($"""
                    SELECT "BlogId" AS "Value" FROM "Blogs"
                    """)
    .Where(id => id > context.Blogs.Average(b => b.BlogId))
    .ToList();

var overAverageIds = context.Database
    .SqlQuery<int>($"""
                    SELECT "BlogId" AS "Value" FROM "Blogs"
                    """)
    .Where(id => id > context.Blogs.Average(b => b.BlogId))
    .ToList();

FromSql kann mit jedem skalaren Typ verwendet werden, der von Ihrem Datenbankanbieter unterstützt wird. Wenn Sie einen Typ verwenden möchten, der von Ihrem Datenbankanbieter nicht unterstützt wird, können Sie mithilfe derPräkonventionskonfiguration eine Wertkonvertierung dafür definieren.

SqlQueryRaw ermöglicht wie FromSqlRaw für Entitätstypen eine dynamische Erstellung von SQL-Abfragen.

Ausführen von nicht abfragendem SQL-Code

In einigen Szenarien kann es erforderlich sein, SQL-Code auszuführen, der keine Daten zurückgibt, üblicherweise zum Ändern von Daten in der Datenbank oder zum Aufrufen einer gespeicherten Prozedur, die keine Resultsets zurückgibt. Verwenden Sie hierzu ExecuteSql:

using (var context = new BloggingContext())
{
    var rowsModified = context.Database.ExecuteSql($"UPDATE [Blogs] SET [Url] = NULL");
}

Dadurch wird der bereitgestellte SQL-Code ausgeführt und die Anzahl der geänderten Zeilen zurückgegeben. ExecuteSql schützt, genau wie FromSql, vor Einschleusung von SQL-Befehlen durch Verwendung sicherer Parametrisierung, und ExecuteSqlRaw ermöglicht die dynamische Erstellung von SQL-Abfragen, genau wie FromSqlRaw für Abfragen.

Einschränkungen

Bei der Rückgabe von Entitätstypen von SQL-Abfragen sind einige Einschränkungen zu beachten:

• Die SQL-Abfrage muss Daten für sämtliche Eigenschaften des Entitätstyps zurückgeben.
• Die Spaltennamen im Resultset müssen mit den Spaltennamen übereinstimmen, denen Eigenschaften zugewiesen sind. Beachten Sie, dass sich dieses Verhalten von EF6 unterscheidet. EF6 ignorierte die Zuordnung von Eigenschaften zu Spalten bei SQL-Abfragen, und die Namen der Spalten im Resultset mussten mit den Eigenschaftsnamen übereinstimmen.
• Die SQL-Abfrage darf keine zugehörigen Daten enthalten. 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).