EF Core Raw SQL Queries — When and How to Use Them

// SAFE — parameterized query
var categoryId = 5;
var products = await _db.Products
    .FromSqlRaw("SELECT * FROM Products WHERE CategoryId = {0}", categoryId)
    .ToListAsync();
 
// SAFE — named parameters (SQL Server)
var products = await _db.Products
    .FromSqlRaw("SELECT * FROM Products WHERE CategoryId = @categoryId",
        new SqlParameter("@categoryId", categoryId))
    .ToListAsync();

// Raw SQL for the base query, then compose with LINQ
var products = await _db.Products
    .FromSqlRaw("SELECT * FROM Products WHERE CategoryId = {0}", categoryId)
    .Where(p => p.Price > 50)        // Adds WHERE clause
    .OrderBy(p => p.Name)            // Adds ORDER BY
    .Include(p => p.Category)        // Adds 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

// This looks like string interpolation, but EF Core converts it to parameters
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();

-- Actual SQL executed (no injection possible):
SELECT * FROM Products WHERE CategoryId = @p0 AND Name LIKE @p1
-- @p0 = 5, @p1 = 'Widget%'

// Window function for ranked products by price within category
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 for the result
public record RankedProduct(int Id, string Name, decimal Price, int CategoryId, int PriceRank);
 
// Use raw SQL with Dapper or ADO.NET for non-entity results
// Or add PriceRank as a [NotMapped] property and use FromSqlRaw

// EF Core 7+ — query to any type, not just entity types
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 — MERGE statement
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);
");

// Bulk update with ExecuteSqlInterpolated (always safe)
int categoryId = 5;
decimal discountFactor = 0.9m;
 
int rowsAffected = await _db.Database.ExecuteSqlInterpolatedAsync(
    $"UPDATE Products SET Price = Price * {discountFactor} WHERE CategoryId = {categoryId}");
 
Console.WriteLine($"Updated {rowsAffected} products");
 
// Or with ExecuteSqlRaw and explicit parameters
await _db.Database.ExecuteSqlRawAsync(
    "UPDATE Products SET Price = Price * @discount WHERE CategoryId = @catId",
    new SqlParameter("@discount", discountFactor),
    new SqlParameter("@catId", categoryId));

// Stored procedure that returns entities
var products = await _db.Products
    .FromSqlRaw("EXEC dbo.GetProductsByCategory @CategoryId = {0}", categoryId)
    .ToListAsync();
 
// Stored procedure with output parameter
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($"Processed {totalCount} orders");

var products = await _db.Products
    .FromSqlInterpolated($"SELECT * FROM Products WHERE Price > {minPrice}")
    .AsNoTracking()  // No change tracking — better performance for reads
    .ToListAsync();

// Log queries to console in development
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 });
 
// Get the SQL without executing
var sql = query.ToQueryString();
Console.WriteLine(sql);

// PostgreSQL JSONB query — can't be expressed in portable LINQ
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();