Partilhar via

Consultas SQL

  • Artigo

O Entity Framework Core permite que se recorra a consultas SQL quando trabalha com um banco de dados relacional. As consultas SQL sã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.

Consultas SQL básicas

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

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

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 = await context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogs")
    .ToListAsync();

Observação

FromSql só pode ser usado diretamente num DbSet​. Ele não pode ser composto sobre uma consulta LINQ arbitrária.

Parâmetros de passagem

Advertência

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

Ao introduzir quaisquer valores fornecidos pelo usuário em uma consulta SQL, deve-se tomar 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 esta página.

Os métodos FromSql e FromSqlInterpolated são seguros contra a injeção de SQL e sempre integram dados de parâmetros 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. Veja abaixo mais detalhes.

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

var user = "johndoe";

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

Embora essa sintaxe possa se parecer com de interpolação de cadeia de caracteres C#regular, 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 o 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 = await context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser @filterByUser={user}")
    .ToListAsync();

Se precisar de mais controle sobre o parâmetro de banco de dados que está sendo enviado, você também pode 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 = await context.Blogs
    .FromSql($"EXECUTE dbo.GetMostPopularBlogsForUser {user}")
    .ToListAsync();

Observação

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

SQL dinâmico e parâmetros

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

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

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

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

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

Se você decidiu que deseja construir dinamicamente seu SQL, terá que usar FromSqlRaw, 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 = await context.Blogs
    .FromSqlRaw($"SELECT * FROM [Blogs] WHERE {columnName} = @columnValue", columnValue)
    .ToListAsync();

No código acima, o nome da coluna é inserido diretamente no SQL, usando interpolação de cadeia de caracteres C#. É sua responsabilidade certificar-se de que este valor de corda é seguro, higienizando-o se vier de uma origem insegura; isso significa detetar caracteres especiais, como ponto-e-vírgula, comentários e outras construções SQL, e escapar deles corretamente ou rejeitar essas entradas.

Por outro lado, o valor da coluna é enviado através de um DbParametere, portanto, é seguro em face da injeção de SQL.

Advertência

Tenha muito cuidado ao usar FromSqlRawe certifique-se sempre de que os valores são de origem segura ou estão devidamente higienizados. Os ataques de injeção de SQL podem ter consequências desastrosas para o seu aplicativo.

Compondo com LINQ

Você pode compor sobre a consulta SQL inicial usando operadores LINQ; O EF Core tratará seu SQL como uma subconsulta e comporá sobre ele no banco de dados. O exemplo a seguir usa uma consulta SQL que seleciona de uma função de Table-Valued (TVF). E então compõe nele usando LINQ para fazer filtragem e classificação.

var searchTerm = "Lorem ipsum";

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

A consulta acima 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 com qualquer outra consulta LINQ:

var searchTerm = "Lorem ipsum";

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

Compor com LINQ requer que sua consulta SQL seja compostável, já que o EF Core tratará o SQL fornecido como uma subconsulta. As consultas SQL compostas geralmente começam com a palavra-chave SELECT e não podem conter recursos SQL que não são válidos em uma subconsulta, como:

  • Um ponto-e-vírgula no final
  • No SQL Server, uma dica de nível de consulta no final (por exemplo, OPTION (HASH JOIN))
  • No SQL Server, uma cláusula ORDER BY que não é usada com OFFSET 0 OU 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 compor sobre um procedimento armazenado.

Rastreamento 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 projeta tipos de entidade, os resultados são rastreados por padrão.

O exemplo a seguir usa uma consulta SQL que seleciona de uma função de Table-Valued (TVF) e, em seguida, desabilita o controle de alterações com a chamada para AsNoTracking:

var searchTerm = "Lorem ipsum";

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

Consultando tipos escalares (não entidade)

Observação

Este 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 entidades via SQL, sem a necessidade de deslizar 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 = await context.Database
    .SqlQuery<int>($"SELECT [BlogId] FROM [Blogs]")
    .ToListAsync();

Você também pode compor operadores LINQ sobre sua consulta SQL. No entanto, como o seu SQL se torna uma subconsulta cuja coluna de saída precisa ser referenciada pelos elementos adicionais do SQL introduzidos pelo Entity Framework, deve atribuir o nome Valueà coluna de saída. Por exemplo, a consulta a seguir retorna os IDs que estão acima da média de ID:

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

SqlQuery pode ser usado com qualquer tipo de escalar suportado pelo seu provedor de banco de dados. Caso queira usar um tipo não suportado pelo seu provedor de banco de dados, pode usar configuração de pré-convenção para definir uma conversão de valor para esse tipo.

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

Executando SQL sem consulta

Em alguns cenários, pode ser necessário executar SQL que não retorna dados, normalmente para modificar dados no banco de dados ou chamar um procedimento armazenado que não retorna nenhum conjunto de resultados. Isto pode ser feito através 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 a injeção de SQL usando parametrização segura, assim como FromSql, e ExecuteSqlRaw permite a construção dinâmica de consultas SQL, assim como FromSqlRaw 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 tornou possível expressar operações eficientes de atualização em massa via LINQ. Recomenda-se 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 de coluna no conjunto de resultados devem corresponder aos nomes de coluna para os 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 das colunas do conjunto de resultados tinham que corresponder a esses nomes de propriedade.
  • A consulta SQL não pode conter dados relacionados. No entanto, em muitos casos, você pode compor sobre a consulta usando o operador Include para retornar dados relacionados (consulte Incluindo dados relacionados).