SQL-Abfragen

  • Artikel

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.

Tipp

Das in diesem Artikel verwendete Beispiel finden Sie auf GitHub.

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();

Hinweis

FromSql wurde in EF Core 7.0 eingeführt. Greifen Sie beim Arbeiten mit älteren Versionen stattdessen auf FromSqlInterpolated zurück.

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

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

Hinweis

FromSql kann nur direkt auf ein DbSet angewendet werden. Es kann nicht über eine beliebige LINQ-Abfrage zusammengesetzt werden.

Übergeben von Parametern

Warnung

Achten Sie bei der Verwendung von SQL-Abfragen auf die Parametrisierung.

Wenn Sie von Benutzern bereitgestellte Werte in eine SQL-Abfrage aufnehmen, muss darauf geachtet werden, dass Angriffe durch Einschleusung von SQL-Befehlen vermieden werden. Eine Einschleusung von SQL-Befehlen liegt vor, wenn ein Programm einen vom Benutzer bereitgestellten Zeichenfolgenwert in eine SQL-Abfrage integriert und der vom Benutzer bereitgestellte Wert so manipuliert wird, dass er die Zeichenfolge beendet und einen anderen schädlichen SQL-Vorgang ausführt. Weitere Informationen zur Einschleusung von SQL-Befehlen finden Sie auf dieser Seite.

Die Methoden FromSql und FromSqlInterpolated sind gegen Einschleusung von SQL-Befehlen geschützt und integrieren Parameterdaten stets als separate SQL-Parameter. Die Methode FromSqlRaw kann jedoch bei unsachgemäßer Verwendung anfällig für Angriffe durch Einschleusung von SQL-Befehlen sein. Weitere Informationen finden Sie unten.

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();

Hinweis

Die übergebenen Parameter müssen genau mit der Definition der gespeicherten Prozedur übereinstimmen. Achten Sie besonders auf die Reihenfolge der Parameter und darauf, dass Sie keinen davon vergessen oder falsch platzieren. Sie können auch die Notation für benannte Parameter verwenden. Vergewissern Sie sich außerdem, dass die Parametertypen übereinstimmen und dass ihre Facetten (Größe, Präzision, Skalierung) wie erforderlich festgelegt sind.

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.

Warnung

Seien Sie bei Verwendung von FromSqlRaw sehr vorsichtig, und stellen Sie stets sicher, dass die Werte entweder aus einer sicheren Quelle stammen oder ordnungsgemäß bereinigt wurden. Angriffe durch Einschleusung von SQL-Befehlen können katastrophale Folgen für Ihre Anwendung haben.

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

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

Hinweis

Dieses Feature wurde in EF Core 7.0 eingeführt.

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();

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();

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.

Hinweis

Vor EF Core 7.0 war es mitunter notwendig, die ExecuteSql-APIs zu verwenden, um eine „Massenaktualisierung“ der Datenbank durchzuführen, wie oben beschrieben. Dies ist wesentlich effizienter als die Abfrage aller übereinstimmenden Zeilen und die anschließende Verwendung von SaveChanges, um sie zu ändern. In EF Core 7.0 wurden ExecuteUpdate und ExecuteDelete eingeführt, wodurch es möglich wurde, effiziente Massenaktualisierungsvorgänge über LINQ auszudrücken. Es wird empfohlen, nach Möglichkeit diese APIs anstelle von ExecuteSql zu verwenden.

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).