Consultas SQL brutoRaw SQL Queries

Entity Framework Core permite que a lista suspensa brutas consultas SQL ao trabalhar com um banco de dados relacional.Entity Framework Core allows you to drop down to raw SQL queries when working with a relational database. Isso pode ser útil se a consulta que você deseja executar não pode ser expressada usando LINQ, ou se usando uma consulta LINQ está resultando em ineficiente SQL sendo enviado para o banco de dados.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.

Dica

Veja o exemplo deste artigo no GitHub.You can view this article's sample on GitHub.

LimitaçõesLimitations

Há algumas limitações a serem consideradas ao usar consultas SQL brutas:There are a couple of limitations to be aware of when using raw SQL queries:

  • Consultas SQL só podem ser usadas para retornar os tipos de entidade que fazem parte do seu modelo.SQL queries can only be used to return entity types that are part of your model. Há um aprimoramento na nossa lista de pendências para habilitar retornando tipos de ad hoc de consultas SQL brutas.There is an enhancement on our backlog to enable returning ad-hoc types from raw SQL queries.

  • A consulta SQL deve retornar dados de todas as propriedades do tipo de entidade.The SQL query must return data for all properties of the entity type.

  • Os nomes de coluna no conjunto de resultados devem corresponder aos nomes de coluna mapeados para propriedades.The column names in the result set must match the column names that properties are mapped to. Observe que isso é diferente de EF6 onde o mapeamento de coluna/propriedade foi ignorado para consultas SQL brutas e tinham nomes que correspondam aos nomes de propriedade de coluna do conjunto de resultados.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.

  • A consulta SQL não pode conter dados relacionados.The SQL query cannot contain related data. No entanto, em muitos casos você pode compor sobre a consulta usando o Include operador para retornar dados relacionados (consulte incluindo dados relacionados).However, in many cases you can compose on top of the query using the Include operator to return related data (see Including related data).

  • SELECTinstruções passadas para este método devem normalmente ser combináveis: núcleo de EF se precisa avaliar os operadores de consulta adicionais no servidor (por exemplo, converter operadores LINQ aplicadas após FromSql), SQL fornecido será tratado como uma subconsulta.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. Isso significa que o SQL passado não deve conter todos os caracteres ou opções que não são válidas em uma subconsulta, tais como:This means that the SQL passed should not contain any characters or options that are not valid on a subquery, such as:

    • um ponto e vírgula à direitaa trailing semicolon
    • No SQL Server, um nível de consulta à direita dica, por exemploOPTION (HASH JOIN)On SQL Server, a trailing query-level hint, e.g. OPTION (HASH JOIN)
    • No SQL Server, um ORDER BY cláusula não é acompanhada de TOP 100 PERCENT no SELECT cláusulaOn SQL Server, an ORDER BY clause that is not accompanied of TOP 100 PERCENT in the SELECT clause
  • Instruções SQL que SELECT são reconhecidos automaticamente como não combinável.SQL statements other than SELECT are recognized automatically as non-composable. Como consequência, os resultados completos de procedimentos armazenados sempre são retornados ao cliente e os operadores LINQ aplicadas após FromSql são avaliadas na memória.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.

Consultas SQL brutas básicasBasic raw SQL queries

Você pode usar o FromSql método de extensão para iniciar uma consulta LINQ com base em uma consulta SQL bruta.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();

Consultas SQL brutas podem ser usadas para executar um procedimento armazenado.Raw SQL queries can be used to execute a stored procedure.

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

Passando parâmetrosPassing parameters

Assim como acontece com qualquer API que aceita SQL, é importante parametrizar qualquer entrada do usuário para proteger contra um ataque de injeção de SQL.As with any API that accepts SQL, it is important to parameterize any user input to protect against a SQL injection attack. Você pode incluir espaços reservados de parâmetros na cadeia de caracteres de consulta SQL e, em seguida, fornece valores de parâmetros como argumentos adicionais.You can include parameter placeholders in the SQL query string and then supply parameter values as additional arguments. Quaisquer valores de parâmetro que você fornecer serão automaticamente convertidos para um DbParameter.Any parameter values you supply will automatically be converted to a DbParameter.

O exemplo a seguir passa um único parâmetro para um procedimento armazenado.The following example passes a single parameter to a stored procedure. Enquanto isso pode parecer como String.Format sintaxe, o valor fornecido é encapsulado em um parâmetro e o nome de parâmetro gerado inserida onde o {0} espaço reservado foi especificado.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();

Essa é a mesma consulta, mas usando a sintaxe de interpolação de cadeia de caracteres, que tem suporte no EF Core 2.0 e posterior: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();

Você também pode criar um DbParameter e fornecê-lo como um valor de parâmetro.You can also construct a DbParameter and supply it as a parameter value. Isso permite que você use parâmetros nomeados na cadeia de caracteres de consulta 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();

Compor com LINQComposing with LINQ

Se a consulta SQL pode ser composta no banco de dados, você pode compor sobre a consulta SQL bruta inicial usando operadores 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. Consultas SQL que podem ser compostas em ser com o SELECT palavra-chave.SQL queries that can be composed on being with the SELECT keyword.

O exemplo a seguir usa uma consulta SQL bruta que seleciona a partir de uma função com valor de tabela (TVF) e, em seguida, compõe usando LINQ para executar a filtragem e classificação.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();

Compondo operadores LINQ pode ser usado para incluir dados relacionados na consulta.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();

Aviso

Sempre use a parametrização para consultas SQL brutas: APIs que aceitam um SQL bruto da cadeia de caracteres como FromSql e ExecuteSqlCommand permitir valores facilmente ser passados como parâmetros.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. Além de validar a entrada do usuário, sempre use parametrização para os valores usados em um consulta SQL bruto/comando.In addition to validating user input, always use parameterization for any values used in a raw SQL query/command. Se você estiver usando a concatenação de cadeia de caracteres para criar dinamicamente a qualquer parte da cadeia de caracteres de consulta, você será responsável por validar qualquer entrada para proteger contra ataques de injeção de 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.