Lesson 16 of 17Advanced16 min

Raw SQL and Stored Procedures

By the end of this lesson

Drop to SQL when the ORM is the wrong tool, without losing safety.

EF Core translates LINQ into SQL. Some SQL it cannot produce, and some it produces less well than you would by hand: a report using window functions, a recursive walk up a hierarchy, an update across a million rows, a query whose plan you need to control precisely.

Writing SQL for those is not a defeat, and EF Core has first-class support for it. Writing it without parameters is a serious mistake, so this lesson deals with safety before anything else.

The safe forms

OrderStatus status = OrderStatus.Open;

// Parameterised. The value never becomes part of the command text.
List<Order> orders = await context.Orders
    .FromSql($"SELECT * FROM [dbo].[Orders] WHERE [Status] = {status}")
    .Where(o => o.OrderDate >= from)        // composed into the SQL, not in memory
    .Include(o => o.Customer)
    .AsNoTracking()
    .ToListAsync();

// A statement that returns no rows. Returns the number of rows affected.
int archived = await context.Database.ExecuteSqlAsync(
    $"UPDATE [dbo].[Orders] SET [Status] = {closedStatus} WHERE [OrderDate] < {cutoff}");

// A stored procedure that returns Order rows.
List<Order> forCustomer = await context.Orders
    .FromSql($"EXEC [dbo].[GetOrdersForCustomer] @CustomerId = {customerId}")
    .ToListAsync();

FromSql takes an interpolated string, and this is the part to understand properly: EF Core does not paste the value into the text. It reads the holes in the interpolation and sends each one as a separate SQL parameter.
The query stays composable. Where, Include, OrderBy and paging after FromSql are translated into SQL wrapped around it, so the database still does the filtering rather than your process.
FromSql must return every column the entity maps, under the mapped names. That is a real cost of leaving LINQ behind: add a property to Order and a hand-written column list stops matching, with no compiler error to tell you.
ExecuteSqlAsync runs a statement and gives back the rows-affected count. It bypasses the change tracker entirely, so entities already in memory keep their old values.
A stored procedure usually cannot be composed with LINQ operators, because the database will not accept a SELECT wrapped around EXEC. Call AsEnumerable first if you need to shape results in C#.
In EF Core 6 and earlier these methods were named FromSqlInterpolated and ExecuteSqlInterpolatedAsync. Same behaviour; the shorter names are the ones for new code.

Watch out

Concatenation is a vulnerability, and the two forms look almost identical

When you build a command by joining strings around a value, that value becomes part of the statement the database parses. Anything in it is treated as SQL rather than as data. This is SQL injection, and it is how records get read, altered or destroyed through nothing more than a search box.

The trap is visual. FromSql with an interpolated string is parameterised and safe. FromSqlRaw with a concatenated string is not. On screen the two lines are almost the same length and the same shape, and both compile.

Two habits cover it. Never join strings with a value in the middle to build SQL, and always pass values either through FromSql interpolation or as explicit parameters to the Raw overloads.

Object names cannot be parameterised — a parameter carries a value, not a table or column name. When a name genuinely has to vary, map the incoming key to a fixed list of names you control in code, and never let the caller's text through. Better still, express the varying part in LINQ, where it cannot become SQL text at all.

The same query, written both ways, differs in more than safety:

	FromSql with interpolation	FromSqlRaw with concatenation
What the database receives	A fixed command text plus separate parameter values	One string, with the value now part of the command
If the value contains SQL syntax	It is data. It gets compared, not executed	It is parsed as part of the statement
Plan reuse	Same command text every time, so the plan is reused	A different text per value, filling the plan cache with near-duplicates
Type handling	The provider sends the right type and length for each value	You are formatting values into text by hand, including dates and decimals
Safe with values from outside your code	Yes	Only with explicit parameters, never with concatenation

The parameterised call, as the server receives it

SQL

exec sp_executesql
    N'SELECT * FROM [dbo].[Orders] WHERE [Status] = @p0',
    N'@p0 int',
    @p0 = 1;

The command text and the value arrive separately. @p0 is bound as an integer, and nothing about its contents can change the shape of the statement.
This is also why the plan is reused: the text is identical for every value of @p0, so the database recognises it.
Find this in your own logs. It is the quickest way to confirm that a query is parameterised rather than assembled, and it takes about ten seconds.

Common mistake

Common mistakes

Reaching for FromSqlRaw when FromSql would do. Raw exists for the case where the command text itself has to be constructed, which is rare. If you are using it only because it was the first overload you found, switch.

Assigning the interpolated string to a string variable first. The interpolation happens at that moment and the value is baked into the text. FromSql will not accept a plain string, which is a small mercy; ExecuteSqlRaw will take whatever you hand it.

Forgetting that ExecuteSql bypasses the change tracker. Entities already loaded keep their old values, and a later SaveChanges can write those stale values straight back over your update.

Assuming a stored procedure is automatically faster. Sometimes it is. Often the gain came from a better query shape or an index, both of which you could have had from LINQ.

Summary

Raw SQL is a supported part of EF Core, for reports, set-based writes, and plans you need to control
FromSql with an interpolated string sends each value as a parameter and stays composable with LINQ
Concatenating a value into SQL makes it part of the statement, which is an injection vulnerability
The safe and unsafe forms look alike, so the habit is to never join strings to build SQL
Object names cannot be parameterised: use an allow-list you control, or express it in LINQ instead

Practice

Attempt each one before opening the solution. Getting it wrong first is how the idea sticks.

Try it yourself

Confirm the parameter with your own eyes

Write a FromSql query that returns orders for a given status, with the status coming from a variable.

Log the SQL and find the parameter in the log. Then add a Where clause after the FromSql and log it again.

Show solution

You should see sp_executesql with the command text as one argument and the status as a declared parameter. The value is nowhere in the text.

After adding Where, the log shows your SQL wrapped as a subquery with the extra predicate applied around it. That is the composability worth knowing about: the filtering still happens in the database, so you have not given up on it by writing some SQL yourself.

Doing this once is what makes the difference between believing FromSql is safe and knowing it. The log is the evidence.

Think about it

A sort column the user chooses

A search screen lets the user pick which column to sort by from a dropdown. A column name cannot be passed as a parameter.

How would you build this safely, and which approach would you prefer in an EF Core application?

Show solution

The safe pattern with SQL is an allow-list: the request carries a short key, and your code maps that key to one of a fixed set of column names it holds. The user's text never reaches the statement, only your own constants do. Anything not in the list is rejected rather than passed through.

The approach to prefer in EF Core is not to write the SQL at all. A switch expression over the key that returns a different OrderBy gives you the same feature, with the column names as compiled C# that cannot become SQL text.

That is the general principle worth taking from this lesson. The safest way to handle a value that shapes a statement is to keep the decision in code and let the ORM emit the SQL, rather than to sanitise your way out of a string you built.

IQueryable<Order> query = context.Orders.Where(o => o.Status == status);

query = sortKey switch
{
    "date"     => query.OrderByDescending(o => o.OrderDate),
    "customer" => query.OrderBy(o => o.Customer.Name),
    "value"    => query.OrderByDescending(o => o.Items.Sum(i => i.Quantity * i.UnitPrice)),
    _          => query.OrderByDescending(o => o.Id),
};

Knowledge check

Nothing is recorded and there is no score. The explanation appears either way.

Which of these sends the value to the database as a parameter?

FromSqlRaw with the value joined into the stringFromSql with an interpolated stringAn interpolated string assigned to a string variable, then passed to ExecuteSqlRawString.Format, then FromSqlRaw

Why can a table or column name not be passed as a parameter?

It is an EF Core limitation that providers may liftParameters carry values, and the database needs object names to parse and plan the statementNames are too long to fit in a parameterIt works, but only for tables and not for columns

Saved in this browser only.