EF Core Raw SQL Queries

EF Core has always given you a way to step outside LINQ when a query gets awkward to express. With EF7 and EF8, that escape hatch got noticeably better. EF7 added support for raw SQL queries that return scalar values, and EF8 pushed this further by letting you query unmapped types directly with SQL, something Dapper has offered from day one. This piece covers the SqlQuery and ExecuteSql APIs, how they compose with LINQ, and where the SQL injection protection actually comes from.

Why Raw SQL Still Matters in EF Core

Most day to day queries in EF Core map cleanly onto LINQ. But there are cases where you want to call a stored procedure, read from a database view, or run a query that is simply easier to write in SQL than to coax out of the LINQ provider. Before EF7, your options for this were FromSqlRaw and FromSqlInterpolated, and both required the result type to already be part of your EF model, which meant throwaway DTOs got added to the model just so a raw query could return them.

Querying Unmapped Types with SqlQuery

EF8 removes that constraint with the SqlQuery method. You no longer need to register the return type as an entity in your DbContext. SqlQuery uses string interpolation to build the query, and EF Core automatically parameterizes any interpolated values, so you are not hand-rolling SQL injection protection yourself.

var startDate = new DateOnly(2023, 1, 1);
 
var ordersIn2023 = await dbContext
    .Database
    .SqlQuery<OrderSummary>(
        $"SELECT * FROM OrderSummaries AS o WHERE o.CreatedOn >= {startDate}")
    .ToListAsync();

This query returns a list of OrderSummary records without OrderSummary ever appearing in your EF model configuration. The property names on OrderSummary need to line up with the column names in the result set, not with any table in your database, and the type can have a parameterized constructor if you prefer immutable DTOs. Under the hood, EF Core sends this to the database:

SELECT * FROM OrderSummaries AS o WHERE o.CreatedOn >= @p0

Notice that startDate becomes @p0 rather than being concatenated into the query string. That is the actual injection protection, and it works the same way whether you use SqlQuery or the older FromSqlInterpolated. If you switch to SqlQueryRaw instead, you are back to passing parameters explicitly, which is easy to get wrong under deadline pressure, so I would default to SqlQuery unless you have a specific reason not to.

Beyond plain SELECT statements, SqlQuery also works against database views, table-valued functions, and stored procedures. This is useful if your DBA team already owns a library of those objects and you would rather call into them than reimplement the same logic in C#.

Composing SqlQuery Results with LINQ

One detail that is easy to miss is that SqlQuery returns IQueryable, not a plain list. That means you can chain further LINQ operators onto it before the query actually executes against the database.

var startDate = new DateOnly(2023, 1, 1);
 
var ordersIn2023 = await dbContext
    .Database
    .SqlQuery<OrderSummary>("SELECT * FROM OrderSummaries AS o")
    .Where(o => o.CreatedOn >= startDate)
    .ToListAsync();

This looks convenient, but check the SQL that gets generated before you rely on it in a hot path:

SELECT s.Id, s.CustomerId, s.TotalPrice, s.CreatedOn
FROM (
    SELECT * FROM OrderSummaries AS o
) AS s
WHERE s.CreatedOn >= @p0

EF Core wraps your raw SQL in a subquery and applies the WHERE clause outside it, rather than pushing the filter into the original query. For a small OrderSummaries view this barely matters, but if the raw SQL behind SqlQuery is already an expensive query or a stored procedure call, wrapping it in a subquery can quietly defeat an index seek the database would otherwise use. If filtering matters for performance, put the filter inside the raw SQL string itself and skip the LINQ Where entirely.

Paging works the same way. You can combine OrderBy with Skip and Take on top of a SqlQuery call:

var startDate = new DateOnly(2023, 1, 1);
 
var ordersIn2023 = await dbContext
    .Database
    .SqlQuery<OrderSummary>(
        $"SELECT * FROM OrderSummaries AS o WHERE o.CreatedOn >= {startDate}")
    .OrderBy(o => o.Id)
    .Skip(10)
    .Take(5)
    .ToListAsync();

The generated SQL wraps the raw query in a subquery again, this time applying ORDER BY with OFFSET and FETCH NEXT for the paging:

SELECT s.Id, s.CustomerId, s.TotalPrice, s.CreatedOn
FROM (
    SELECT * FROM OrderSummaries AS o WHERE o.CreatedOn >= @p0
) AS s
ORDER BY s.Id
OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY

Benchmarking this pattern against an equivalent LINQ query with a Select projection did not show a meaningful performance difference between the two. So the composability is a genuine convenience, not a performance trap, as long as your underlying raw query is not the kind that depends on an index the subquery wrapper defeats. Test with your actual query shape before assuming either way holds.

Running Data Modifications with ExecuteSql

SqlQuery covers reads. For UPDATE, DELETE, or stored procedure calls that do not return rows, use ExecuteSql instead:

var startDate = new DateOnly(2023, 1, 1);
 
dbContext.Database.ExecuteSql(
    $"UPDATE Orders SET Status = 5 WHERE CreatedOn >= {startDate}");

ExecuteSql parameterizes interpolated values the same way SqlQuery does, so this UPDATE statement is not vulnerable to injection through startDate. If you are on EF7 or later and the operation is a straightforward bulk update or delete, consider ExecuteUpdate and ExecuteDelete first, since those let you express the same operation in LINQ and keep compile time checking on column references.

Reach for ExecuteSql when the statement genuinely needs to be SQL, such as a stored procedure call or a database specific construct that LINQ has no vocabulary for. Anything that ExecuteUpdate or ExecuteDelete can already express is better left in LINQ, since it keeps the query inside EF Core’s translation pipeline instead of a hand-written string.

When to Reach for This vs LINQ or Dapper

The honest comparison is against Dapper, since that is what most teams reach for when EF Core LINQ starts to feel slow or a query is naturally SQL shaped. SqlQuery comes close to Dapper on raw execution time, and network latency to the database tends to dominate both anyway once you are talking about production workloads. Where SqlQuery wins is that you stay inside a single ORM and a single DbContext, which matters more than a small performance delta once you factor in connection management, transactions, and change tracking for the rest of your application.

I would not make SqlQuery a default choice. Use LINQ for anything your EF model already covers well, since you get compile time safety and the query provider handles translation for you, and reserve SqlQuery and SqlQueryRaw for views, stored procedures, and the handful of queries where the LINQ translation is genuinely worse than the SQL you would write by hand. When you compose LINQ on top of SqlQuery, check the generated SQL with logging turned on, because the subquery wrapping is not obvious from the C# code alone.