Consultas SQL Brutas no EF Core — Quando e Como Usá-las

// SEGURO — consulta parametrizada
var categoryId = 5;
var products = await _db.Products
    .FromSqlRaw("SELECT * FROM Products WHERE CategoryId = {0}", categoryId)
    .ToListAsync();
 
// SEGURO — parâmetros nomeados (SQL Server)
var products = await _db.Products
    .FromSqlRaw("SELECT * FROM Products WHERE CategoryId = @categoryId",
        new SqlParameter("@categoryId", categoryId))
    .ToListAsync();

// SQL bruto para a consulta base, depois compõe com LINQ
var products = await _db.Products
    .FromSqlRaw("SELECT * FROM Products WHERE CategoryId = {0}", categoryId)
    .Where(p => p.Price > 50)        // Adiciona cláusula WHERE
    .OrderBy(p => p.Name)            // Adiciona ORDER BY
    .Include(p => p.Category)        // Adiciona JOIN
    .ToListAsync();

SELECT p.*, c.*
FROM (SELECT * FROM Products WHERE CategoryId = 5) AS p
JOIN Categories AS c ON p.CategoryId = c.Id
WHERE p.Price > 50
ORDER BY p.Name

// Isso parece interpolação de string, mas o EF Core converte em parâmetros
int categoryId = 5;
string nameFilter = "Widget%";
 
var products = await _db.Products
    .FromSqlInterpolated(
        $"SELECT * FROM Products WHERE CategoryId = {categoryId} AND Name LIKE {nameFilter}")
    .OrderBy(p => p.Price)
    .ToListAsync();

-- SQL real executado (sem possibilidade de injeção):
SELECT * FROM Products WHERE CategoryId = @p0 AND Name LIKE @p1
-- @p0 = 5, @p1 = 'Widget%'

// Função de janela para produtos classificados por preço dentro da categoria
var rankedProducts = await _db.Products
    .FromSqlRaw(@"
        SELECT
            p.*,
            ROW_NUMBER() OVER (PARTITION BY p.CategoryId ORDER BY p.Price DESC) AS PriceRank
        FROM Products p
    ")
    .ToListAsync();

// DTO para o resultado
public record RankedProduct(int Id, string Name, decimal Price, int CategoryId, int PriceRank);
 
// Use SQL bruto com Dapper ou ADO.NET para resultados que não são entidades
// Ou adicione PriceRank como propriedade [NotMapped] e use FromSqlRaw

// EF Core 7+ — consulta para qualquer tipo, não apenas tipos de entidade
var rankedProducts = await _db.Database
    .SqlQueryRaw<RankedProduct>(@"
        SELECT
            p.Id,
            p.Name,
            p.Price,
            p.CategoryId,
            CAST(ROW_NUMBER() OVER (PARTITION BY p.CategoryId ORDER BY p.Price DESC) AS int) AS PriceRank
        FROM Products p
    ")
    .ToListAsync();

var topCategories = await _db.Database
    .SqlQueryRaw<CategorySummary>(@"
        WITH ProductCounts AS (
            SELECT
                CategoryId,
                COUNT(*) AS ProductCount,
                AVG(Price) AS AvgPrice
            FROM Products
            GROUP BY CategoryId
        )
        SELECT
            c.Id,
            c.Name,
            pc.ProductCount,
            pc.AvgPrice
        FROM Categories c
        JOIN ProductCounts pc ON c.Id = pc.CategoryId
        ORDER BY pc.ProductCount DESC
    ")
    .ToListAsync();

var searchTerm = "entity framework performance";
var results = await _db.Articles
    .FromSqlInterpolated(
        $"SELECT * FROM Articles WHERE CONTAINS(Content, {searchTerm})")
    .Include(a => a.Author)
    .ToListAsync();

// SQL Server — instrução MERGE
await _db.Database.ExecuteSqlInterpolatedAsync($@"
    MERGE Products AS target
    USING (SELECT {id} AS Id, {newName} AS Name) AS source
    ON target.Id = source.Id
    WHEN MATCHED THEN UPDATE SET Name = source.Name
    WHEN NOT MATCHED THEN INSERT (Id, Name) VALUES (source.Id, source.Name);
");

// Atualização em massa com ExecuteSqlInterpolated (sempre seguro)
int categoryId = 5;
decimal discountFactor = 0.9m;
 
int rowsAffected = await _db.Database.ExecuteSqlInterpolatedAsync(
    $"UPDATE Products SET Price = Price * {discountFactor} WHERE CategoryId = {categoryId}");
 
Console.WriteLine($"Foram atualizados {rowsAffected} produtos");
 
// Ou com ExecuteSqlRaw e parâmetros explícitos
await _db.Database.ExecuteSqlRawAsync(
    "UPDATE Products SET Price = Price * @discount WHERE CategoryId = @catId",
    new SqlParameter("@discount", discountFactor),
    new SqlParameter("@catId", categoryId));

// Stored procedure que retorna entidades
var products = await _db.Products
    .FromSqlRaw("EXEC dbo.GetProductsByCategory @CategoryId = {0}", categoryId)
    .ToListAsync();
 
// Stored procedure com parâmetro de saída
var outputParam = new SqlParameter("@TotalCount", SqlDbType.Int)
{
    Direction = ParameterDirection.Output
};
 
await _db.Database.ExecuteSqlRawAsync(
    "EXEC dbo.ProcessOrders @BatchSize = {0}, @TotalCount = @TotalCount OUTPUT",
    100,
    outputParam);
 
var totalCount = (int)outputParam.Value;
Console.WriteLine($"Foram processados {totalCount} pedidos");

var products = await _db.Products
    .FromSqlInterpolated($"SELECT * FROM Products WHERE Price > {minPrice}")
    .AsNoTracking()  // Sem rastreamento de alterações — melhor desempenho para leituras
    .ToListAsync();

// Registrar consultas no console em desenvolvimento
builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString);
    if (builder.Environment.IsDevelopment())
        options.LogTo(Console.WriteLine, LogLevel.Information);
});

var query = _db.Products
    .Where(p => p.CategoryId == 5)
    .OrderBy(p => p.Price)
    .Select(p => new { p.Id, p.Name, p.Price });
 
// Obter o SQL sem executar
var sql = query.ToQueryString();
Console.WriteLine(sql);

// Consulta JSONB do PostgreSQL — não pode ser expressa em LINQ portátil
var results = await _db.Database
    .SqlQueryRaw<OrderResult>(@"
        SELECT id, data->>'customer_name' AS CustomerName,
               (data->>'total')::numeric AS Total
        FROM orders
        WHERE data @> '{""status"": ""pending""}'::jsonb
        ORDER BY created_at DESC
    ")
    .ToListAsync();