Requêtes SQL

  • Article

Entity Framework Core vous permet de dérouler les requêtes SQL lorsque vous travaillez avec une base de données relationnelle. Les requêtes SQL s’avèrent utiles si la requête dont vous avez besoin ne peut pas être exprimée en utilisant LINQ, ou si une requête LINQ amène EF à générer du code SQL inefficace. Les requêtes SQL peuvent retourner des types d’entités standard ou des types d’entités sans clé qui font partie de votre modèle.

Conseil

Vous pouvez afficher cet exemple sur GitHub.

Requêtes SQL de base

Vous pouvez utiliser FromSql pour commencer une requête LINQ basée sur une requête SQL :

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

Remarque

FromSql a été introduit dans EF Core 7.0. Quand vous utilisez des versions antérieures, utilisez plutôt FromSqlInterpolated.

Les requêtes SQL peuvent être utilisées pour exécuter une procédure stockée qui retourne des données d’entité :

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

Remarque

FromSql peut uniquement être utilisé directement sur un DbSet. Il n’est pas possible de le composer sur une requête LINQ arbitraire.

Passage de paramètres

Avertissement

Soyez très attentif au paramétrage lors de l’utilisation de requêtes SQL

Quand vous introduisez des valeurs fournies par l’utilisateur dans une requête SQL, vous devez prendre soin d’éviter les attaques par injection de code SQL. Une injection de code SQL se produit quand un programme intègre une valeur de chaîne fournie par l’utilisateur dans une requête SQL, et que cette valeur est conçue pour arrêter la chaîne et effectuer une autre opération SQL malveillante. Pour en savoir plus sur l’injection de code SQL, consultez cette page.

Les méthodes FromSql et FromSqlInterpolated sont protégées contre l’injection de code SQL et intègrent toujours les données de paramètre sous forme de paramètre SQL distinct. En revanche, la méthode FromSqlRaw peut être vulnérable aux attaques par injection de code SQL, si elle n’est pas utilisée correctement. Voir ci-dessous pour plus de détails.

L’exemple suivant passe un paramètre unique à une procédure stockée en incluant un espace réservé de paramètre dans la chaîne de requête SQL et en fournissant un argument supplémentaire :

var user = "johndoe";

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

Si cette syntaxe peut ressembler à une interpolation de chaîne C# standard, la valeur fournie est encapsulée dans un DbParameter et le nom de paramètre généré est inséré là où l’espace réservé {0} a été spécifié. Cela permet à la méthode FromSql d’être protégée contre les attaques par injection de code SQL et d’envoyer la valeur efficacement et correctement à la base de données.

Lors de l’exécution de procédures stockées, il peut s’avérer utile d’utiliser des paramètres nommés dans la chaîne de requête SQL, notamment quand la procédure stockée a des paramètres facultatifs :

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

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

Si vous avez besoin de davantage de contrôle sur le paramètre de base de données envoyé, vous pouvez également construire un DbParameter et le fournir en tant que valeur de paramètre. Ainsi, vous pouvez définir le type de base de données précis du paramètre ou des facettes telles que sa taille, sa précision ou sa longueur :

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

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

Remarque

Les paramètres que vous passez doivent correspondre exactement à la définition de la procédure stockée. Portez une attention particulière à l’ordre des paramètres, en prenant soin de ne pas en omettre un ni d’en placer un au mauvais endroit, ou bien envisagez d’utiliser une notation de paramètre nommée. Vérifiez également que les types de paramètres correspondent et que leurs facettes (taille, précision, échelle) sont définies en fonction des besoins.

SQL dynamique et paramètres

Vous devez utiliser FromSql et son paramétrage dès que possible. En revanche, il existe certains scénarios dans lesquels le langage SQL a besoin d’être regroupé dynamiquement et les paramètres de base de données ne peuvent pas être utilisés. Par exemple, supposons qu’une variable C# contienne le nom d’une propriété à filtrer. Il peut être tentant d’utiliser une requête SQL comme la suivante :

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

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

Ce code ne fonctionne pas, car les bases de données n’autorisent pas le paramétrage des noms de colonnes (ou de toute autre partie du schéma).

Tout d’abord, il est important de prendre en compte les implications de la construction dynamique d’une requête, par le biais du langage SQL ou autrement. Accepter un nom de colonne provenant d’un utilisateur peut lui permettre de choisir une colonne qui n’est pas indexée, ce qui ralentit considérablement l’exécution de la requête et surcharge votre base de données, ou encore peut lui permettre de choisir une colonne contenant des données que vous ne voulez pas exposer. À l’exception des scénarios vraiment dynamiques, il est généralement préférable d’avoir deux requêtes pour deux noms de colonnes, plutôt que d’utiliser un paramétrage pour les réduire en une seule requête.

Si vous avez décidé de construire dynamiquement votre code SQL, vous devez utiliser FromSqlRaw, ce qui permet d’interpoler des données de variable directement dans la chaîne SQL, au lieu d’utiliser un paramètre de base de données :

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

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

Dans le code ci-dessus, le nom de colonne est inséré directement dans le code SQL, par interpolation de chaîne C#. Il est de votre responsabilité de veiller à ce que cette valeur de chaîne soit sécurisée, en l’assainissant si son origine n’est pas sécurisée. Ce nettoyage implique de détecter les caractères spéciaux comme les points-virgules, les commentaires et d’autres constructions SQL, ainsi que de les échapper correctement ou de rejeter de telles entrées.

D’un autre côté, la valeur de la colonne est envoyée par le biais d’un DbParameter. Elle est donc protégée contre l’injection de code SQL.

Avertissement

Soyez très prudent quand vous utilisez FromSqlRaw, et veillez toujours à ce que les valeurs proviennent d’une origine sécurisée ou soient correctement assainies. Les attaques par injection de code SQL peuvent avoir des conséquences désastreuses pour votre application.

Composition avec LINQ

Vous pouvez composer par-dessus la requête SQL initiale à l’aide d’opérateurs LINQ. EF Core traite votre code SQL comme une sous-requête et compose dessus dans la base de données. L’exemple suivant utilise une requête SQL qui effectue une sélection à partir d’une Fonction table (TVF). Ensuite, il compose dessus en utilisant LINQ pour effectuer un filtrage et un tri.

var searchTerm = "Lorem ipsum";

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

La requête ci-dessus génère le code SQL suivant :

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

L’opérateur Include peut être utilisé pour charger des données associées, comme avec toute autre requête LINQ :

var searchTerm = "Lorem ipsum";

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

La composition avec LINQ nécessite que votre requête SQL soit composable, car EF Core traite le code SQL fourni comme une sous-requête. Les requêtes SQL composables commencent généralement par le mot clé SELECT et ne peuvent pas contenir de fonctionnalités SQL qui ne sont pas valides dans une sous-requête, comme :

  • Un point-virgule de fin
  • Sur le serveur SQL Server, une indication de niveau de requête en fin (par exemple, OPTION (HASH JOIN))
  • Sur SQL Server, une clause ORDER BY qui n’est pas utilisée avec OFFSET 0 OU TOP 100 PERCENT dans la clause SELECT

SQL Server n’autorise pas de composition sur des appels de procédure stockée. Par conséquent, toute tentative d’application d’opérateurs de requête supplémentaires à un tel appel génère du code SQL non valide. Utilisez AsEnumerable ou AsAsyncEnumerable juste après FromSql ou FromSqlRaw pour veiller à ce qu’EF Core n’essaie pas de composer sur une procédure stockée.

Suivi des modifications

Les requêtes qui utilisent FromSql ou FromSqlRaw suivent strictement les mêmes règles de suivi des modifications que toute autre requête LINQ dans EF Core. Par exemple, si la requête projette des types d’entités, les résultats sont suivis par défaut.

L’exemple suivant utilise une requête SQL qui effectue une sélection dans une Fonction table (TVF), puis désactive le suivi des modifications avec l’appel à AsNoTracking :

var searchTerm = "Lorem ipsum";

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

Interrogation de types scalaires (non-entités)

Remarque

Cette fonctionnalité a été introduite dans EF Core 7.0.

Si FromSql est utile pour interroger des entités définies dans votre modèle, SqlQuery vous permet d’interroger facilement des types scalaires qui ne sont pas des entités via SQL, sans qu’il soit nécessaire d’utiliser des API d’accès aux données de plus bas niveau. Par exemple, la requête suivante extrait tous les ID de la table Blogs :

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

Vous pouvez également composer des opérateurs LINQ sur votre requête SQL. Toutefois, étant donné que votre code SQL devient une sous-requête dont la colonne de sortie a besoin d’être référencée par le code SQL ajouté par EF, vous devez nommer la colonne de sortie Value. Par exemple, la requête suivante retourne les ID qui se trouvent au-dessus de la moyenne de l’ID :

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

FromSql peut être utilisé avec tout type scalaire pris en charge par votre fournisseur de base de données. Si vous voulez utiliser un type non pris en charge par votre fournisseur de base de données, vous pouvez utiliser la configuration de préconvention pour définir une conversion de sa valeur.

SqlQueryRaw permet de construire dynamiquement des requêtes SQL, tout comme FromSqlRaw le permet pour les types d’entités.

Exécution de code SQL de non-requête

Dans certains scénarios, il peut s’avérer nécessaire d’exécuter du code SQL qui ne retourne pas de données, généralement pour modifier des données dans la base de données ou appeler une procédure stockée qui ne retourne aucun jeu de résultats. Ceci peut être fait via ExecuteSql :

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

ExecuteSql exécute le code SQL fourni et retourne le nombre de lignes modifiées. ExecuteSql offre une protection contre l’injection de code SQL en utilisant un paramétrage sécurisé, à l’instar de FromSql, et ExecuteSqlRaw permet une construction dynamique des requêtes SQL, à l’instar de FromSqlRaw pour les requêtes.

Remarque

Avant EF Core 7.0, il était parfois nécessaire d’utiliser les API ExecuteSql pour effectuer une « mise à jour en bloc » sur la base de données, comme ci-dessus. Celles-ci sont considérablement plus efficaces que l’interrogation de toutes les lignes correspondantes suivie de l’utilisation de SaveChanges pour les modifier. EF Core 7.0 a introduit ExecuteUpdate et ExecuteDelete, qui ont rendu possible l’expression d’opérations de mise à jour en bloc efficaces par le biais de LINQ. Il est recommandé d’utiliser ces API dès que possible, plutôt que ExecuteSql.

Limites

Il existe quelques limitations à connaître sur le retour de types d’entités à partir de requêtes SQL :

  • La requête SQL doit retourner des données pour toutes les propriétés du type d’entité.
  • Les noms de colonne dans le jeu de résultats doivent correspondre aux noms de colonne mappés aux propriétés. Notez que ce comportement est différent de celui d’EF6. EF6 ignorait le mappage des propriétés aux colonnes pour les requêtes SQL et les noms de colonnes du jeu de résultats devaient correspondre à ces noms de propriétés.
  • La requête SQL ne peut pas contenir de données associées. Toutefois, dans de nombreux cas, vous pouvez composer au-dessus de la requête à l’aide de l’opérateur Include pour retourner des données associées (consultez Inclusion de données associées).