Raw SQL in EF Core 10: FromSql to ExecuteSqlRawAsync, Everything You Need to Know

You are building a reporting endpoint. The product manager wants the top 10 blogs ranked by a weighted score: engagement divided by age in days, filtered by reader segment, grouped by category. You sit down and start writing LINQ.

Twenty minutes later you have a query that spans 30 lines, three nested Select calls, and you still are not sure it will generate the right SQL. You run it. EF Core generates a subquery inside a subquery inside a cross join. The query plan is a disaster.

This is the moment every EF Core developer eventually reaches: LINQ is not always the right tool.

EF Core knows this. That is why it ships six distinct methods for executing raw SQL, each with a different purpose, a different safety model, and a different performance profile. Most developers only know FromSqlRaw and use it everywhere, including places where it is dangerous. This guide covers every method, when to use each one, what SQL gets generated, and most importantly how to avoid the SQL injection traps that can silently exist in code that looks completely fine.

How EF Core Translates LINQ to SQL#

Before reaching for raw SQL, it helps to understand what EF Core is doing when you write a normal LINQ query. There are three stages:

1. Query Translation When you call context.Blogs.Where(b => b.Reader == "alice").OrderBy(b => b.CreatedAt), EF Core builds an expression tree from your LINQ code. It then walks that tree and translates it into SQL. This translation is done at query compilation time and cached, so the second call is faster than the first.

2. Database Interaction The generated SQL is sent to the database as a parameterized command. EF Core uses ADO.NET under the hood. It opens a DbConnection, creates a DbCommand, binds parameters, and executes.

3. Materialization The raw DbDataReader rows come back from the database. EF Core maps each column to the corresponding property on your entity class and constructs the .NET objects you work with.

Raw SQL methods let you take over stage 1. You write the SQL yourself. Stages 2 and 3 still happen exactly the same way. This is why the results of FromSql are tracked, support Include, and compose with LINQ. EF Core takes your SQL and feeds it into the same pipeline as translated LINQ.

The Six Methods at a Glance#

The pattern is consistent. The interpolated variant (FromSql, SqlQuery, ExecuteSqlAsync) is safe by default because EF Core intercepts the C# string interpolation and converts every {value} into a @pN database parameter before the SQL ever leaves your application. The Raw variant (FromSqlRaw, SqlQueryRaw, ExecuteSqlRawAsync) gives you a plain string, and you are responsible for every parameter yourself.

Now let us walk through each one with real code.

1. FromSql: Basic Entity Query#

When to use: You want to fetch full mapped entities using raw SQL, and the SQL is known at compile time.

C#csharp
1public async Task<IEnumerable<BlogDto>> GetAllBlogsAsync()
2{
3    var blogs = await context.Blogs
4        .FromSql($"SELECT * FROM \"Blogs\"")
5        .AsNoTracking()
6        .ToListAsync();
7
8    return blogs.Select(MapToDto);
9}

Key points:

• FromSql takes a FormattableString, specifically that $"..." syntax. EF Core does not call string.Format on it. Instead it reads the format string and argument list separately, converting each argument to a DbParameter. The interpolation is intercepted before the string is assembled.
• AsNoTracking() skips the change tracker entirely. Use it whenever you are not going to call SaveChanges(). It makes read-only queries noticeably faster on large result sets because EF Core does not need to keep track of every entity it materializes in memory.
• The SQL must return all columns that the entity maps to. SELECT * works, but be aware that schema changes can break it silently. A column rename will not cause a compile error.
• FromSql can only be called directly on a DbSet<T>. You cannot chain it after other LINQ operators.

Generated SQL:

SQLsql
1SELECT * FROM "Blogs"

2. FromSql: Interpolated Parameter (Safe by Default)#

When to use: Querying an entity by a user-supplied value. The interpolation handles parameterization for you.

C#csharp
1public async Task<BlogDto?> GetBlogByIdAsync(Guid id)
2{
3    var blog = await context.Blogs
4        .FromSql($"SELECT * FROM \"Blogs\" WHERE \"Id\" = {id}")
5        .AsNoTracking()
6        .FirstOrDefaultAsync();
7
8    return blog is null ? null : MapToDto(blog);
9}

Key points:

• {id} in the interpolated string is never concatenated into the SQL string. EF Core sees it as {0} in the format template and binds it as @p0 with the value passed separately to the database.
• This is the same protection you get from SqlCommand.Parameters.AddWithValue(), but with zero boilerplate.
• You cannot accidentally make this unsafe by passing a variable. The interpolation mechanism itself is what creates the parameter. Compare this to FromSqlRaw, where the string is just a string and you can accidentally concatenate a value in.

Generated SQL:

SQLsql
1SELECT * FROM "Blogs" WHERE "Id" = @p0
2-- @p0 = 'a1b2c3d4-0001-0000-0000-000000000001'

3. FromSql: Named Parameter + LINQ Composition#

When to use: You want a named parameter (more readable than @p0) and you want to compose LINQ operators (filtering, sorting, pagination) on top of the raw SQL result.

Imagine fetching all blogs for a specific reader that were created in the last year, ordered by creation date descending:

C#csharp
1public async Task<IEnumerable<BlogDto>> GetBlogsByReaderAsync(string reader)
2{
3    var readerParam = new NpgsqlParameter("reader", reader);
4
5    var blogs = await context.Blogs
6        .FromSql($"SELECT * FROM \"Blogs\" WHERE \"Reader\" = {readerParam}")
7        .Where(b => b.CreatedAt >= DateTime.UtcNow.AddYears(-1))
8        .OrderByDescending(b => b.CreatedAt)
9        .AsNoTracking()
10        .ToListAsync();
11
12    return blogs.Select(MapToDto);
13}

Key points:

• Passing a NpgsqlParameter into the interpolation gives you control over the parameter name. The SQL uses @reader instead of @p0, which is easier to read in query logs and database traces.
• The .Where() and .OrderByDescending() after FromSql are LINQ operators composed on top. EF Core wraps your raw SQL as a subquery and attaches the LINQ clauses to the outer query.
• For composition to work, the raw SQL must be composable: it must start with SELECT, must return all mapped columns, and must not end with a semicolon. Stored procedure calls are not composable. Use AsEnumerable() after the call if you need to filter the results in memory.

Generated SQL:

SQLsql
1SELECT * FROM (
2    SELECT * FROM "Blogs" WHERE "Reader" = @reader
3) AS b
4WHERE b."CreatedAt" >= @p0
5ORDER BY b."CreatedAt" DESC
6-- @reader = 'charlie'
7-- @p0 = '2025-03-23T00:00:00Z'

4. FromSqlRaw: Dynamic Identifiers with a Whitelist#

When to use: The SQL structure itself needs to be dynamic, for example when the sort column comes from a user-supplied value. FromSqlRaw takes a plain string, not a FormattableString, so you manage all parameters yourself.

The critical rule with FromSqlRaw: column names and table names cannot be parameterized. Databases only allow values as parameters, not identifiers like column names. The only safe approach when identifiers come from external input is a whitelist.

C#csharp
1public async Task<IEnumerable<BlogDto>> SearchBlogsAsync(
2    string searchTerm,
3    string? orderByColumn = null)
4{
5    // Whitelist: only allow known safe column names. Never pass raw user input as an identifier.
6    string[] allowedColumns = ["Name", "Reader", "CreatedAt"];
7    var safeColumn = allowedColumns.Contains(orderByColumn) ? orderByColumn! : "CreatedAt";
8
9    var searchParam = new NpgsqlParameter("searchTerm", $"%{searchTerm}%");
10
11    IQueryable<Blog> query = context.Blogs
12        .FromSqlRaw(
13            "SELECT * FROM \"Blogs\" WHERE \"Name\" ILIKE @searchTerm",
14            searchParam);
15
16    // Apply ordering via LINQ. EF Core composes it as a subquery.
17    query = safeColumn switch
18    {
19        "Name"   => query.OrderBy(b => b.Name),
20        "Reader" => query.OrderBy(b => b.Reader),
21        _        => query.OrderByDescending(b => b.CreatedAt),
22    };
23
24    return (await query.AsNoTracking().ToListAsync()).Select(MapToDto);
25}

Key points:

• The search value (searchTerm) is still sent as an explicit NpgsqlParameter. The SQL string only has @searchTerm as a placeholder. The value never touches the SQL string.
• The column name (safeColumn) passes through a whitelist before entering the SQL. If the caller sends "Name; DROP TABLE Blogs; --", the whitelist check returns false and it falls back to "CreatedAt".
• Ordering is done via LINQ composition rather than building ORDER BY dynamically in the raw SQL string. This is the safer and cleaner approach when you can do it.

Generated SQL (orderBy = "Name"):

SQLsql
1SELECT * FROM (
2    SELECT * FROM "Blogs" WHERE "Name" ILIKE @searchTerm
3) AS b
4ORDER BY b."Name"
5-- @searchTerm = '%efcore%'

5. SqlQuery<T>: Scalar Result#

When to use: You need a single scalar value (a count, a sum, a max) that does not map to any entity in your model.

C#csharp
1public async Task<int> GetBlogCountAsync()
2{
3    var result = await context.Database
4        .SqlQuery<int>($"SELECT CAST(COUNT(*) AS int) AS \"Value\" FROM \"Blogs\"")
5        .ToListAsync();
6
7    return result.FirstOrDefault();
8}

Key points:

• SqlQuery<T> lives on context.Database, not on a DbSet. There is no entity involved. You are querying the database facade directly.
• The output column must be aliased "Value". This is an EF Core requirement for scalar types. EF Core wraps your SQL in a subquery and selects "Value" from the outer query. If the alias is missing, the query throws at runtime with a confusing column-not-found error.
• COUNT(*) returns bigint in PostgreSQL. Cast it to int to match SqlQuery<int>. If you skip the cast you will get a runtime mapping exception.

Generated SQL:

SQLsql
1SELECT "Value" FROM (
2    SELECT CAST(COUNT(*) AS int) AS "Value" FROM "Blogs"
3) AS t

6. SqlQuery<string>: Scalar String List#

When to use: Returning a list of scalar primitives (strings, GUIDs, dates) without loading full entities.

C#csharp
1public async Task<IEnumerable<string>> GetAllBlogNamesAsync()
2{
3    return await context.Database
4        .SqlQuery<string>($"SELECT \"Name\" AS \"Value\" FROM \"Blogs\"")
5        .ToListAsync();
6}

Key points:

• The "Value" alias rule applies to all scalar SqlQuery<T> types: int, string, Guid, DateTime, decimal. It is not specific to numeric types.
• This is far lighter than loading full Blog entities just to extract one field. When you only need a list of names or IDs for a dropdown or autocomplete, this is the right tool.

7. SqlQueryRaw<T> + LINQ Composition#

When to use: You need a scalar query with explicit parameters and you want to compose LINQ filters on top of the result, for example to apply a minimum threshold.

C#csharp
1public async Task<int> GetBlogCountSinceAsync(DateTime since)
2{
3    var sinceParam = new NpgsqlParameter("since", since);
4
5    return await context.Database
6        .SqlQueryRaw<int>(
7            "SELECT CAST(COUNT(*) AS int) AS \"Value\" " +
8            "FROM \"Blogs\" WHERE \"CreatedAt\" >= @since",
9            sinceParam)
10        .Where(count => count > 0)
11        .SingleOrDefaultAsync();
12}

Key points:

• SqlQueryRaw<T> is the explicit-parameter counterpart to SqlQuery<T>. Same rules apply, but you manage the NpgsqlParameter yourself.
• The "Value" alias is required here too, and it matters especially when composing LINQ. EF Core wraps your SQL and the outer query selects "Value" by name.
• .Where(count => count > 0) composes as a WHERE "Value" > 0 on the outer query. This is evaluated in the database, not in memory.

Generated SQL:

SQLsql
1SELECT "Value" FROM (
2    SELECT CAST(COUNT(*) AS int) AS "Value"
3    FROM "Blogs" WHERE "CreatedAt" >= @since
4) AS t
5WHERE "Value" > 0
6-- @since = '2025-01-01T00:00:00Z'

8. ExecuteSqlAsync: Parameterized INSERT#

When to use: Running a write operation where the SQL is fixed at compile time. This is the safest and most concise option for writes.

C#csharp
1public async Task<BlogDto> CreateBlogAsync(CreateBlogDto createBlogDto)
2{
3    var blog = new Blog
4    {
5        Name        = createBlogDto.Name,
6        Description = createBlogDto.Description,
7        Reader      = createBlogDto.Reader,
8    };
9
10    await context.Database.ExecuteSqlAsync(
11        $"""
12         INSERT INTO "Blogs" ("Id", "Name", "Description", "Reader", "CreatedAt", "UpdatedAt")
13         VALUES ({blog.Id}, {blog.Name}, {blog.Description}, {blog.Reader}, {blog.CreatedAt}, {blog.UpdatedAt})
14         """);
15
16    return MapToDto(blog);
17}

Key points:

• Each {value} in the interpolated string becomes a @pN parameter. EF Core handles this the same way as FromSql.
• ExecuteSqlAsync does not interact with the change tracker. The entity is constructed in memory, the INSERT runs, and the method returns without tracking the entity.
• The method returns the number of rows affected. A failed INSERT throws an exception from the database layer, so you do not need to check the return value to detect failure.
• The raw SQL string used here is a C# raw string literal ("""..."""). This is a readability choice, not an EF Core requirement.

Generated SQL:

SQLsql
1INSERT INTO "Blogs" ("Id", "Name", "Description", "Reader", "CreatedAt", "UpdatedAt")
2VALUES (@p0, @p1, @p2, @p3, @p4, @p5)

9. ExecuteSqlAsync: Pre-fetch and Partial UPDATE#

When to use: A partial update where you need to read the current state first to know which fields to preserve, and then write back with raw SQL.

This pattern is common when your API accepts a patch body where only some fields are provided. You fetch the current entity with change tracking on, apply the provided values, then write the merged state back:

C#csharp
1public async Task<BlogDto?> UpdateBlogAsync(Guid id, UpdateBlogDto updateBlogDto)
2{
3    // Change tracking ON. No AsNoTracking. We need the current state in memory.
4    var blog = await context.Blogs
5        .FromSql($"SELECT * FROM \"Blogs\" WHERE \"Id\" = {id}")
6        .FirstOrDefaultAsync();
7
8    if (blog is null) return null;
9
10    // Partial merge. Only overwrite fields that were provided.
11    if (updateBlogDto.Name        is not null) blog.Name        = updateBlogDto.Name;
12    if (updateBlogDto.Description is not null) blog.Description = updateBlogDto.Description;
13    if (updateBlogDto.Reader      is not null) blog.Reader      = updateBlogDto.Reader;
14    blog.UpdatedAt = DateTime.UtcNow;
15
16    await context.Database.ExecuteSqlAsync(
17        $"""
18         UPDATE "Blogs"
19         SET "Name" = {blog.Name},
20             "Description" = {blog.Description},
21             "Reader" = {blog.Reader},
22             "UpdatedAt" = {blog.UpdatedAt}
23         WHERE "Id" = {id}
24         """);
25
26    return MapToDto(blog);
27}

Key points:

• FromSql here is used without AsNoTracking. Change tracking is on so the entity stays in memory with its current values for the partial merge logic.
• The ExecuteSqlAsync call writes the final merged state directly to the database, bypassing SaveChanges entirely. The change tracker knows about the entity but the write does not go through it.
• This demonstrates that FromSql (read) and ExecuteSqlAsync (write) can be used together in the same operation. They share the same database connection and transaction scope if you are inside a BeginTransaction block.

Generated SQL:

SQLsql
1-- Read:
2SELECT * FROM "Blogs" WHERE "Id" = @p0
3
4-- Write:
5UPDATE "Blogs"
6SET "Name" = @p0, "Description" = @p1, "Reader" = @p2, "UpdatedAt" = @p3
7WHERE "Id" = @p4

10. ExecuteSqlAsync: Parameterized DELETE#

When to use: Deleting a single row by primary key. Clean, minimal, and safe.

C#csharp
1public async Task<bool> DeleteBlogAsync(Guid id)
2{
3    var rowsAffected = await context.Database.ExecuteSqlAsync(
4        $"DELETE FROM \"Blogs\" WHERE \"Id\" = {id}");
5
6    return rowsAffected > 0;
7}

Key points:

• ExecuteSqlAsync returns the rows affected. Check it to distinguish "deleted successfully" from "row not found".
• {id} is parameterized automatically. The generated SQL never contains the raw GUID value as a string literal. It is always a bound parameter.

Generated SQL:

SQLsql
1DELETE FROM "Blogs" WHERE "Id" = @p0
2-- @p0 = 'a1b2c3d4-0001-0000-0000-000000000001'

11. ExecuteSqlRawAsync: Explicit Parameters for Dynamic SQL#

When to use: Write operations where the SQL string itself needs to be built at runtime, such as dynamic table names, multi-step pipelines, or bulk operations built conditionally. You supply every parameter explicitly.

C#csharp
1public async Task<int> BulkUpdateReaderAsync(string oldReader, string newReader)
2{
3    var oldReaderParam = new NpgsqlParameter("oldReader", oldReader);
4    var newReaderParam = new NpgsqlParameter("newReader", newReader);
5    var updatedAtParam = new NpgsqlParameter("updatedAt", DateTime.UtcNow);
6
7    return await context.Database.ExecuteSqlRawAsync(
8        "UPDATE \"Blogs\" " +
9        "SET \"Reader\" = @newReader, \"UpdatedAt\" = @updatedAt " +
10        "WHERE \"Reader\" = @oldReader",
11        oldReaderParam, newReaderParam, updatedAtParam);
12}

Key points:

• The SQL string is a plain string, not a FormattableString. EF Core does not auto-parameterize anything. Every value that goes into the database must be wrapped in a NpgsqlParameter.
• The @name placeholder in the SQL string maps to new NpgsqlParameter("name", value). Matching is by name.
• Returns the number of rows affected, which is useful for confirming the bulk operation touched the expected rows.
• Never concatenate user input directly into the SQL string passed to ExecuteSqlRawAsync. Every user-supplied value must go through NpgsqlParameter.

Generated SQL:

SQLsql
1UPDATE "Blogs"
2SET "Reader" = @newReader, "UpdatedAt" = @updatedAt
3WHERE "Reader" = @oldReader
4-- @oldReader = 'alice'
5-- @newReader = 'bob'
6-- @updatedAt = '2026-03-23T10:30:00Z'

The SQL Injection Problem: A Closer Look#

SQL injection is not just a theoretical concern. It is consistently ranked in the OWASP Top 10 and is the root cause of some of the largest data breaches in history. With raw SQL methods, it is worth understanding exactly where the boundary is.

The safe zone: values

All of the following are safe no matter what the user provides, as long as you use the interpolated variant or an explicit DbParameter:

C#csharp
1// Safe. EF Core parameterizes {userId} automatically.
2await context.Blogs.FromSql($"SELECT * FROM \"Blogs\" WHERE \"UserId\" = {userId}");
3
4// Safe. Explicit NpgsqlParameter.
5var p = new NpgsqlParameter("userId", userId);
6await context.Blogs.FromSqlRaw("SELECT * FROM \"Blogs\" WHERE \"UserId\" = @userId", p);

In both cases, the SQL sent to the database is WHERE "UserId" = @p0. The user-supplied value never appears in the SQL string itself.

The danger zone: identifiers

Column names, table names, schema names, and function names cannot be parameterized. Databases do not allow parameters in those positions. A parameter is a value placeholder, not an identifier placeholder. If you try to pass a column name as a parameter, the database will treat it as a string value rather than an identifier, and the query will either return wrong results or fail entirely.

This means the following is dangerous:

C#csharp
1// DANGEROUS: do not do this
2var columnName = Request.Query["sort"];
3await context.Blogs.FromSqlRaw($"SELECT * FROM \"Blogs\" ORDER BY {columnName}");

A malicious user could send sort=1; DROP TABLE "Blogs"; -- and your database is gone.

The correct approach is always a whitelist:

C#csharp
1// Safe: whitelist approach
2var allowedSortColumns = new HashSet<string> { "Name", "CreatedAt", "Reader" };
3var safeSort = allowedSortColumns.Contains(columnName) ? columnName : "CreatedAt";
4
5await context.Blogs.FromSqlRaw($"SELECT * FROM \"Blogs\" ORDER BY \"{safeSort}\"");

Or better, sort using LINQ after FromSql:

C#csharp
1// Even safer: let EF Core generate the ORDER BY
2var query = context.Blogs.FromSql($"SELECT * FROM \"Blogs\"");
3query = columnName switch
4{
5    "Name"   => query.OrderBy(b => b.Name),
6    "Reader" => query.OrderBy(b => b.Reader),
7    _        => query.OrderByDescending(b => b.CreatedAt),
8};

When NOT to Use Raw SQL#

Raw SQL is a tool. Like all tools, using it in the wrong situation makes things worse.

Do not reach for raw SQL when LINQ can express the query. EF Core's LINQ translator is excellent. Even moderately complex queries with joins, groupings, and aggregations translate cleanly. The generated SQL is usually well-optimized. Check the generated SQL with .ToQueryString() or a query logger before deciding LINQ is the problem.

Do not use raw SQL to work around missing columns. If your raw SQL query omits a column that the entity maps to, EF Core will throw at materialization time. This is not a raw SQL limitation. It means your entity and your SQL are out of sync. Fix the query or add a projection.

Do not use ExecuteSqlRawAsync when ExecuteSqlAsync will work. The interpolated variant protects you from accidental SQL injection automatically. Only reach for the Raw variant when you genuinely need a dynamically constructed SQL string.

Do not bypass transactions when you need them. ExecuteSqlAsync does not start a transaction by itself. If you need a read-modify-write to be atomic, wrap it in context.Database.BeginTransactionAsync():

C#csharp
1await using var transaction = await context.Database.BeginTransactionAsync();
2try
3{
4    var blog = await context.Blogs
5        .FromSql($"SELECT * FROM \"Blogs\" WHERE \"Id\" = {id}")
6        .FirstOrDefaultAsync();
7    // ... modify
8    await context.Database.ExecuteSqlAsync($"UPDATE \"Blogs\" SET ... WHERE \"Id\" = {id}");
9    await transaction.CommitAsync();
10}
11catch
12{
13    await transaction.RollbackAsync();
14    throw;
15}

Key Takeaways#

1. Always prefer the interpolated variant (FromSql, SqlQuery, ExecuteSqlAsync). Values are parameterized automatically with no SQL injection risk and no boilerplate.

2. Use the Raw variant (FromSqlRaw, SqlQueryRaw, ExecuteSqlRawAsync) only when the SQL string itself must be dynamic. Always pass values via explicit NpgsqlParameter, never via string concatenation.

3. Column and table names cannot be parameterized by any EF Core API or database driver. Use a hardcoded whitelist when identifiers come from external input. Or better, express the dynamic part as LINQ on top of the raw SQL.

4. The "Value" alias is always required for scalar SqlQuery<T> results. EF Core wraps your SQL in a subquery and selects "Value" from the outer query. Missing it causes a runtime error.

5. LINQ composition works on FromSql and SqlQueryRaw. EF Core wraps the raw SQL as a subquery and adds LINQ operators around it. The raw SQL must be composable: it must start with SELECT, return all mapped columns, and have no trailing semicolon.

6. Use AsNoTracking() on read-only queries. Skip the change tracker when you are not calling SaveChanges. It is a free performance win on any query where you only need to read the data.

7. Transactions are not automatic. ExecuteSqlAsync does not start a transaction. If your operation involves multiple SQL statements that must succeed or fail together, use BeginTransactionAsync.

If you found this post useful, the next step is reading the EF Core performance docs, specifically the sections on query tracking, split queries, and compiled queries. Raw SQL is powerful, but combining it with EF Core's other performance knobs is where you get the real gains.