Requêtes SQL brutesRaw SQL Queries

Entity Framework Core vous permet d’examiner les requêtes SQL brutes lorsque vous travaillez avec une base de données relationnelle.Entity Framework Core allows you to drop down to raw SQL queries when working with a relational database. Cela peut être utile si la requête que vous voulez effectuer ne peut pas être exprimée à l’aide de LINQ ou que l’utilisation d’une requête LINQ se traduit par des requêtes SQL inefficaces.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 queries. Les requêtes SQL brutes peuvent retourner des types d’entités ou, à partir d’EF Core 2.1, des types de requête qui font partie de votre modèle.Raw SQL queries can return entity types or, starting with EF Core 2.1, query types that are part of your model.

Conseil

Vous pouvez afficher cet exemple sur GitHub.You can view this article's sample on GitHub.

LimitationsLimitations

Il existe quelques limitations à connaître lors de l’utilisation des requêtes SQL brutes :There are a few limitations to be aware of when using raw SQL queries:

  • La requête SQL doit retourner des données pour toutes les propriétés du type d’entité ou de requête.The SQL query must return data for all properties of the entity or query type.

  • Les noms de colonne dans le jeu de résultats doivent correspondre aux noms de colonne mappés aux propriétés.The column names in the result set must match the column names that properties are mapped to. Notez que cela est différent à compter d’EF6, où le mappage de propriétés/colonnes était ignoré pour les requêtes SQL brutes et où les noms de colonne du jeu de résultats devaient correspondre aux noms des propriétés.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.

  • La requête SQL ne peut pas contenir de données associées.The SQL query cannot contain related data. 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).However, in many cases you can compose on top of the query using the Include operator to return related data (see Including related data).

  • Les instructions SELECT passées à cette méthode doivent généralement être composables : si EF Core a besoin évaluer des opérateurs de requête supplémentaires sur le serveur (par exemple, pour traduire les opérateurs LINQ appliqués après FromSql), le SQL fourni sera considéré comme une sous-requête.SELECT statements passed to this method should generally be composable: If EF Core needs to evaluate additional query operators on the server (for example, to translate LINQ operators applied after FromSql), the supplied SQL will be treated as a subquery. Cela signifie que l’instruction SQL passée ne doit pas contenir de caractères ou d’options qui ne sont pas valides sur une sous-requête, comme :This means that the SQL passed should not contain any characters or options that are not valid on a subquery, such as:

    • un point-virgule de fina trailing semicolon
    • Sur le serveur SQL Server, une indication de niveau de requête en fin (par exemple, OPTION (HASH JOIN))On SQL Server, a trailing query-level hint (for example, OPTION (HASH JOIN))
    • Sur le serveur SQL Server, une clause ORDER BY n’est pas accompagnée de TOP 100 PERCENT dans la clause SELECTOn SQL Server, an ORDER BY clause that is not accompanied of TOP 100 PERCENT in the SELECT clause
  • Les instructions SQL autres que SELECT sont reconnues automatiquement en tant que non composables.SQL statements other than SELECT are recognized automatically as non-composable. Par conséquent, les résultats complets des procédures stockées sont toujours retournés au client et tous les opérateurs LINQ appliqués après FromSql sont évalués en mémoire.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.

Requêtes SQL brutes de baseBasic raw SQL queries

Vous pouvez utiliser la méthode d’extension FromSql pour lancer une requête LINQ basée sur une requête SQL brute.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();

Les requêtes SQL brutes peuvent servir à exécuter une procédure stockée.Raw SQL queries can be used to execute a stored procedure.

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

Passage de paramètresPassing parameters

Comme avec toute API qui accepte SQL, il est important de paramétrer toutes les entrées d’utilisateur pour protéger contre les attaques par injection SQL.As with any API that accepts SQL, it is important to parameterize any user input to protect against a SQL injection attack. Vous pouvez inclure des espaces réservés de paramètre dans la chaîne de requête SQL et fournir ensuite les valeurs de paramètre en tant qu’arguments supplémentaires.You can include parameter placeholders in the SQL query string and then supply parameter values as additional arguments. Les valeurs de paramètre que vous fournissez seront automatiquement converties en DbParameter.Any parameter values you supply will automatically be converted to a DbParameter.

L’exemple suivant passe un paramètre unique à une procédure stockée.The following example passes a single parameter to a stored procedure. Si cela ressemble à la syntaxe de String.Format, la valeur fournie est encapsulée dans un paramètre et le nom de paramètre généré est inséré là où l’espace réservé {0} a été spécifié.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();

Il s’agit de la même requête, mais avec la syntaxe d’interpolation de chaîne, qui est prise en charge dans EF Core 2.0 et versions ultérieures :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();

Vous pouvez également construire un objet DbParameter et le fournir en tant que valeur de paramètre.You can also construct a DbParameter and supply it as a parameter value. Cela vous permet d’utiliser des paramètres nommés dans la chaîne de requête SQLThis 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();

Composition avec LINQComposing with LINQ

Si la requête SQL peut être composée dans la base de données, vous pouvez composer au-dessus de la requête SQL brute initiale à l’aide des opérateurs LINQ.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. Les requêtes SQL qui peuvent être composées commencent par le mot clé SELECT.SQL queries that can be composed on begin with the SELECT keyword.

L’exemple suivant utilise une requête SQL brute qui sélectionne à partir d’une fonction table (TVF) et compose dessus à l’aide de LINQ pour effectuer le filtrage et le tri.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();

La composition avec les opérateurs LINQ peut servir à inclure les données associées dans la requête.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();

Avertissement

Utilisez toujours le paramétrage pour les requêtes SQL brutes : les API acceptant une chaîne SQL brute comme FromSql et ExecuteSqlCommand autorisent les valeurs à passer facilement en tant que paramètres.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. En plus de valider l’entrée utilisateur, vous devez toujours utiliser le paramétrage pour toutes les valeurs utilisées dans une commande/requête en SQL brut.In addition to validating user input, always use parameterization for any values used in a raw SQL query/command. Si vous utilisez la concaténation de chaînes pour générer dynamiquement une partie de la chaîne de requête, vous êtes responsable de la validation de l’entrée pour vous protéger contre les attaques par injection SQL.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.