Consultas SQL

  • Artigo

O Entity Framework Core permite acessar uma lista suspensa de consultas SQL brutas ao trabalhar com um banco de dados relacional. As consultas SQL serão úteis se a consulta desejada não puder ser expressa usando LINQ ou se uma consulta LINQ fizer com que o EF gere SQL ineficiente. As consultas SQL podem retornar tipos de entidade regulares ou tipos de entidade sem chave que fazem parte do seu modelo.

Dica

Veja o exemplo deste artigo no GitHub.

Consultas SQL básicas

Você pode usar FromSql para iniciar uma consulta LINQ com base em uma consulta SQL:

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

Observação

FromSql foi introduzido no EF Core 7.0. Ao usar versões mais antigas, use FromSqlInterpolated em vez disso.

As consultas SQL podem ser usadas para executar um procedimento armazenado que retorna dados de entidade:

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

Observação

FromSql só pode ser usado diretamente em um DbSet. Ele não pode ser composto por uma consulta LINQ arbitrária.

Passando parâmetros

Aviso

Preste muita atenção à parametrização ao usar consultas SQL

Ao introduzir os valores fornecidos pelo usuário em uma consulta SQL, é necessário ter cuidado para evitar ataques de injeção de SQL. A injeção de SQL ocorre quando um programa integra um valor de cadeia de caracteres fornecido pelo usuário em uma consulta SQL e o valor fornecido pelo usuário é criado para encerrar a cadeia de caracteres e executar outra operação SQL mal-intencionada. Para saber mais sobre a injeção de SQL, consulte essa página.

Os método FromSql e FromSqlInterpolated são seguros contra injeção de SQL e sempre integram dados de parâmetro como um parâmetro SQL separado. No entanto, o método FromSqlRaw pode ser vulnerável a ataques de injeção de SQL, se usado incorretamente. Consulte abaixo para obter mais detalhes.

O exemplo a seguir passa um único parâmetro para um procedimento armazenado, incluindo um espaço reservado de parâmetro na cadeia de caracteres de consulta SQL e fornecendo um argumento adicional:

var user = "johndoe";

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

Embora essa sintaxe possa parecer uma interpolação de cadeia de caracteres C# comum, o valor fornecido é encapsulado em um DbParameter e o nome do parâmetro gerado inserido onde o espaço reservado {0} foi especificado. Isso torna FromSql seguro contra ataques de injeção de SQL e envia o valor de forma eficiente e correta para o banco de dados.

Ao executar procedimentos armazenados, pode ser útil usar parâmetros nomeados na cadeia de caracteres de consulta SQL, especialmente quando o procedimento armazenado tem parâmetros opcionais:

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

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

Se você precisar de mais controle sobre o parâmetro de banco de dados que está sendo enviado, também poderá construir um DbParameter e fornecê-lo como um valor de parâmetro. Isso permite que você defina o tipo de banco de dados preciso do parâmetro ou facetas como seu tamanho, precisão ou comprimento:

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

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

Observação

Os parâmetros que você passa devem corresponder exatamente à definição de procedimento armazenado. Preste atenção especial à ordenação dos parâmetros, tomando cuidado para não perder ou perder nenhum deles ou considere o uso da notação de parâmetro nomeada. Além disso, verifique se os tipos de parâmetro correspondem e se suas facetas (tamanho, precisão, escala) são definidas conforme necessário.

SQL e parâmetros dinâmicos

FromSql e sua parametrização devem ser usados sempre que possível. No entanto, há certos cenários em que o SQL precisa ser agrupado dinamicamente e os parâmetros de banco de dados não podem ser usados. Por exemplo, vamos supor que uma variável C# contém o nome de uma propriedade a ser filtrada. Pode ser tentador usar uma consulta SQL, como a seguinte:

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

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

Esse código não funciona, pois os bancos de dados não permitem parametrizar nomes de coluna (ou qualquer outra parte do esquema).

Primeiro, é importante considerar as implicações da construção dinâmica de uma consulta por meio do SQL ou de outra forma. Aceitar um nome de coluna de um usuário pode permitir que ele escolha uma coluna que não esteja indexada, fazendo com que a consulta seja executada de forma extremamente lenta e sobrecarregue seu banco de dados, ou pode permitir que eles escolham uma coluna contendo dados que você não deseja expor. Com exceção de cenários realmente dinâmicos, geralmente é melhor ter duas consultas para dois nomes de coluna, em vez de usar a parametrização para recolhê-las em uma única consulta.

Se você decidiu que deseja construir dinamicamente seu SQL, precisará usar FromSqlRaw, o que permite interpolar dados variáveis diretamente na cadeia de caracteres SQL, em vez de usar um parâmetro de banco de dados:

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

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

No código acima, o nome da coluna é inserido diretamente no SQL, usando a interpolação de cadeia de caracteres C#. É sua responsabilidade garantir que esse valor de cadeia de caracteres seja seguro, higienizando-o se ele vier de uma origem não segura. Isso significa detectar caracteres especiais, como ponto-e-vírgula, comentários e outros constructos do SQL, e escapá-los corretamente ou rejeitar tais entradas.

Por outro lado, o valor da coluna é enviado por meio de um DbParameter e, portanto, é seguro diante da injeção de SQL.

Aviso

Tenha muito cuidado ao usar FromSqlRaw e sempre verifique se os valores são de uma origem segura ou estão devidamente higienizados. Ataques de injeção de SQL podem ter consequências de desastre para seu aplicativo.

Como compor com o LINQ

Você pode redigir sobre a consulta SQL inicial usando operadores LINQ. O EF Core tratará seu SQL como uma subconsulta e redigirá sobre ele no banco de dados. O exemplo a seguir usa uma consulta SQL que seleciona de uma função com valor de tabela (TVF). Em seguida, combina com base nele usando LINQ para fazer filtragem e classificação.

var searchTerm = "Lorem ipsum";

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

Essa consulta gera o seguinte SQL:

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

O operador Include pode ser usado para carregar dados relacionados, assim como em qualquer outra consulta LINQ:

var searchTerm = "Lorem ipsum";

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

Combinar com LINQ requer que sua consulta SQL seja redigida, pois o EF Core tratará o SQL fornecido como uma subconsulta. As consultas SQL combináveis geralmente começam com a palavra-chave SELECT e não podem conter recursos SQL que não sejam válidos em uma subconsulta, como:

  • Um ponto-e-vírgula à direita
  • No SQL Server, uma dica a nível de consulta à direita (por exemplo, OPTION (HASH JOIN))
  • No SQL Server, uma cláusula ORDER BY que não é usada com OFFSET 0 OR TOP 100 PERCENT na cláusula SELECT

O SQL Server não permite a composição em chamadas de procedimento armazenado, portanto, qualquer tentativa de aplicar operadores de consulta adicionais a essa chamada resultará em SQL inválido. Use AsEnumerable ou AsAsyncEnumerable logo após FromSql ou FromSqlRaw para garantir que o EF Core não tente redigir um procedimento armazenado.

Controle de Alterações

As consultas que usam FromSql ou FromSqlRaw seguem exatamente as mesmas regras de controle de alterações que qualquer outra consulta LINQ no EF Core. Por exemplo, se a consulta projetar tipos de entidade, os resultados serão controlados por padrão.

O exemplo a seguir usa uma consulta SQL que seleciona uma função com valor de tabela (TVF) e, em seguida, desativa o controle de alterações com a chamada a AsNoTracking:

var searchTerm = "Lorem ipsum";

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

Como consultar tipos escalares (não entidades)

Observação

Esse recurso foi introduzido no EF Core 7.0.

Embora FromSql seja útil para consultar entidades definidas em seu modelo, SqlQuery permite que você consulte facilmente tipos escalares e não de entidade por meio do SQL, sem a necessidade de baixar para APIs de acesso a dados de nível inferior. Por exemplo, a consulta a seguir busca todas as IDs da tabela Blogs:

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

Você também pode compor operadores LINQ em sua consulta SQL. No entanto, como o SQL se torna uma subconsulta cuja coluna de saída precisa ser referenciada pelo SQL EF adiciona, você deve nomear a coluna de saída Value. Por exemplo, a consulta a seguir retorna as IDs que estão acima da média da ID:

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

FromSql pode ser usado com qualquer tipo escalar compatível com seu provedor de banco de dados. Se você quiser usar um tipo sem suporte pelo provedor de banco de dados, poderá usar a configuração de pré-convenção para definir uma conversão de valor para ele.

SqlQueryRaw permite a construção dinâmica de consultas SQL, assim como FromSqlRaw o faz para tipos de entidade.

Como executar o SQL que não está consultando

Em alguns cenários, pode ser necessário executar o SQL que não retorna nenhum dado, normalmente para modificar dados no banco de dados ou chamar um procedimento armazenado que não retorna nenhum conjunto de resultados. Isso pode ser feito por meio de ExecuteSql:

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

Isso executa o SQL fornecido e retorna o número de linhas modificadas. ExecuteSql protege contra injeção de SQL usando parametrização segura, assim como FromSql, e ExecuteSqlRaw permite a construção dinâmica de consultas SQL, assim como FromSqlRawo faz para consultas.

Observação

Antes do EF Core 7.0, às vezes era necessário usar as APIs ExecuteSql para executar uma "atualização em massa" no banco de dados, como acima; isso é consideravelmente mais eficiente do que consultar todas as linhas correspondentes e, em seguida, usar SaveChanges para modificá-las. O EF Core 7.0 introduziu ExecuteUpdate e ExecuteDelete, o que possibilitou expressar operações eficientes de atualização em massa por meio do LINQ. É recomendável usar essas APIs sempre que possível, em vez de ExecuteSql.

Limitações

Há algumas limitações a serem observadas ao retornar tipos de entidade de consultas SQL:

  • A consulta SQL deve retornar dados para todas as propriedades do tipo de entidade.
  • Os nomes das colunas no conjunto de resultados devem corresponder aos nomes das colunas para as quais as propriedades são mapeadas. Observe que esse comportamento é diferente do EF6. O EF6 ignorou o mapeamento de propriedade para coluna para consultas SQL e os nomes de coluna do conjunto de resultados tiveram que corresponder a esses nomes de propriedade.
  • A consulta SQL não pode conter dados relacionados. 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).