Stored Procedures with Entity Framework Core in .NET

Introduction

LINQ is convenient, but it is not always the right tool. Complex filtering logic, multi-table aggregations, and performance-sensitive queries can become unreadable or impossible to express cleanly in LINQ. Stored procedures solve this - the SQL lives in the database, it is reusable across applications, and it executes faster than the equivalent dynamically generated query.

The question is how to manage stored procedures properly in a Code First EF Core project. You do not want to run raw SQL scripts manually against every environment. You want them version-controlled, applied automatically with migrations, and called safely from your API without opening the door to SQL injection.

This post covers the full workflow: creating stored procedures inside EF Core migrations, calling them from Minimal API endpoints using FromSqlRaw, FromSqlInterpolated, and ExecuteSqlInterpolatedAsync, and understanding when to reach for each method.

🎬 Watch the full video here:

The Starting Point

The demo project is a Minimal API with two entities - Author and Book:

public class Author
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Country { get; set; }
}

public class Book
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Category { get; set; } = string.Empty;
    public Author Author { get; set; }
    public int AuthorId { get; set; }
}

Four endpoints exist initially, all written with LINQ. The goal is to replace them with stored procedure equivalents, with each procedure created and managed entirely through EF Core migrations.

What Are Stored Procedures and When to Use Them

A stored procedure is a named SQL statement stored inside the database itself. It can be called by any application that has access to that database, and the database engine can cache its execution plan for faster repeated execution.

For simple CRUD operations, LINQ is fine and Code First keeps things clean. Stored procedures make sense when:

• The SQL is too complex or verbose to express cleanly in LINQ
• The query cannot be expressed in LINQ at all
• The same logic needs to be shared across multiple applications or services
• Raw query performance matters and you want the database engine to optimize the plan

In this demo, the operations are intentionally simple - the point is demonstrating the pattern, not justifying every stored procedure.

🏗️ Creating Stored Procedures in EF Core Migrations

The right way to manage stored procedures in Code First is through blank migrations. You add an empty migration, write the CREATE PROCEDURE SQL in the Up method, and write the DROP PROCEDURE SQL in the Down method. This keeps every procedure version-controlled and applied automatically with dotnet ef database update.

Creating a Blank Migration

dotnet ef migrations add AddedGetBooks \
  --startup-project StoredProcedures.API \
  --project StoredProcedures.Infrastructure

• --startup-project points to where your API lives
• --project points to where your DbContext and migrations folder live
• The generated migration will have empty Up and Down methods ready for your SQL

Writing the Migration

public partial class AddedGetBooks : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        var createProcedure =
            "CREATE PROCEDURE [dbo].[GetBooks]\n" +
            "AS\n" +
            "BEGIN\n" +
            "    SET NOCOUNT ON;\n" +
            "    SELECT * FROM Books;\n" +
            "END";

        migrationBuilder.Sql(createProcedure);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        var dropStatement = "DROP PROCEDURE [dbo].[GetBooks]";
        migrationBuilder.Sql(dropStatement);
    }
}

Key points:

• migrationBuilder.Sql() executes any raw SQL string during migration
• SET NOCOUNT ON suppresses row-count messages - always include this in stored procedures
• The Down method must drop the procedure so rollbacks work cleanly
• Apply with dotnet ef database update --startup-project ... --project ...

A Procedure with Parameters

public partial class AddedGetBooksByCategoryAndAuthorId : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        var sql =
            "CREATE PROCEDURE GetBooksByCategoryAndAuthorId\n" +
            "    @category NVARCHAR(100),\n" +
            "    @authorId INT\n" +
            "AS\n" +
            "BEGIN\n" +
            "    SET NOCOUNT ON;\n" +
            "    SELECT * FROM Books\n" +
            "    WHERE Category = @category AND AuthorId = @authorId;\n" +
            "END";

        migrationBuilder.Sql(sql);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        var sql = "DROP PROCEDURE GetBooksByCategoryAndAuthorId";
        migrationBuilder.Sql(sql);
    }
}

• Parameters are declared with @paramName TYPE after the procedure name
• The WHERE clause uses those parameters directly
• The pattern for Down is identical - just drop the procedure by name

⚡ Calling Stored Procedures from Minimal API Endpoints

There are three EF Core methods for executing stored procedures, and choosing the right one depends on whether you are querying data or executing a command.

FromSqlRaw - No Parameters

Use FromSqlRaw when the stored procedure takes no parameters and you need to return entity results:

app.MapGet("/stored-procedure-get-books", async (DemoDbContext dbContext) =>
{
    var books = await dbContext.Books
        .FromSqlRaw("EXEC GetBooks")
        .ToListAsync();

    return books;
});

• FromSqlRaw executes a raw SQL string and maps results to the entity type
• Only use this when there are no user-supplied parameters - never concatenate user input into a raw SQL string

FromSqlInterpolated - Parameterized Queries

Use FromSqlInterpolated when you need to pass parameters and return entity results. EF Core converts the interpolated string into a parameterized query, which prevents SQL injection:

app.MapGet("/stored-procedure-get-books-by-category-and-authorid",
    async (string category, int authorId, DemoDbContext dbContext) =>
    {
        var books = await dbContext.Books
            .FromSqlInterpolated(
                $"EXEC GetBooksByCategoryAndAuthorId @category={category}, @authorId={authorId}")
            .ToListAsync();

        return books;
    });

• The $ prefix makes this an interpolated string, but EF Core does not concatenate the values directly
• Each interpolated value is converted to a proper SQL parameter under the hood
• Results are mapped back to Book entities automatically

ExecuteSqlInterpolatedAsync - Commands (No Return Value)

Use ExecuteSqlInterpolatedAsync for INSERT, UPDATE, and DELETE procedures that do not return rows:

app.MapPost("/stored-procedure-books",
    async (CreateBookRequest createBookRequest, DemoDbContext dbContext) =>
    {
        await dbContext.Database.ExecuteSqlInterpolatedAsync(
            $"EXEC CreateBook @name={createBookRequest.Name}, @category={createBookRequest.Category}, @authorId={createBookRequest.AuthorId}");

        return Results.Ok();
    });

app.MapPut("/stored-procedure-books",
    async (UpdateBookRequest updateBookRequest, DemoDbContext dbContext) =>
    {
        await dbContext.Database.ExecuteSqlInterpolatedAsync(
            $"EXEC UpdateBook @bookId={updateBookRequest.BookId}, @name={updateBookRequest.Name}, @category={updateBookRequest.Category}, @authorId={updateBookRequest.AuthorId}");

        return Results.Ok();
    });

• Called on dbContext.Database, not on a DbSet
• Async by default - always await it
• Same SQL injection protection as FromSqlInterpolated - values are parameterized automatically

Comparison: Which Method to Use

The Full BooksStoredProcedureModule

public static class BooksStoredProcedureModule
{
    public static void AddBooksStoredProcedureEndpoints(this IEndpointRouteBuilder app)
    {
        app.MapPost("/stored-procedure-books",
            async (CreateBookRequest createBookRequest, DemoDbContext dbContext) =>
            {
                await dbContext.Database.ExecuteSqlInterpolatedAsync(
                    $"EXEC CreateBook @name={createBookRequest.Name}, @category={createBookRequest.Category}, @authorId={createBookRequest.AuthorId}");
                return Results.Ok();
            });

        app.MapPut("/stored-procedure-books",
            async (UpdateBookRequest updateBookRequest, DemoDbContext dbContext) =>
            {
                await dbContext.Database.ExecuteSqlInterpolatedAsync(
                    $"EXEC UpdateBook @bookId={updateBookRequest.BookId}, @name={updateBookRequest.Name}, @category={updateBookRequest.Category}, @authorId={updateBookRequest.AuthorId}");
                return Results.Ok();
            });

        app.MapGet("/stored-procedure-get-books", async (DemoDbContext dbContext) =>
        {
            var books = await dbContext.Books
                .FromSqlRaw("EXEC GetBooks")
                .ToListAsync();
            return books;
        });

        app.MapGet("/stored-procedure-get-books-by-category-and-authorid",
            async (string category, int authorId, DemoDbContext dbContext) =>
            {
                var books = await dbContext.Books
                    .FromSqlInterpolated(
                        $"EXEC GetBooksByCategoryAndAuthorId @category={category}, @authorId={authorId}")
                    .ToListAsync();
                return books;
            });
    }
}

Migrations Keep Everything in Sync

One major benefit of managing stored procedures through migrations is that dropping and recreating the database from scratch works perfectly. Running dotnet ef database update replays all migrations in order - tables first, then each stored procedure gets created automatically.

No manual SQL scripts, no environment drift, no missing procedures in staging. The database state is fully reproducible from migration history alone.

Key Takeaways

• Create stored procedures inside blank EF Core migrations using migrationBuilder.Sql() - this keeps them version-controlled and applied automatically.
• Always write a Down method that drops the procedure so rollbacks work cleanly.
• Use FromSqlRaw only for no-parameter queries - never concatenate user input into a raw SQL string.
• Use FromSqlInterpolated for parameterized queries that return entities - EF Core converts interpolated values to proper SQL parameters automatically.
• Use ExecuteSqlInterpolatedAsync on dbContext.Database for INSERT, UPDATE, and DELETE procedures that do not return rows.
• Stored procedures are worth reaching for when the SQL is too complex for LINQ, needs to be shared across applications, or when raw execution performance matters.