Working with Raw SQL and Stored Procedures in EF Core

LINQ covers 95 % of data access needs, but that last 5 %—legacy stored procedures, tricky window functions, mass updates—still matters. EF Core lets you drop to SQL without giving up the unit-of-work pattern or connection pooling.

Real-Life Analogy: The Red Telephone

In a modern office you normally chat via Slack (LINQ). For critical incidents you pick up the red hotline straight to the data team (raw SQL). It bypasses layers but is logged, secured, and used only when necessary.

Reading Data with Raw SQL

var dtos = await ctx.ProductSummaries
                   .FromSqlInterpolated($@"
                       SELECT Id, Name, Price
                       FROM Products
                       WHERE Price > {minPrice}")
                   .AsNoTracking()
                   .ToListAsync();

• Interpolation produces @p0 parameters—immune to SQL injection.
• Target can be a mapped entity or a Keyless Entity Type (modelBuilder.Entity<ProductSummary>().HasNoKey()).

Keyless Entity Types

Perfect for views or multi-column primary keys you don’t edit. They skip change tracker even without AsNoTracking().

Executing Stored Procedures

// Result set to entity graph
var orders = await ctx.Orders
                      .FromSqlRaw("EXEC dbo.GetRecentOrders @p0", days)
                      .ToListAsync();

Output Parameters & Scalar Results

var totalParam = new SqlParameter
{
    ParameterName = "@total",
    SqlDbType = SqlDbType.Decimal,
    Direction = ParameterDirection.Output,
    Precision = 18,
    Scale = 2
};

await ctx.Database.ExecuteSqlRawAsync(
    "EXEC dbo.CalcTotalSales @year, @total OUTPUT",
    new SqlParameter("@year", 2025), totalParam);

decimal totalSales = (decimal)totalParam.Value;

Multiple Result Sets

EF Core 8 adds context.Database.SqlQuery<T>() that streams multiple result sets with NextResultAsync(), keeping type safety.

Raw SQL Inside Transactions

await using var tx = await ctx.Database.BeginTransactionAsync();
await ctx.Database.ExecuteSqlAsync("UPDATE Stock SET Qty = Qty - 1 WHERE Id = @p0", sku);
order.Status = OrderStatus.Paid;
await ctx.SaveChangesAsync();
await tx.CommitAsync();

Unit of work remains intact—commits or rolls back as one.

Migrations & Stored Procedures

migrationBuilder.Sql(@"
    CREATE PROCEDURE dbo.GetRecentOrders
    @days INT
AS
BEGIN
    SELECT * FROM Orders WHERE DATEDIFF(day, CreatedAt, GETDATE()) < @days
END");

Store SQL inside migrations to keep schema evolution traceable.

Safety Checklist

• Parameterise every variable—never string-concatenate.
• Wrap raw SQL calls in repository or gateway classes; keep them discoverable.
• Add integration tests that fail when the procedure signature drifts.

Final Thoughts

Raw SQL is a power tool—perfect for bulk operations, reporting joins, and enterprise procs. Use it sparingly, wrap it cleanly, and EF Core will play nicely with both worlds.