Consultas SQL Directas en EF Core — Cuándo y Cómo Usarlas

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

// SQL directo para la consulta base, luego se compone con LINQ
var products = await _db.Products
    .FromSqlRaw("SELECT * FROM Products WHERE CategoryId = {0}", categoryId)
    .Where(p => p.Price > 50)        // Agrega cláusula WHERE
    .OrderBy(p => p.Name)            // Agrega ORDER BY
    .Include(p => p.Category)        // Agrega 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

// Esto parece interpolación de cadenas, pero EF Core lo convierte en 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 ejecutado (sin posibilidad de inyección):
SELECT * FROM Products WHERE CategoryId = @p0 AND Name LIKE @p1
-- @p0 = 5, @p1 = 'Widget%'

// Función de ventana para productos clasificados por precio dentro de la categoría
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 el resultado
public record RankedProduct(int Id, string Name, decimal Price, int CategoryId, int PriceRank);
 
// Usa SQL directo con Dapper o ADO.NET para resultados que no son entidades
// O agrega PriceRank como propiedad [NotMapped] y usa FromSqlRaw

// EF Core 7+ — consulta a cualquier tipo, no solo tipos de entidad
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 — sentencia 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);
");

// Actualización masiva con ExecuteSqlInterpolated (siempre 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($"Se actualizaron {rowsAffected} productos");
 
// O con ExecuteSqlRaw y 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));

// Procedimiento almacenado que devuelve entidades
var products = await _db.Products
    .FromSqlRaw("EXEC dbo.GetProductsByCategory @CategoryId = {0}", categoryId)
    .ToListAsync();
 
// Procedimiento almacenado con parámetro de salida
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($"Se procesaron {totalCount} órdenes");

var products = await _db.Products
    .FromSqlInterpolated($"SELECT * FROM Products WHERE Price > {minPrice}")
    .AsNoTracking()  // Sin seguimiento de cambios — mejor rendimiento para lecturas
    .ToListAsync();

// Registrar consultas en la consola en desarrollo
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 });
 
// Obtener el SQL sin ejecutar
var sql = query.ToQueryString();
Console.WriteLine(sql);

// Consulta JSONB de PostgreSQL — no puede expresarse en 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();