Consultas SQL brutasRaw SQL Queries

O Entity Framework Core permite acessar uma lista suspensa de consultas SQL brutas 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 realizar não puder ser expressa usando o LINQ ou se o uso de uma consulta do LINQ estiver resultando no envio de um SQL ineficiente 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. Consultas SQL brutas podem retornar tipos de entidade ou, a partir do EF Core 2.1, tipos de consulta que fazem parte do seu modelo.Raw SQL queries can return entity types or, starting with EF Core 2.1, query types that are part of your model.

Dica

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

LimitaçõesLimitations

Algumas limitações deve ser consideradas ao usar consultas SQL brutas:There are a few limitations to be aware of when using raw SQL queries:

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

  • Os nomes das colunas no conjunto de resultados devem corresponder aos nomes das colunas para as quais as propriedades são mapeadas.The column names in the result set must match the column names that properties are mapped to. Observe que isso é diferente do EF6, onde o mapeamento de coluna/propriedade foi ignorado para consultas SQL brutas e os nomes das colunas do conjunto de resultados tinham que corresponder aos nomes das propriedades.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 é possível combinar com base na consulta usando o operador Include para retornar dados relacionados (confira Como incluir 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).

  • As instruções SELECT aprovadas para este método devem normalmente ser combináveis: se o EF Core precisar avaliar operadores de consulta adicionais no servidor (por exemplo, para traduzir operadores LINQ aplicados após FromSql), o 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 (for example, to translate LINQ operators applied after FromSql), the supplied SQL will be treated as a subquery. Isso significa que o SQL aprovado não deve conter nenhum caractere ou opção não válida em uma subconsulta, 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, uma dica a nível de consulta à direita (por exemplo, OPTION (HASH JOIN))On SQL Server, a trailing query-level hint (for example, OPTION (HASH JOIN))
    • No SQL Server, um cláusula ORDER BY não é acompanhada de TOP 100 PERCENT na cláusula SELECTOn SQL Server, an ORDER BY clause that is not accompanied of TOP 100 PERCENT in the SELECT clause
  • As instruções SQL, diferentes de SELECT, são reconhecidas automaticamente como não combináveis.SQL statements other than SELECT are recognized automatically as non-composable. Como consequência, os resultados completos de procedimentos armazenados são sempre retornados ao cliente e os operadores LINQ aplicados após FromSql são avaliados 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 método de extensão FromSql 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();

As 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 fornecer os 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. Qualquer valor de parâmetro fornecido será automaticamente convertido em um DbParameter.Any parameter values you supply will automatically be converted to a DbParameter.

O exemplo a seguir aprova um único parâmetro para um procedimento armazenado.The following example passes a single parameter to a stored procedure. Embora isso possa parecer a sintaxe String.Format, o valor fornecido é encapsulado em um parâmetro e o nome de parâmetro gerado é inserido onde o espaço reservado {0} 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 construir um DbParameter e fornecê-lo como valor de parâmetro.You can also construct a DbParameter and supply it as a parameter value. Isso permite usar 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();

Como compor com o LINQComposing with LINQ

Se a consulta SQL puder ser composta no banco de dados, é possível compor com base na consulta SQL bruta inicial usando os 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. As consultas SQL que podem ser compostas com a palavra-chave SELECT.SQL queries that can be composed on being with the SELECT keyword.

O exemplo a seguir usa uma consulta SQL bruta que é selecionada de uma Função com Valor de Tabela (TVF) e compõe com base nela usando o LINQ para filtrar e classificar.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();

A composição com operadores LINQ pode ser usada 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: as APIs que aceitam uma cadeia de caracteres SQL, como FromSql e ExecuteSqlCommand, permitem que os valores sejam facilmente aprovados 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 a parametrização para os valores usados em um comando/consulta SQL bruta.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 cadeias de caracteres para criar de forma dinâmica qualquer parte da cadeia de caracteres de consulta, você é responsável por validar qualquer entrada para proteger-se de 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.