How to Call SQL Server Stored Procedures in Entity Framework Core

Does EF Core support stored procedures?

Yes - EF Core can execute stored procedures, but it does not model them. There is no first-class "stored procedure" object in EF Core: you call them through raw-SQL APIs and you are responsible for matching parameters and mapping the result set onto a type. For a database with dozens of procedures, that adds up to a lot of repetitive, error-prone plumbing.

The built-in options (and where they stop)

EF Core gives you a few ways to run a procedure, each with trade-offs:

1. FromSql / FromSqlRaw - returns rows mapped to an entity or keyless type:

var customers = context.Customers
    .FromSqlInterpolated($"EXEC GetCustomersByCity {city}")
    .ToList();

This only works when the result columns line up with an existing entity (or a hand-configured keyless entity type). Arbitrary result shapes - the common case for reporting procedures - need a type you define and register yourself.

2. ExecuteSqlRaw / ExecuteSqlInterpolated - for procedures that do not return rows:

var rowsAffected = context.Database
    .ExecuteSqlInterpolated($"EXEC ArchiveOrders {cutoffDate}");

3. Raw ADO.NET - full control, but you write every parameter, reader and mapping by hand. It works, but it is exactly the boilerplate EF Core was meant to remove.

None of these give you output parameters, return values and multiple result sets as strongly-typed C#. You bolt those on manually, per procedure, and keep them in sync as the procedures change.

Why Scaffold-DbContext ignores stored procedures

When you reverse engineer a database with the built-in scaffolder, it generates entity types for tables and views only. Stored procedures, table-valued functions and scalar-valued functions are skipped, so the one part of your schema that is hardest to call by hand is also the part the tooling does not help with.

Generating strongly-typed stored procedure calls

The EntityFramework Reverse POCO Generator reads your stored procedures along with your tables and generates, for each procedure, a strongly-typed method on the context plus a result class with one property per output column. You call the method, pass typed parameters, and read typed results - no manual reader code:

// Representative generated usage for a procedure named GetOrdersForCustomer:
var orders = context.GetOrdersForCustomer(customerId: 42);

foreach (var row in orders)
{
    Console.WriteLine($"{row.OrderId}  {row.OrderDate:d}  {row.Total:C}");
}

The generator also handles the cases the raw APIs make awkward:

• Input, output and return values are exposed as typed parameters and a return value, not SqlParameter bags you populate by hand.
• Multiple result sets are generated as separate strongly-typed result-set classes on the return model.
• Table-valued and scalar-valued functions are generated too, alongside your tables and views.
• Regenerate when a procedure changes and the method signature and result class update with it, so the compiler catches drift.

You can also filter which procedures are generated, so a service only gets the procedures it actually uses.

Fields or properties on the result class?

By default the generated result-set members are fields. If you need them to be properties - for example so a JSON serializer, data binding or a mapper can see them - set UsePropertiesForStoredProcResultSets to true:

// false (default) generates a field:
public List<ResultSetModel1> ResultSet1;

// true generates a property:
public List<ResultSetModel1> ResultSet1 { get; set; }

It is a small thing, but it saves a manual edit every time you regenerate.

Handling tricky procedures (temp tables, dynamic SQL)

To work out a procedure's result columns without running it, the generator inspects it using SQL Server's sp_describe_first_result_set (and SET FMTONLY as a fallback). That static analysis cannot always infer a shape - typically when a procedure:

• Builds its result in a #temp table,
• Returns rows from dynamic SQL (EXEC / sp_executesql),
• Has conditional branches that only resolve at runtime, or
• Reaches across linked servers or objects the connection cannot see.

When that happens the generator emits an error comment rather than guessing. You stay in control through the ReadStoredProcReturnObjectException callback - suppress the error, filter by type, or supply the result columns yourself:

Settings.ReadStoredProcReturnObjectException = delegate(Exception ex, StoredProcedure sp)
{
    // e.g. suppress known temp-table procedures, or define the
    // result columns manually so a typed model is still generated.
};

Step-by-step

1. Install the extension from the Visual Studio Marketplace.
2. Add the template to your project and point it at your database.
3. Make sure stored procedures are included (IncludeStoredProcedures), and filter to the ones you need.
4. Save the .tt file to generate typed methods and result classes for every procedure, alongside your entities and DbContext.
5. Call them like normal C# methods and let the compiler enforce the parameter and result types.

See the project wiki for the full stored procedure settings and return-model troubleshooting.

Frequently asked questions

Does EF Core support stored procedures?

EF Core can execute stored procedures via FromSqlRaw, ExecuteSqlRaw or raw ADO.NET, but it does not model them - there is no generated, strongly-typed call. You map parameters and results yourself unless you use a generator that produces typed methods for you.

Does Scaffold-DbContext generate stored procedures?

No. The built-in Scaffold-DbContext generates entity types for tables and views only. Stored procedures and functions are not scaffolded. The Reverse POCO Generator does generate them as strongly-typed methods.

How do I get a strongly-typed result from a stored procedure in EF Core?

Either define a type and configure it as a keyless entity to use with FromSqlRaw, or generate the procedure and its result class automatically. The generated approach produces a method plus a result class with a property per column, so there is no manual mapping to maintain.

Can a stored procedure return multiple result sets?

Yes, and they are awkward to read with the built-in APIs. The Reverse POCO Generator exposes each result set as its own strongly-typed result-set class on the procedure's return model.

Why does my stored procedure fail to generate a return model?

SQL Server cannot statically describe the result columns of procedures that use temp tables, dynamic SQL, conditional branches or linked servers. Use the ReadStoredProcReturnObjectException callback to suppress the error or supply the result columns manually.