In this article we going to learn how to use stored procedures using Entity Framework Core — with input parameters, output parameters, and how to map the results back to your C# models.
A lot of developers think EF Core and stored procedures don't mix well. That's not true. EF Core has solid support for stored procedures and raw SQL in general. You might prefer LINQ queries for simple CRUD operations, but for complex reporting queries, legacy databases, or performance-sensitive operations, stored procedures are often the better choice. And you don't have to switch to Dapper or ADO.NET just to use them.
Let me show you exactly how to call stored procedures from EF Core in all the common scenarios.
This tutorial shows how to:
- Call a stored procedure that returns rows and map them to a model
- Pass input parameters to a stored procedure safely
- Use output parameters and return values
- Call stored procedures that don't return data (INSERT, UPDATE, DELETE)
- Execute raw SQL queries when needed
- Avoid SQL injection when working with raw SQL in EF Core
Why Use Stored Procedures with EF Core
EF Core's LINQ queries are great for most operations. But there are real situations where a stored procedure makes more sense :
- Complex queries — joins across many tables with grouping, windowing functions, and complex filtering are sometimes cleaner in SQL than translated LINQ
- Performance — SQL Server caches execution plans for stored procedures. For critical high-traffic queries this matters.
- Legacy databases — if you're working with an existing database that already has stored procedures, you need to call them from EF Core
- Business logic in DB — some teams keep business logic in the database. EF Core needs to work with that.
- Pagination and reporting — stored procedures with pagination parameters are common in enterprise apps
Step 1 : Create the Sample Database and Stored Procedures
Let's create a simple Orders table and a few stored procedures we'll call from EF Core.
CREATE TABLE Orders (
OrderId INT IDENTITY(1,1) PRIMARY KEY,
CustomerId INT NOT NULL,
OrderDate DATE NOT NULL,
TotalAmount DECIMAL(10,2) NOT NULL,
Status NVARCHAR(20) NOT NULL DEFAULT 'Pending'
);
-- Insert some sample data
INSERT INTO Orders (CustomerId, OrderDate, TotalAmount, Status) VALUES
(1, '2024-01-10', 1500.00, 'Completed'),
(1, '2024-02-15', 2200.00, 'Completed'),
(2, '2024-01-20', 800.00, 'Pending'),
(3, '2024-03-05', 3100.00, 'Completed'),
(4, '2024-03-10', 500.00, 'Cancelled');
Now create the stored procedures we'll be calling :
-- SP 1 : Get all orders with optional filter
CREATE PROCEDURE sp_GetOrders
@Status NVARCHAR(20) = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT OrderId, CustomerId, OrderDate, TotalAmount, Status
FROM Orders
WHERE (@Status IS NULL OR Status = @Status)
ORDER BY OrderDate DESC;
END
-- SP 2 : Get orders by customer with total
CREATE PROCEDURE sp_GetOrdersByCustomer
@CustomerId INT,
@TotalOrders INT OUTPUT,
@TotalSpent DECIMAL(10,2) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT OrderId, CustomerId, OrderDate, TotalAmount, Status
FROM Orders
WHERE CustomerId = @CustomerId
ORDER BY OrderDate DESC;
SELECT
@TotalOrders = COUNT(*),
@TotalSpent = SUM(TotalAmount)
FROM Orders
WHERE CustomerId = @CustomerId;
END
-- SP 3 : Update order status
CREATE PROCEDURE sp_UpdateOrderStatus
@OrderId INT,
@NewStatus NVARCHAR(20)
AS
BEGIN
SET NOCOUNT ON;
UPDATE Orders SET Status = @NewStatus WHERE OrderId = @OrderId;
END
-- SP 4 : Get order count (returns scalar value)
CREATE PROCEDURE sp_GetOrderCount
@Status NVARCHAR(20)
AS
BEGIN
SET NOCOUNT ON;
SELECT COUNT(*) FROM Orders WHERE Status = @Status;
END
Step 2 : Set Up EF Core DbContext
Create your Order model and DbContext :
public class Order
{
public int OrderId { get; set; }
public int CustomerId { get; set; }
public DateTime OrderDate { get; set; }
public decimal TotalAmount { get; set; }
public string Status { get; set; } = string.Empty;
}
public class AppDbContext : DbContext
{
public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
public DbSet<Order> Orders { get; set; }
}
Register in Program.cs :
builder.Services.AddDbContext<AppDbContext>(options =>
options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));
Step 3 : Call a Stored Procedure That Returns Rows
The most common scenario — calling a stored procedure and mapping the results to a list of objects.
Use FromSqlRaw() or FromSqlInterpolated() on a DbSet :
public async Task<List<Order>> GetOrdersAsync(string? status = null)
{
// Using FromSqlInterpolated — safe, handles parameterization automatically
var orders = await _context.Orders
.FromSqlInterpolated($"EXEC sp_GetOrders @Status = {status}")
.ToListAsync();
return orders;
}
FromSqlInterpolated takes an interpolated string and automatically creates parameterized SQL. The {status} placeholder becomes a proper SQL parameter — not string concatenation. This is safe from SQL injection.
If you prefer FromSqlRaw, you need to pass parameters explicitly :
public async Task<List<Order>> GetOrdersAsync(string? status = null)
{
var statusParam = new SqlParameter("@Status", (object?)status ?? DBNull.Value);
var orders = await _context.Orders
.FromSqlRaw("EXEC sp_GetOrders @Status", statusParam)
.ToListAsync();
return orders;
}
The (object?)status ?? DBNull.Value handles null correctly. If you pass null directly as a SqlParameter value, SQL Server doesn't receive NULL — it just ignores the parameter. Using DBNull.Value sends an actual SQL NULL.
One thing to know about FromSqlRaw and FromSqlInterpolated — the columns returned by the stored procedure must match the properties of your entity. EF Core maps by column name. If your stored procedure returns a column named OrderId, it maps to the OrderId property on the Order class. If names don't match, the property gets its default value (0 for int, null for string).
Step 4 : Map Results to a Custom Class (Not a DbSet Entity)
Sometimes the stored procedure returns data that doesn't match any existing entity. Maybe it's a report query that joins multiple tables and returns a custom result set.
Create a keyless entity for this :
public class OrderSummary
{
public int CustomerId { get; set; }
public int TotalOrders { get; set; }
public decimal TotalSpent { get; set; }
public string LastStatus { get; set; } = string.Empty;
}
Register it in DbContext as a keyless entity — no primary key, not tracked :
public class AppDbContext : DbContext
{
public DbSet<Order> Orders { get; set; }
public DbSet<OrderSummary> OrderSummaries { get; set; } // Keyless
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<OrderSummary>().HasNoKey();
}
}
Now call the stored procedure and map to OrderSummary :
public async Task<List<OrderSummary>> GetOrderSummaryAsync()
{
return await _context.OrderSummaries
.FromSqlRaw("EXEC sp_GetOrderSummary")
.ToListAsync();
}
Keyless entities are not tracked by EF Core's change tracker, which is the correct behaviour for read-only report data.
Step 5 : Call Stored Procedure with Output Parameters
Output parameters are trickier with EF Core because FromSqlRaw doesn't expose them directly. For output parameters, use Database.ExecuteSqlRaw() with SqlParameter objects where you set the direction.
public async Task<(List<Order> Orders, int TotalOrders, decimal TotalSpent)>
GetOrdersByCustomerAsync(int customerId)
{
var customerIdParam = new SqlParameter("@CustomerId", customerId);
var totalOrdersParam = new SqlParameter
{
ParameterName = "@TotalOrders",
SqlDbType = System.Data.SqlDbType.Int,
Direction = System.Data.ParameterDirection.Output
};
var totalSpentParam = new SqlParameter
{
ParameterName = "@TotalSpent",
SqlDbType = System.Data.SqlDbType.Decimal,
Precision = 10,
Scale = 2,
Direction = System.Data.ParameterDirection.Output
};
var orders = await _context.Orders
.FromSqlRaw(
"EXEC sp_GetOrdersByCustomer @CustomerId, @TotalOrders OUTPUT, @TotalSpent OUTPUT",
customerIdParam, totalOrdersParam, totalSpentParam
)
.ToListAsync();
var totalOrders = (int)(totalOrdersParam.Value ?? 0);
var totalSpent = (decimal)(totalSpentParam.Value ?? 0m);
return (orders, totalOrders, totalSpent);
}
Key things here — set Direction = ParameterDirection.Output for output parameters. After ToListAsync() runs, the output parameter values are populated in totalOrdersParam.Value and totalSpentParam.Value. Read them after the query completes.
The cast from object to int and decimal is needed because SqlParameter.Value returns object.
Step 6 : Call Stored Procedures That Don't Return Rows
For stored procedures that only INSERT, UPDATE, or DELETE — use ExecuteSqlInterpolatedAsync() or ExecuteSqlRawAsync(). These return the number of rows affected, not a result set.
public async Task UpdateOrderStatusAsync(int orderId, string newStatus)
{
await _context.Database
.ExecuteSqlInterpolatedAsync(
$"EXEC sp_UpdateOrderStatus @OrderId = {orderId}, @NewStatus = {newStatus}"
);
}
Or with SqlParameters for more control :
public async Task UpdateOrderStatusAsync(int orderId, string newStatus)
{
var orderIdParam = new SqlParameter("@OrderId", orderId);
var statusParam = new SqlParameter("@NewStatus", newStatus);
await _context.Database
.ExecuteSqlRawAsync(
"EXEC sp_UpdateOrderStatus @OrderId, @NewStatus",
orderIdParam, statusParam
);
}
Both approaches work. ExecuteSqlInterpolatedAsync is cleaner to read. ExecuteSqlRawAsync with explicit SqlParameters gives you more control over data types, especially for nullable parameters.
Step 7 : Get a Scalar Value from a Stored Procedure
Sometimes a stored procedure returns a single value — a count, a total, a status code. EF Core doesn't have a built-in ExecuteScalar equivalent, but you can handle this with ADO.NET through the DbContext connection :
public async Task<int> GetOrderCountAsync(string status)
{
var connection = _context.Database.GetDbConnection();
await connection.OpenAsync();
using var command = connection.CreateCommand();
command.CommandText = "sp_GetOrderCount";
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@Status", status));
var result = await command.ExecuteScalarAsync();
return result != null ? Convert.ToInt32(result) : 0;
}
This uses the underlying ADO.NET connection through EF Core's GetDbConnection(). Since you're using the same connection, it participates in the same transaction if one is active.
Close the connection manually if EF Core didn't open it — or better, check the connection state first :
var connection = _context.Database.GetDbConnection();
if (connection.State != System.Data.ConnectionState.Open)
await connection.OpenAsync();
Step 8 : Call Stored Procedure Inside a Transaction
Sometimes you need multiple database operations — including stored procedure calls — to succeed or fail together.
public async Task ProcessOrderAsync(int orderId)
{
using var transaction = await _context.Database.BeginTransactionAsync();
try
{
// EF Core operation
var order = await _context.Orders.FindAsync(orderId);
if (order == null) throw new Exception("Order not found");
order.Status = "Processing";
await _context.SaveChangesAsync();
// Stored procedure call inside same transaction
await _context.Database
.ExecuteSqlInterpolatedAsync(
$"EXEC sp_UpdateOrderStatus @OrderId = {orderId}, @NewStatus = {"Processing"}"
);
await transaction.CommitAsync();
}
catch
{
await transaction.RollbackAsync();
throw;
}
}
The stored procedure call uses the same DbConnection that has the active transaction. So if anything fails, both the EF Core operation and the stored procedure call roll back together.
Common Issues and Fixes
"The required column X was not present in the results of a FromSql operation"
The stored procedure's result columns don't match the entity properties. Either the column name doesn't match the property name, or the stored procedure isn't returning all required columns. Either alias the columns in the stored procedure to match property names, or add [NotMapped] to optional properties in the entity.
Output parameter value is DBNull after execution
The stored procedure didn't set the output parameter — usually because an early RETURN or no matching rows. Check the stored procedure logic. Also make sure you set the correct SqlDbType for the output parameter — mismatch can cause silent failures.
"A second operation was started on this context before a previous operation completed"
This happens if you're using async/await incorrectly and two operations run on the same DbContext at the same time. EF Core DbContext is not thread-safe. Use one DbContext per request in ASP.NET Core (Scoped lifetime) and don't share it across threads.
FromSqlRaw with NOCOUNT OFF causes mapping issues
If your stored procedure doesn't have SET NOCOUNT ON, SQL Server sends row count messages alongside the result set. This can confuse EF Core's result reader. Always add SET NOCOUNT ON at the start of stored procedures called from EF Core.
Null parameters not working
Passing a C# null directly as a SqlParameter value doesn't send SQL NULL — it sends nothing. Use DBNull.Value for nullable parameters :
var param = new SqlParameter("@Status", (object?)status ?? DBNull.Value);
Summary
You learned how to call stored procedures from EF Core in all common scenarios. You covered :
- Calling stored procedures that return rows using
FromSqlInterpolatedandFromSqlRaw - Mapping results to existing entities and to custom keyless entities for report queries
- Passing input parameters safely without SQL injection risk
- Using output parameters with
SqlParameterandParameterDirection.Output - Calling non-returning stored procedures with
ExecuteSqlInterpolatedAsync - Getting scalar return values using ADO.NET through
GetDbConnection() - Wrapping stored procedure calls inside EF Core transactions
- Fixing common issues like null parameters, NOCOUNT, and column mapping errors
EF Core and stored procedures work together well once you know the right methods to use. FromSqlInterpolated for result sets, ExecuteSqlInterpolatedAsync for non-returning procs, and ADO.NET through GetDbConnection() for scalar values. Between these three approaches, any stored procedure call is covered.
I hope you like this article...
Happy coding! 🚀
0 Comments