278 - Stored Procedures · dotnetmadeeasy.com

Two EF Core method pairs let you call a stored procedure. One of them auto-parameterizes for you. The other one doesn't — and confusing them is how 138's lesson on SQL injection comes back to bite you, years later, inside an ORM you trusted.

Intermediate's 138-parameters lesson taught you something you probably assumed you'd never need to revisit: never build a SQL string by gluing untrusted text into it, because the database can't tell "a value" apart from "new instructions." You learned to fix that with SqlParameter and parameterized ADO.NET commands, and you moved on trusting that EF Core — which parameterizes the SQL it generates from LINQ automatically — had that problem handled for good.

It does, for LINQ. But EF Core also gives you an escape hatch into raw SQL and stored procedures, for the real cases where LINQ genuinely isn't the right tool. And that escape hatch comes in two flavors that look almost identical in code, behave completely differently underneath, and — if you reach for the wrong one out of habit — can reopen the exact vulnerability 138 taught you to close. One flavor auto-parameterizes interpolated values for you, safely, no matter how you write the call. The other one doesn't, and trusts you to parameterize it yourself.

In this lesson, you'll learn what a stored procedure actually is, how to call one from EF Core with FromSqlRaw/FromSqlInterpolated and ExecuteSqlRaw/ExecuteSqlInterpolated, exactly which of those four methods protects you from SQL injection and which one doesn't, and when reaching for a stored procedure at all is the right call versus unnecessary friction.

What Is It?

The Simple Explanation

A stored procedure is a named, precompiled block of SQL logic that lives on the database server itself, not in your application. Instead of sending the database an entire SQL statement every time, your application sends a short instruction — "run the procedure named GetTopCustomers with this parameter" — and the database runs logic it already has stored and ready to go.

The Technical Definition

A stored procedure is a saved, named unit of SQL — potentially spanning multiple statements, control-flow logic, temp tables, and conditional branches — stored and compiled inside the database engine, invoked by name (EXEC dbo.GetTopCustomers @minOrders) rather than sent as ad-hoc text on every call. It can accept input parameters, return one or more result sets, use output parameters, and be wrapped in its own transactional logic. From EF Core, you reach a stored procedure through two method families: FromSqlRaw/FromSqlInterpolated, which return tracked or untracked entities, and ExecuteSqlRaw/ExecuteSqlInterpolated, which run a command and return the number of rows affected, for calls that don't need to materialize entities.

Why Does It Exist?

The Problem — Some Logic Belongs Closer to the Data Than LINQ Can Reach

LINQ-to-EF-Core is excellent at expressing "get me these entities, shaped this way." It gets noticeably harder to express certain things cleanly: a report that pivots data across many tables with database-specific windowing functions, a batch operation that needs to run entirely inside the database engine for correctness or performance, or logic a DBA wants to own, version, and permission independently of whatever the application team ships. Forcing all of that through LINQ either produces awkward, hard-to-read query expressions, or drags large amounts of data across the network into application memory just to do work the database itself is far better positioned to do close to the data.

The Solution — a Deliberate, Explicit Escape Hatch Into Raw SQL

EF Core doesn't pretend LINQ is the only tool you'll ever need. FromSqlRaw/FromSqlInterpolated and ExecuteSqlRaw/ExecuteSqlInterpolated exist specifically so you can drop down to raw SQL — including calling an existing stored procedure — for the specific query or operation that genuinely needs it, while keeping the rest of your data access on ordinary, type-safe LINQ. The design goal is narrow and deliberate: an escape hatch for the cases that need it, not a replacement for LINQ everywhere.

Big Picture

FOUR METHODS, TWO QUESTIONS

Question 1 — Does the call return entities, or just a row count?

Returns entities (a SELECT, or a stored procedure returning a result set) → FromSqlRaw / FromSqlInterpolated
No entities — just runs a command (INSERT/UPDATE/DELETE, or a procedure run purely for its side effects) → ExecuteSqlRaw / ExecuteSqlInterpolated

▼

Question 2 — Interpolated or Raw? This is the security question.

Interpolated — built with C#'s $"..." syntax, passed directly. EF Core parses the interpolation itself and safely, automatically parameterizes every interpolated value.
Raw — a plain SQL string with numbered placeholders ({0}, {1}) plus a separate array of values, which EF Core also parameterizes safely — as long as you use the placeholder-and-array form and never build the string yourself first.

Two independent choices, four resulting methods — and the safety of each one depends entirely on how you write the call, not just which pair you picked. That's the part this lesson spends the most time on.

How It Works

Entity-Returning Calls — FromSqlRaw and FromSqlInterpolated

Both return an IQueryable<T> of tracked entities (add .AsNoTracking() the same way you would for any other query when you don't need change tracking):

// FromSqlInterpolated — takes a FormattableString
List<Product> expensiveProducts = await context.Products
    .FromSqlInterpolated($"EXEC dbo.GetProductsAbovePrice {minPrice}")
    .ToListAsync();

// FromSqlRaw — takes a plain string plus a separate parameter array
List<Product> expensiveProductsRaw = await context.Products
    .FromSqlRaw("EXEC dbo.GetProductsAbovePrice {0}", minPrice)
    .ToListAsync();

These two calls produce identical, equally safe SQL. The difference is only in how you write the C# — one uses familiar string-interpolation syntax, the other uses a positional placeholder and a values array. Both parameterize minPrice correctly. Keep that image in mind — it's about to matter, because the danger case looks deceptively similar to the safe one.

Non-Query Calls — ExecuteSqlRaw and ExecuteSqlInterpolated

Both live on context.Database and return an int — the number of rows affected — for commands that don't materialize entities:

int rowsArchived = await context.Database.ExecuteSqlInterpolatedAsync(
    $"EXEC dbo.ArchiveOldOrders {cutoffDate}");

int rowsArchivedRaw = await context.Database.ExecuteSqlRawAsync(
    "EXEC dbo.ArchiveOldOrders {0}", cutoffDate);

Same shape, same rule: interpolated auto-parameterizes by construction; raw needs the placeholder-and-array form to stay safe.

Simple Example — Calling a Stored Procedure by Name

Suppose a DBA has already created this stored procedure:

CREATE PROCEDURE dbo.GetProductsByCategory
    @CategoryId INT
AS
BEGIN
    SELECT Id, Name, Price, CategoryId
    FROM Products
    WHERE CategoryId = @CategoryId AND IsDiscontinued = 0;
END

Calling it from EF Core, entities-back, the safe and idiomatic way:

public async Task<List<Product>> GetProductsByCategoryAsync(int categoryId)
{
    return await context.Products
        .FromSqlInterpolated($"EXEC dbo.GetProductsByCategory {categoryId}")
        .ToListAsync();
}

categoryId is written right inside the $"..." string, exactly the way you'd write any interpolated string — but because FromSqlInterpolated expects a FormattableString, not a string, the C# compiler does not eagerly build a plain string here. It keeps the literal SQL text and categoryId as two separate pieces, and EF Core reads that structure to build a real, safely-typed database parameter before anything is sent to SQL Server. This is the mechanism that makes FromSqlInterpolated safe by construction — covered in detail in Under the Hood, below.

Real-World Example — the Vulnerable-vs-Safe Comparison, Reinforcing 138

This is the exact scenario 138-parameters warned you about, now wearing an EF Core costume. A product search box, backed by a stored procedure:

Vulnerable — building the raw string yourself, then handing it to FromSqlRaw

// NEVER DO THIS
List<Product> results = await context.Products
    .FromSqlRaw($"EXEC dbo.SearchProducts '{searchTerm}'")   // C# interpolation BEFORE FromSqlRaw sees it
    .ToListAsync();

Look closely at what's happening: this uses $"..." too — but because FromSqlRaw expects an ordinary string parameter, not a FormattableString, the C# compiler does eagerly evaluate the interpolated string into a single, fully-baked string before FromSqlRaw ever sees it. searchTerm is already glued into the SQL text by the time EF Core receives it. FromSqlRaw has no placeholder to find and nothing to parameterize — it just sends the string exactly as it received it. If searchTerm is x'; DROP TABLE Products; --, that text becomes part of the executed SQL, exactly like the vulnerable ADO.NET example from 138.

Safe — FromSqlRaw with a placeholder and a separate parameter array

List<Product> results = await context.Products
    .FromSqlRaw("EXEC dbo.SearchProducts {0}", searchTerm)
    .ToListAsync();

Here the SQL text is a fixed literal — "EXEC dbo.SearchProducts {0}" never changes — and searchTerm travels alongside it as a separate argument. EF Core recognizes the {0} placeholder and converts searchTerm into a real DbParameter before sending anything to the database. This is the correct way to use FromSqlRaw when you genuinely need the raw form — it is exactly as safe as the interpolated version.

Safe — FromSqlInterpolated, the more natural-reading equivalent

List<Product> results = await context.Products
    .FromSqlInterpolated($"EXEC dbo.SearchProducts {searchTerm}")
    .ToListAsync();

Same safety, more readable syntax — and it's the one that's genuinely hard to get wrong, because there's no separate placeholder-and-array bookkeeping to forget.

Also safe — FromSqlRaw with an explicit SqlParameter

For cases where you need to control the exact SQL type, size, or direction (an output parameter, say), FromSqlRaw also accepts real SqlParameter/DbParameter objects directly, exactly the way 138 taught you to build them for ADO.NET:

SqlParameter searchParam = new("@Term", SqlDbType.NVarChar, 100) { Value = searchTerm };

List<Product> results = await context.Products
    .FromSqlRaw("EXEC dbo.SearchProducts @Term", searchParam)
    .ToListAsync();

This is the third genuinely safe pattern: the SQL text stays a fixed literal, and the value is handed over as a real, typed database parameter object — never string-concatenated into the command text.

The rule that actually matters: It's not "Interpolated = safe, Raw = unsafe." Both method families can be used safely. The real rule is: never let a value reach the SQL text as plain concatenated or C#-interpolated string content before EF Core sees it. FromSqlInterpolated makes that mistake structurally impossible, because it receives a FormattableString, not a finished string. FromSqlRaw makes it possible to do it safely (placeholders + array, or explicit parameter objects) — but it also makes it possible to do it dangerously, if you build the string yourself first with concatenation or interpolation before calling it.

Analogy

A Sealed Envelope vs. a Note Written Directly on the Form

FromSqlInterpolated is like handing the post office a printed form with a labeled blank, and separately, a sealed envelope containing the customer's answer — the post office reads the form's fixed structure, then opens the envelope only to read the value it's tagged for. The customer's answer can never rewrite the form itself, because it never touches the form directly.

FromSqlRaw used correctly (placeholder + array, or an explicit parameter) is the same sealed-envelope handoff — just requiring you to write "please insert the envelope's contents at blank #0" yourself instead of it happening automatically from the syntax.

FromSqlRaw used incorrectly is what happens when someone writes the customer's answer directly onto the form in pen, in the same handwriting as the form's own printed instructions, before handing it over — the post office has no way left to tell "the customer's answer" apart from "part of the form" once that's done.

Under the Hood

WHY $"..." BEHAVES DIFFERENTLY DEPENDING ON THE METHOD IT'S PASSED TO

1. C#'s interpolated string has two possible compiled forms

When a $"..." expression is assigned to (or passed as) a parameter of type string, the compiler immediately calls string.Format-equivalent logic and produces one finished, fully-substituted string — indistinguishable from hand-written concatenation by the time anything downstream sees it. When it's assigned to (or passed as) a parameter of type System.FormattableString, the compiler instead produces an object holding the format string and its argument values separately — nothing is substituted yet.

▼

2. FromSqlInterpolated's parameter type is FormattableString — that's the entire mechanism

Because the parameter is typed as FormattableString, C# hands EF Core the unsubstituted structure. EF Core walks that structure itself, turns every interpolation hole into a real DbParameter, and only then builds the final parameterized command it sends to the database. Nothing about the syntax "looking like" interpolation makes it unsafe — the safety comes entirely from the parameter type EF Core declared.

▼

3. FromSqlRaw's parameter type is plain string — it never sees your interpolation holes at all

If you build that string with concatenation or C# interpolation first, it's already fully formed, with values baked directly into the text, by the time FromSqlRaw receives it — there's no structure left to parse. FromSqlRaw's own placeholder syntax ({0}, {1}) is a separate mechanism it looks for in that plain string and matches against a params array you pass alongside it — it has nothing to do with C#'s own interpolation feature.

▼

4. Either safe path ends the same way: a parameterized command, just like 138's ADO.NET example

Whether you arrive via FromSqlInterpolated, a correctly-used FromSqlRaw with placeholders, or an explicit SqlParameter, EF Core ends up sending the exact same shape of request to the database as the parameterized ADO.NET calls from 138 — SQL text and values as separate, distinct pieces.

Common Confusion

1. "FromSqlRaw is the dangerous one, FromSqlInterpolated is the safe one"

Close, but not quite right — and the imprecise version of this rule is exactly what causes real bugs. FromSqlInterpolated genuinely can't be used unsafely for value interpolation, because of the FormattableString mechanism above. FromSqlRaw can be used either safely (placeholders + array, or explicit parameters) or unsafely (a pre-built, concatenated, or C#-interpolated string handed to it as one opaque blob) — it depends entirely on how you call it, not on which method name you typed.

2. "You can compose further LINQ on top of any FromSql call, including a stored procedure"

You can compose additional LINQ operators — .Where(), .OrderBy() — on top of a FromSqlRaw/FromSqlInterpolated call for ordinary raw SELECT SQL. Once the call is invoking a stored procedure specifically, EF Core does not compose further server-side SQL on top of it — the procedure's result set is what you get. If you need additional filtering or sorting on procedure output, either build that into the procedure itself, or apply it client-side in C# after materializing the results with .ToListAsync().

Common Mistakes

Mistake 1 — Interpolating a string first, then passing it to FromSqlRaw

context.Products.FromSqlRaw($"EXEC dbo.Search '{term}'") — this is a real, exploitable SQL injection vulnerability, identical in shape to the ADO.NET mistake 138 warned about. It compiles cleanly and works fine for a normal search term, which is exactly what makes it dangerous. Use FromSqlInterpolated for this call, or use FromSqlRaw with a {0} placeholder and a separate argument.

Mistake 2 — Reaching for a stored procedure to "avoid EF Core" for ordinary CRUD

Wrapping a plain single-table INSERT or a simple filtered SELECT in a stored procedure purely out of habit, when the equivalent LINQ is just as fast and far more maintainable. Reserve stored procedures for the cases where they genuinely earn their keep — see the next section.

Mistake 3 — Forgetting AsNoTracking() on a read-only FromSql query

Materializing a large read-only result set from a reporting stored procedure with full change tracking enabled, paying tracking overhead for entities that will never be modified or saved. Add .AsNoTracking() to a FromSqlRaw/FromSqlInterpolated call exactly as you would for any other read-only LINQ query — the same tracking-vs-no-tracking judgment from Intermediate's 146 lesson still applies here.

When Should I Use It?

A stored procedure genuinely earns its place

Complex, multi-table, set-based reporting logic that's awkward or inefficient to express as LINQ
Logic a DBA needs to own, tune, and permission independently of your deployment cycle
Integrating with a legacy database where procedures are already the established, documented interface
Bulk, database-server-side operations where you specifically want the logic to run close to the data, not round-trip through your application

It's adding friction for no real benefit

Ordinary single- or few-table CRUD that LINQ already expresses clearly
Anything where you want compile-time type checking on the query shape — a stored procedure's SQL isn't checked by the C# compiler at all
Logic that changes often alongside your application code — a procedure lives on a separate deployment/versioning track from your codebase
Anything you want to unit test easily without spinning up a real database

Rule of thumb: Reach for a stored procedure when the logic genuinely belongs on the database server — not as a default way to write data access, and never as a way to avoid learning EF Core's own safe raw-SQL parameterization.

Mental Model

FromSql = entities come back. ExecuteSql = just a row count comes back.
Interpolated = EF Core reads your $"..." structure itself and parameterizes it for you — safe by construction.
Raw = you control the exact SQL text — safe if you use placeholders/parameters, dangerous if you build the string yourself first.

Remember: the danger was never the word "Raw" — it's letting a value touch the SQL text as plain concatenated or interpolated content before EF Core ever sees it.

Key Takeaway

A stored procedure is precompiled SQL logic stored and run on the database server, invoked by name.
FromSqlRaw/FromSqlInterpolated call procedures (or raw SQL) that return entities; ExecuteSqlRaw/ExecuteSqlInterpolated run non-query commands and return a row count.
FromSqlInterpolated/ExecuteSqlInterpolated receive a FormattableString, not a plain string — EF Core reads its structure and safely parameterizes every interpolated value automatically.
FromSqlRaw/ExecuteSqlRaw receive a plain string and do NOT auto-parameterize it — a string you built yourself with concatenation or interpolation before the call is sent verbatim, reopening real SQL injection. Used correctly, with {0}-style placeholders plus a separate argument array, or with explicit SqlParameter/DbParameter objects, they are exactly as safe.
Reach for a stored procedure for complex set-based reporting, DBA-owned logic, or legacy integration — not as a default replacement for ordinary LINQ CRUD.

Check Your Understanding

This lesson connects directly back to 138-parameters' security lesson — being able to spot the unsafe pattern instantly is the whole point.

1. Which of these calls is a genuine SQL injection vulnerability?

A) context.Products.FromSqlInterpolated($"EXEC dbo.Search {term}")
B) context.Products.FromSqlRaw($"EXEC dbo.Search '{term}'")
C) context.Products.FromSqlRaw("EXEC dbo.Search {0}", term)
D) context.Database.ExecuteSqlInterpolatedAsync($"EXEC dbo.Archive {cutoff}")

Show answer

Correct: B

Why B is correct: Because FromSqlRaw expects a plain string, the C# compiler eagerly evaluates the $"..." expression into one fully-substituted string before FromSqlRaw ever sees it — term is already baked into the SQL text with no parameterization applied.

Why A is incorrect: FromSqlInterpolated expects a FormattableString, so C# keeps the text and term separate — EF Core parameterizes it safely.

Why C is incorrect: This is the safe raw form — a fixed SQL string with a {0} placeholder plus a separate argument, which EF Core converts into a real parameter.

Why D is incorrect: Same mechanism as A — ExecuteSqlInterpolatedAsync also takes a FormattableString and parameterizes it safely.

Reinforcement: The danger isn't the word "Raw" — it's a value reaching the SQL text as an already-built string before EF Core ever sees the call.

2. Why does FromSqlInterpolated($"EXEC dbo.Search {term}") stay safe even though it uses C#'s $"..." interpolation syntax — the same syntax that's dangerous elsewhere?

A) Because EF Core scans the final string for suspicious SQL keywords and blocks them
B) Because FromSqlInterpolated's parameter type is FormattableString, not string — C# keeps the literal text and the interpolated value as separate pieces instead of eagerly building one finished string, letting EF Core parameterize the value itself
C) Because interpolated strings are always automatically safe in any C# context
D) Because term is validated against a list of forbidden characters before the call runs

Show answer

Correct: B

Why B is correct: The safety comes from the parameter type EF Core declared for the method. A FormattableString parameter tells the C# compiler not to eagerly evaluate the interpolated string — it hands over the format text and arguments as a structure, which EF Core reads to build real parameters.

Why A is incorrect: EF Core does not scan or blocklist SQL keywords in values — that approach was explicitly called out as unreliable back in 138. The real mechanism is structural, not detection-based.

Why C is incorrect: Interpolated strings are not universally safe — passed to a method expecting a plain string (like FromSqlRaw), the exact same syntax becomes dangerous, as question 1 showed.

Why D is incorrect: There's no character-based validation happening — the value can contain anything, including quotes and SQL syntax, and it's still safe because it's never re-parsed as SQL text.

Reinforcement: This is the single most important mechanical detail in the lesson — the method's declared parameter type, FormattableString vs string, is what determines whether interpolation syntax is safe.

3. A developer wants to call a stored procedure and needs an explicitly-typed, fixed-size parameter (matching a specific SqlDbType) rather than letting EF Core infer it. Which approach correctly supports this?

A) FromSqlInterpolated only, since FromSqlRaw cannot accept typed parameters
B) FromSqlRaw, passed an explicit SqlParameter/DbParameter object instead of a plain value
C) Neither method supports explicitly-typed parameters — you'd need raw ADO.NET instead
D) ExecuteSqlRaw only, since FromSqlRaw never accepts parameter objects

Show answer

Correct: B

Why B is correct: FromSqlRaw accepts real SqlParameter/DbParameter objects directly in its argument list, exactly like the explicit parameter form from 138's ADO.NET lesson — giving you full control over type and size while keeping the value safely parameterized.

Why A is incorrect: FromSqlInterpolated infers parameter types automatically from the interpolated value's .NET type — it doesn't offer this level of explicit control the way a manually-constructed SqlParameter passed to FromSqlRaw does.

Why C is incorrect: This is a real, supported EF Core capability — you don't need to drop to raw ADO.NET for it.

Why D is incorrect: FromSqlRaw (the entity-returning method) does accept parameter objects, not just ExecuteSqlRaw (the non-query method) — both support this form.

Reinforcement: Explicit SqlParameter objects are the third safe pattern alongside interpolated calls and the placeholder-plus-array form.

4. Which scenario is the strongest, most honest case for reaching for a stored procedure instead of ordinary LINQ?

A) A simple query that filters one table by a single column value
B) A complex, multi-table reporting query using database-specific set-based logic that a DBA owns and tunes independently of the application's deployment cycle
C) Any query at all, since stored procedures are always faster than LINQ-generated SQL
D) Inserting a single new row into a table

Show answer

Correct: B

Why B is correct: This is exactly the honest use case the lesson lays out — complex set-based reporting and DBA-owned logic are where a stored procedure's separate deployment lifecycle and database-server-side execution genuinely add value over LINQ.

Why A is incorrect: A simple single-table filter is precisely the kind of case ordinary LINQ already expresses clearly — a stored procedure would only add friction here.

Why C is incorrect: The lesson explicitly rejects "always faster" as a blanket claim — performance depends on the specific query, and EF Core-generated SQL is often just as efficient for straightforward cases.

Why D is incorrect: A single-row insert is ordinary CRUD that context.Add() and SaveChangesAsync() already handle cleanly.

Reinforcement: Stored procedures earn their place through genuine complexity or ownership boundaries, not as a default data-access style.

You now know exactly how to call a stored procedure from EF Core, and — more importantly — exactly which of the four methods protects you from SQL injection by construction and which one trusts you to get it right. Next up: what to do when tracked, one-row-at-a-time EF Core operations aren't fast enough for a truly large batch of changes.

dotnetmadeeasy.com — Learn C# and .NET, the right way.