Stored procedures in Entity Framework Core: call, map, test

The Visual FoxPro system I migrated to .NET ended up with 42 T-SQL stored procedures behind an Entity Framework Core domain layer, with a data access layer that could be tested. That is not the EF Core default, and it is not what the EF Core documentation recommends as a starting point. This article is about when a procedure belongs in an EF Core application anyway, how to call one for each kind of result, and how to keep the code around it testable.

When a stored procedure earns its place

EF Core's LINQ provider writes good SQL for most reads and writes, and a query it generates is visible in one place, in C#, with the types checked. Reach for a procedure when one of these holds:

• Set-based money logic. A posting that touches thousands of rows runs as one statement in T-SQL and as a slow loop in C#.
• Several statements, one unit. Insert the journal, update balances, write the audit row: one procedure, one transaction, no half-done state if the app process dies between calls.
• Rules the database team owns. When a DBA maintains the calculation, the procedure is the contract between teams.
• Reports. Window functions, ROLLUP and running totals are plainer in SQL than in LINQ.

The migration had three of the four; as the sole engineer I was also the database team. If none applies, write LINQ. Microsoft's own note on the save-mapping feature says that supporting procedures does not mean recommending them.

Read rows: FromSql on a keyless type

A procedure that returns rows is read through FromSql. The result type can be an entity, but a report shape usually is not a table, so declare it as a keyless type and keep it out of migrations with ToView(null). The columns the procedure returns must match the property names exactly.

public sealed class StatementLine
{
    public DateOnly PostedOn { get; init; }
    public string Reference { get; init; } = "";
    public decimal Amount { get; init; }
    public decimal RunningBalance { get; init; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // A result shape, not a table: no key, and ToView(null) keeps it out of migrations
    modelBuilder.Entity<StatementLine>().HasNoKey().ToView(null);
}

var lines = await db.Set<StatementLine>()
    .FromSql($"EXECUTE dbo.Account_Statement @AccountId={accountId}, @From={from}, @To={to}")
    .AsNoTracking()
    .ToListAsync();

// Anything after FromSql becomes a subquery, and SQL Server cannot wrap EXECUTE in one.
// Filter in memory instead:
var debits = await db.Set<StatementLine>()
    .FromSql($"EXECUTE dbo.Account_Statement @AccountId={accountId}, @From={from}, @To={to}")
    .AsAsyncEnumerable()
    .Where(l => l.Amount < 0)
    .ToListAsync();

The interpolated string is not ordinary C# interpolation. EF Core turns each {value} into a DbParameter, which is why it is safe from injection and why named parameters work. FromSql arrived in EF Core 7; on EF Core 6 the same call is FromSqlInterpolated.

The second query shows the limit that catches everyone once. LINQ operators after FromSql are translated by wrapping your SQL in a subquery, and SQL Server does not allow EXECUTE inside one. AsAsyncEnumerable ends the translation, so the filter runs in memory. If the filter needs to run in the database, it becomes a procedure parameter.

The procedure itself is ordinary T-SQL. The running balance is a window function, and the date range is written so that @To is inclusive without a BETWEEN on a date that might one day become a datetime:

CREATE OR ALTER PROCEDURE dbo.Account_Statement
    @AccountId int,
    @From date,
    @To date
AS
BEGIN
    SET NOCOUNT ON;

    SELECT PostedOn,
           Reference,
           Amount,
           SUM(Amount) OVER (ORDER BY PostedOn, Id ROWS UNBOUNDED PRECEDING) AS RunningBalance
    FROM dbo.Ledger
    WHERE AccountId = @AccountId
      AND PostedOn >= @From
      AND PostedOn < DATEADD(day, 1, @To)
    ORDER BY PostedOn, Id;
END

Scalars and unmapped types: SqlQuery is for SELECT

Database.SqlQuery<T> returns a scalar, or since EF Core 8 any class with a property per column, from a SQL statement that EF Core can treat as a subquery. That makes it the right call for a SELECT or a table-valued function. For a procedure that returns one value, use an output parameter instead, as below; for a procedure that returns rows, the keyless type with FromSql above.

Writes: ExecuteSql

A procedure that changes data and returns no rows is run with ExecuteSql, which returns the rows-affected count. With SET NOCOUNT ON in the procedure, which you want for performance, that count is -1, so do not use it as a success check; raise an error inside the procedure instead and let the exception propagate.

// Returns the rows affected, or -1 when the procedure sets NOCOUNT ON
var affected = await db.Database.ExecuteSqlAsync(
    $"EXECUTE dbo.Ledger_Post @InvoiceId={invoice.Id}, @PostedBy={userId}");

Output parameters and facets

Output parameters need a SqlParameter built by hand, so the call switches to the raw overload with named placeholders. Read the value after the call completes.

using System.Data;
using Microsoft.Data.SqlClient;

var journalId = new SqlParameter("JournalId", SqlDbType.Int) { Direction = ParameterDirection.Output };

await db.Database.ExecuteSqlRawAsync(
    "EXECUTE dbo.Ledger_Post @InvoiceId, @PostedBy, @JournalId OUTPUT",
    new SqlParameter("InvoiceId", invoice.Id),
    new SqlParameter("PostedBy", userId),
    journalId);

var id = (int)journalId.Value;

The same construction fixes a quieter bug. A C# decimal is sent as decimal(18,2) unless told otherwise, so a procedure declared with decimal(19,4) silently receives a rounded value. Set the precision and scale on the parameter:

// decimal(19,4) in the procedure, so say so; the default inferred type is decimal(18,2)
var amount = new SqlParameter("Amount", SqlDbType.Decimal) { Precision = 19, Scale = 4, Value = payment.Amount };

await db.Database.ExecuteSqlRawAsync(
    "EXECUTE dbo.Payment_Apply @AccountId, @Amount",
    new SqlParameter("AccountId", payment.AccountId),
    amount);

Procedures and SaveChanges in one transaction

SaveChanges and a procedure call can share a transaction, because both go through the context's connection. Begin it on the Database facade, save, call, commit.

await using var tx = await db.Database.BeginTransactionAsync();

db.Invoices.Add(invoice);
await db.SaveChangesAsync();                                    // the invoice row exists inside the transaction

await db.Database.ExecuteSqlAsync(
    $"EXECUTE dbo.Ledger_Post @InvoiceId={invoice.Id}, @PostedBy={userId}");  // same connection, same transaction

await tx.CommitAsync();

Mapping SaveChanges to procedures

Since EF Core 7 an entity's insert, update and delete can each be routed through a procedure, so ordinary Add and SaveChanges calls end up in your T-SQL. This is the example from the EF Core 7 release notes:

modelBuilder.Entity<Person>()
    .InsertUsingStoredProcedure(
        "People_Insert",
        storedProcedureBuilder =>
        {
            storedProcedureBuilder.HasParameter(a => a.Name);
            storedProcedureBuilder.HasResultColumn(a => a.Id);
        })
    .UpdateUsingStoredProcedure(
        "People_Update",
        storedProcedureBuilder =>
        {
            storedProcedureBuilder.HasOriginalValueParameter(person => person.Id);
            storedProcedureBuilder.HasOriginalValueParameter(person => person.Name);
            storedProcedureBuilder.HasParameter(person => person.Name);
            storedProcedureBuilder.HasRowsAffectedResultColumn();
        })
    .DeleteUsingStoredProcedure(
        "People_Delete",
        storedProcedureBuilder =>
        {
            storedProcedureBuilder.HasOriginalValueParameter(person => person.Id);
            storedProcedureBuilder.HasOriginalValueParameter(person => person.Name);
            storedProcedureBuilder.HasRowsAffectedResultColumn();
        });

The update procedure receives original values for concurrency checks and returns the row count through SELECT @@ROWCOUNT; the insert returns the generated key with an OUTPUT INSERTED.[Id] clause. You can map one operation and leave the others to EF Core. I used this where a DBA-owned procedure had to stay the only write path to a table, and nowhere else: the procedure's shape is now coupled to the model, and a mismatch shows up at runtime.

Keep the procedures in migrations

A procedure that lives only on the server is invisible to the repository and to the test database. Put the CREATE OR ALTER in a migration with migrationBuilder.Sql, and the drop in Down, so every environment gets the same version with the same deploy.

public partial class AccountStatement : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
        => migrationBuilder.Sql("""
            CREATE OR ALTER PROCEDURE dbo.Account_Statement
                @AccountId int, @From date, @To date
            AS
            BEGIN
                SET NOCOUNT ON;
                SELECT PostedOn, Reference, Amount,
                       SUM(Amount) OVER (ORDER BY PostedOn, Id ROWS UNBOUNDED PRECEDING) AS RunningBalance
                FROM dbo.Ledger
                WHERE AccountId = @AccountId AND PostedOn >= @From AND PostedOn < DATEADD(day, 1, @To)
                ORDER BY PostedOn, Id;
            END
            """);

    protected override void Down(MigrationBuilder migrationBuilder)
        => migrationBuilder.Sql("DROP PROCEDURE IF EXISTS dbo.Account_Statement;");
}

A data access layer you can test

The procedure is the logic, so an in-memory database cannot test it, and a mock of DbContext proves nothing. Two layers of tests instead. The business code depends on a small interface per use, so it is unit-tested with a fake that returns known lines. The implementation runs against a real SQL Server in an integration test, started by Testcontainers and migrated to the current version.

public interface IStatements
{
    Task<IReadOnlyList<StatementLine>> ForAccountAsync(int accountId, DateOnly from, DateOnly to, CancellationToken ct);
}

public sealed class SqlStatements(LedgerDbContext db) : IStatements
{
    public async Task<IReadOnlyList<StatementLine>> ForAccountAsync(int accountId, DateOnly from, DateOnly to, CancellationToken ct)
        => await db.Set<StatementLine>()
            .FromSql($"EXECUTE dbo.Account_Statement @AccountId={accountId}, @From={from}, @To={to}")
            .AsNoTracking()
            .ToListAsync(ct);
}

// Testcontainers.MsSql starts a real SQL Server in Docker for the test run
public sealed class StatementTests : IAsyncLifetime
{
    private readonly MsSqlContainer _sql = new MsSqlBuilder().Build();
    private LedgerDbContext _db = null!;

    public async Task InitializeAsync()
    {
        await _sql.StartAsync();
        var options = new DbContextOptionsBuilder<LedgerDbContext>().UseSqlServer(_sql.GetConnectionString()).Options;
        _db = new LedgerDbContext(options);
        await _db.Database.MigrateAsync();                      // tables and procedures from the migrations
    }

    [Fact]
    public async Task Running_balance_is_the_sum_of_amounts_so_far()
    {
        _db.Ledger.AddRange(
            new LedgerEntry { AccountId = 7, PostedOn = new DateOnly(2026, 1, 3), Reference = "INV-1", Amount = 100m },
            new LedgerEntry { AccountId = 7, PostedOn = new DateOnly(2026, 1, 5), Reference = "PAY-1", Amount = -40m });
        await _db.SaveChangesAsync();

        var lines = await new SqlStatements(_db).ForAccountAsync(7, new DateOnly(2026, 1, 1), new DateOnly(2026, 1, 31), default);

        Assert.Equal([100m, 60m], lines.Select(l => l.RunningBalance));
    }

    public async Task DisposeAsync()
    {
        await _db.DisposeAsync();
        await _sql.DisposeAsync();
    }
}

One such test per procedure, with the arithmetic case that matters, is what a testable data access layer meant on a system with 42 of them.

Mistakes I would check for first

• String concatenation into FromSqlRaw. That is SQL injection. Interpolate into FromSql, and when a table or column name has to vary, check it against an allow list first.
• Where or OrderBy after a procedure call. Invalid SQL at runtime. Add AsAsyncEnumerable or move the clause into the procedure.
• Tracking report rows. Keyless types are never tracked, but entity results are; add AsNoTracking to reads.
• Trusting the rows-affected count from a procedure with NOCOUNT ON.
• Decimal parameters without precision. Money arrives rounded to two places.
• Procedures outside migrations. The test database and production drift apart.

The procedures in the migration came after the tables had been loaded and reconciled, which is its own job: Visual FoxPro to SQL Server: a repeatable data migration covers the staging load and control totals, and the migration guide has the order the whole project ran in.