When LINQ Isn't Enough
Most queries are better expressed in LINQ because it is type-checked, refactor-safe, and provider-agnostic, but some scenarios genuinely need raw SQL: calling a stored procedure, using a database-specific feature LINQ cannot express (like SQL Server's OPTION (RECOMPILE) query hint, or a recursive CTE), or working around a LINQ translation limitation. EF Core exposes FromSqlRaw() and FromSqlInterpolated() on DbSet<T> for exactly these cases, mapping the result columns of a raw SQL query back onto your entity or a keyless entity type.
Cricket analogy: It is like a captain usually trusting the standard bowling rotation, but occasionally calling for a specific, unscripted tactic like a deliberate wide to manipulate the strike, a deviation from the default playbook for a specific situation.
FromSqlRaw and the SQL Injection Risk
FromSqlRaw() accepts a raw SQL string and an array of parameters, for example context.Products.FromSqlRaw("SELECT * FROM Products WHERE CategoryId = {0}", categoryId), and it is critical to always pass values as parameters rather than concatenating them into the SQL string. Writing FromSqlRaw($"SELECT * FROM Products WHERE Name = '{userInput}'") builds the string with unescaped user input baked directly into the SQL text, which is a textbook SQL injection vulnerability the moment userInput comes from anywhere outside your own code.
Cricket analogy: It is like a groundskeeper letting any spectator walk onto the pitch and personally re-mark the crease lines versus having only the umpire, using an approved measuring tool, adjust them; one invites tampering, the other stays controlled.
// SAFE: parameter placeholder, EF Core creates a DbParameter under the hood
var products = await context.Products
.FromSqlRaw("SELECT * FROM Products WHERE CategoryId = {0} AND Price > {1}", categoryId, minPrice)
.ToListAsync();
// UNSAFE: never do this - string interpolation bakes raw input into the SQL text
// var products = await context.Products
// .FromSqlRaw($"SELECT * FROM Products WHERE Name = '{userSearchTerm}'")
// .ToListAsync();Never build a FromSqlRaw() string with C# string interpolation ($"...") or concatenation using values that originate from user input. Always use the {0}, {1} placeholder syntax with a separate parameter argument, which EF Core converts into a properly typed, escaped DbParameter.
FromSqlInterpolated for Safer Syntax
FromSqlInterpolated() accepts a FormattableString, letting you write natural-looking C# string interpolation like context.Products.FromSqlInterpolated($"SELECT * FROM Products WHERE CategoryId = {categoryId}"), while EF Core still extracts each interpolated {} value and converts it into a parameterized DbParameter behind the scenes rather than inlining it as text. This gives you the ergonomic syntax of string interpolation without the injection risk, because the compiler generates a FormattableString object EF Core can inspect, not a plain concatenated string.
Cricket analogy: It is like a DRS system letting the umpire speak naturally into a microphone ('review the leg-before call') while the underlying system still processes it through a fixed, validated set of technology checks, not raw free-text commands to the hardware.
Composing and Executing Non-Query Commands
A FromSqlRaw/FromSqlInterpolated result set can be further composed with LINQ operators like Where(), OrderBy(), or Include(), as long as the raw SQL selects all columns of the mapped entity and does not include a top-level ORDER BY/GROUP BY that would conflict with EF Core wrapping it in a subquery. For commands that don't return rows at all, like a bulk update or delete, context.Database.ExecuteSqlRawAsync() or ExecuteSqlInterpolatedAsync() runs the SQL directly and returns the number of rows affected, bypassing the change tracker entirely.
Cricket analogy: It is like taking a raw ball-by-ball feed from one specific over and then still being able to apply further analysis filters, like isolating just the boundary balls, on top of that raw data.
ExecuteSqlRawAsync() and ExecuteSqlInterpolatedAsync() are the correct tools for bulk operations like UPDATE Products SET Discontinued = 1 WHERE LastOrderDate < @cutoff, since they execute directly against the database without loading entities into the change tracker first.
- FromSqlRaw() and FromSqlInterpolated() let you drop down to raw SQL when LINQ cannot express what you need.
- FromSqlRaw() requires explicit {0}, {1} parameter placeholders; never interpolate or concatenate raw values into the SQL string.
- FromSqlInterpolated() accepts a FormattableString, giving natural interpolation syntax while still parameterizing values safely.
- Raw SQL query results can be further composed with Where, OrderBy, and Include as long as all mapped columns are selected.
- A raw query's SQL should avoid a conflicting top-level ORDER BY/GROUP BY when further LINQ composition is applied.
- ExecuteSqlRawAsync()/ExecuteSqlInterpolatedAsync() run non-query commands like bulk UPDATE/DELETE directly, bypassing the change tracker.
- Reach for raw SQL only for stored procedures, database-specific features, or genuine LINQ translation gaps, not as a default.
Practice what you learned
1. What is the safe way to include a variable value in a FromSqlRaw() query?
2. Why is FromSqlInterpolated() considered safe despite using string interpolation syntax?
3. Which method should you use to run a bulk UPDATE or DELETE statement directly against the database, bypassing the change tracker?
4. Can the results of a FromSqlRaw() query be further composed with LINQ operators like Where() or OrderBy()?
Was this page helpful?
You May Also Like
LINQ to Entities
How EF Core translates LINQ query syntax into SQL, and the rules that govern what can and cannot be pushed down to the database.
Filtering, Sorting, and Paging
Building efficient, parameterized queries with Where, OrderBy, and Skip/Take, plus the tradeoffs of offset versus keyset pagination.
Projections with Select
Using Select to shape query results into DTOs, reducing data transfer and avoiding unnecessary change tracking overhead.
Related Reading
Related Study Notes in Microsoft Technologies
Browse all study notesWindows 10 / UWP Development Study Notes
.NET · 30 topics
Microsoft TechnologiesWindows Batch Scripting Study Notes
Batch · 30 topics
Microsoft TechnologiesMFC (Microsoft Foundation Classes) Study Notes
C++ · 30 topics
Microsoft TechnologiesSilverlight Study Notes
.NET · 30 topics
Microsoft TechnologiesXAML Study Notes
.NET · 30 topics
Microsoft TechnologiesWPF (Windows Presentation Foundation) Study Notes
.NET · 30 topics