Calling Stored Procedures in Entity Framework and Choosing the Right ORM Tool

FromSqlRaw, ExecuteSqlRaw, output params, Dapper vs EF Core, raw SQL, ORM comparison table

Entity Framework Core is the default ORM for .NET — but it's not the only option, and it's not always the right one.

This guide covers how to call stored procedures from EF Core (including output parameters), then compares the main ORM tools so you can choose the right one for your scenario.

---

Why Use Stored Procedures?

Stored procedures are pre-compiled SQL that runs server-side.

Use them when:

• Complex business logic is already written in SQL by your DBA
• Performance-critical queries benefit from a fixed execution plan
• Security — stored procedures restrict direct table access
• You're integrating with a legacy system with existing procedures

---

Calling Stored Procedures in EF Core

Method 1: FromSqlRaw — returns entities

Use when the stored procedure returns rows that map to an entity.

var orders = await dbContext.Orders
    .FromSqlRaw("EXEC GetOrdersByCustomer @CustomerId = {0}", customerId)
    .ToListAsync();

With explicit SqlParameter for safer parameterisation:

var param = new SqlParameter("@CustomerId", customerId);

var orders = await dbContext.Orders
    .FromSqlRaw("EXEC GetOrdersByCustomer @CustomerId", param)
    .ToListAsync();

Method 2: SqlQueryRaw — returns any type (.NET 7+)

Use when the stored procedure returns columns that don't map to an existing entity.

var summaries = await dbContext.Database
    .SqlQueryRaw<OrderSummaryDto>(
        "EXEC GetOrderSummaries @From, @To",
        new SqlParameter("@From", fromDate),
        new SqlParameter("@To", toDate))
    .ToListAsync();

Method 3: ExecuteSqlRawAsync — no return value

Use for INSERT, UPDATE, DELETE procedures that don't return rows.

await dbContext.Database.ExecuteSqlRawAsync(
    "EXEC ArchiveOldOrders @CutoffDate",
    new SqlParameter("@CutoffDate", cutoffDate));

---

Output Parameters

Output parameters require explicit SqlParameter setup.

var orderIdParam = new SqlParameter
{
    ParameterName = "@OrderId",
    SqlDbType = SqlDbType.UniqueIdentifier,
    Direction = ParameterDirection.Output
};

var statusParam = new SqlParameter
{
    ParameterName = "@Status",
    SqlDbType = SqlDbType.NVarChar,
    Size = 50,
    Direction = ParameterDirection.Output
};

await dbContext.Database.ExecuteSqlRawAsync(
    "EXEC CreateOrder @CustomerId, @Product, @OrderId OUTPUT, @Status OUTPUT",
    new SqlParameter("@CustomerId", customerId),
    new SqlParameter("@Product", product),
    orderIdParam,
    statusParam);

var newOrderId = (Guid)orderIdParam.Value;
var status = (string)statusParam.Value;

---

ORM Comparison — Choosing the Right Tool

Entity Framework Core

The full-featured ORM for .NET. Abstracts SQL for most operations.

var orders = await db.Orders
    .Where(o => o.CustomerId == id && o.Status == OrderStatus.Active)
    .Include(o => o.Lines)
    .OrderByDescending(o => o.CreatedAt)
    .Take(50)
    .AsNoTracking()
    .ToListAsync();

Use when: CRUD operations dominate, LINQ is preferred, rapid development matters, migrations are needed.

Avoid when: you need fine-grained SQL control or maximum performance on complex queries.

Dapper

A micro-ORM by the Stack Overflow team. Maps raw SQL results to objects.

using var connection = new SqlConnection(connectionString);

var orders = await connection.QueryAsync<Order>(
    "SELECT * FROM Orders WHERE CustomerId = @CustomerId AND Status = @Status",
    new { CustomerId = id, Status = "Active" });

Use when: maximum performance, full SQL control, simple mapping, or legacy databases where EF migrations don't fit.

Avoid when: you want LINQ queries, change tracking, or automatic migrations.

NHibernate

A mature, feature-rich ORM with a longer history than EF Core.

Use when: working with legacy enterprise systems that already use NHibernate, or when you need features like second-level caching out of the box.

Raw ADO.NET

No ORM — direct SQL via SqlConnection, SqlCommand, SqlDataReader.

using var conn = new SqlConnection(connectionString);
using var cmd = new SqlCommand("SELECT * FROM Orders WHERE Id = @Id", conn);
cmd.Parameters.AddWithValue("@Id", orderId);

await conn.OpenAsync();
using var reader = await cmd.ExecuteReaderAsync();

while (await reader.ReadAsync())
{
    // Map manually
}

Use when: absolute maximum performance, complex multi-resultset stored procedures, or bulk operations where EF Core overhead is unacceptable.

---

Side-by-Side Comparison

	EF Core	Dapper	NHibernate	ADO.NET
LINQ queries	✅	❌	✅	❌
Migrations	✅	❌	✅	❌
Change tracking	✅	❌	✅	❌
Raw SQL	✅	✅	✅	✅
Performance	Good	Excellent	Good	Maximum
Learning curve	Medium	Low	High	Low
Best for	General purpose	Performance	Legacy	Ultra-perf

---

Interview-Ready Summary

• Use FromSqlRaw for stored procedures returning entities
• Use SqlQueryRaw for stored procedures returning custom shapes (.NET 7+)
• Use ExecuteSqlRawAsync for stored procedures with no return value
• Output parameters require SqlParameter with ParameterDirection.Output
• EF Core = full ORM, LINQ, migrations — best for most applications
• Dapper = micro-ORM, raw SQL, maximum speed — best for complex queries
• ADO.NET = maximum control and performance, most verbose

A strong interview answer:

"EF Core supports stored procedures via FromSqlRaw for returning entities, SqlQueryRaw for custom types, and ExecuteSqlRawAsync for non-query procedures. For ORM choice, EF Core is ideal for CRUD-heavy applications with migrations, while Dapper is preferred when you need raw SQL performance without the overhead of a full ORM. ADO.NET is the fallback for maximum control."