Raw SQL Safely

Run hand-written SQL through FromSql, SqlQuery and ExecuteSql with every value parameterised, and know the composition rules that break at run time.

Syntaxdb.Products.FromSql($"SELECT * FROM \"Products\" WHERE \"Sku\" = {sku}").AsNoTracking() db.Database.SqlQuery<RevenueRow>($"SELECT ...") await db.Database.ExecuteSqlAsync($"CALL archive_orders({cutoff})", ct);

LINQ covers most queries, but some SQL has no LINQ form: recursive CTEs, full-text search, window functions, provider functions, stored procedures. EF Core lets you write that SQL yourself and still get parameters, materialisation and, often, LINQ composed on top.

APIReturnsUse for
db.Products.FromSql($"...")Entities of a mapped type, tracked by defaultA custom query that still returns your entities
db.Database.SqlQuery<T>($"...")Scalars or any unmapped class or recordReports and read models
db.Database.ExecuteSqlAsync($"...")Rows affectedStatements that return no rows: procedures, DDL, maintenance
FromSqlRaw, SqlQueryRaw, ExecuteSqlRawAsyncSame, from a plain string plus parametersOnly when the SQL text itself must be built dynamically

Why the interpolation is safe

FromSql takes a FormattableString, not a string. The compiler hands EF the format and the arguments separately, and EF turns each hole into a DbParameter (@p0); the value never becomes part of the SQL text. The danger is the Raw variants: FromSqlRaw($"... {status}") compiles too, but there the interpolation produces a plain string first, which is SQL injection. The EF1002 analyzer warns about exactly that; treat it as an error. Identifiers (table names, sort columns, ASC/DESC) cannot be parameters at all: map user input onto a fixed whitelist of SQL fragments.

Composition rules

When you add LINQ after FromSql, EF wraps your SQL as a subquery: SELECT ... FROM (your SQL) AS p WHERE .... So:

  • The SQL must be composable: a plain SELECT. A SQL Server EXEC cannot be a subquery, so finish with AsAsyncEnumerable() and compose in memory.
  • Global query filters compose too: a soft-delete filter on Product is appended to your SQL. Use IgnoreQueryFilters where that is wrong, for example with a procedure. An uncomposable EXEC drops every filter, tenant isolation included, so the procedure must take the tenant id as a parameter (or rely on row-level security) and the code must re-apply every filter it dropped, not only soft delete.
  • FromSql must return every column the entity maps, by column name; extra columns are ignored.
  • SqlQuery<decimal> composed with FirstAsync, SingleAsync or Where selects a column named Value; alias it AS "Value" or the query fails at run time.

Production failure modes

  • Drift. Hand-written column names are not checked against the model. A migration renames a column and only the raw query breaks, at run time. Cover each raw query with an integration test against the real engine (Testcontainers), not SQLite or the in-memory provider, which cannot run PostgreSQL or T-SQL syntax.
  • Parameter types on SQL Server. A .NET string becomes nvarchar. Compared with a varchar column, SQL Server converts the column side and the index becomes a scan. Pass a typed SqlParameter with SqlDbType.VarChar.
  • Tracking reads. FromSql results are tracked like any entity query; add AsNoTracking() for read-only endpoints.

Example

Example · csharp
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContext<CatalogDb>(o => o.UseNpgsql(builder.Configuration.GetConnectionString("Catalog")));
builder.Services.AddSingleton(TimeProvider.System);
builder.Services.AddAuthentication().AddJwtBearer();
builder.Services.AddAuthorizationBuilder()
    .AddPolicy("Admin", p => p.RequireRole("admin"));
var app = builder.Build();

// Revenue figures and maintenance procedures are admin-only.
var admin = app.MapGroup("").RequireAuthorization("Admin");

// A recursive CTE has no LINQ form; LINQ composes the filter, sort, paging and projection on top.
app.MapGet("/categories/{categoryId:int}/products", async (int categoryId, CatalogDb db, CancellationToken ct) =>
{
    var products = await db.Products
        .FromSql($"""
            SELECT p.* FROM "Products" AS p
            WHERE p."CategoryId" IN (
                WITH RECURSIVE tree AS (
                    SELECT "Id" FROM "Categories" WHERE "Id" = {categoryId}
                    UNION ALL
                    SELECT c."Id" FROM "Categories" AS c JOIN tree AS t ON c."ParentId" = t."Id")
                SELECT "Id" FROM tree)
            """)
        .Where(p => !p.Discontinued)
        .OrderBy(p => p.Name)
        .Take(100)
        .Select(p => new ProductHit(p.Id, p.Sku, p.Name, p.Price))
        .ToListAsync(ct);

    return TypedResults.Ok(products);
});

// SqlQuery<T> materialises an unmapped record; column aliases must match its property names.
admin.MapGet("/reports/daily-revenue", async (DateOnly from, DateOnly to, CatalogDb db, CancellationToken ct) =>
{
    var start = from.ToDateTime(TimeOnly.MinValue, DateTimeKind.Utc);
    var end = to.AddDays(1).ToDateTime(TimeOnly.MinValue, DateTimeKind.Utc);

    var rows = await db.Database.SqlQuery<DailyRevenue>($"""
            SELECT ("PlacedAt" AT TIME ZONE 'UTC')::date AS "Day",
                   sum("Total") AS "Revenue",
                   count(*)::int AS "Orders"
            FROM "Orders"
            WHERE "PlacedAt" >= {start} AND "PlacedAt" < {end}
            GROUP BY 1
            """)
        .OrderBy(r => r.Day)
        .ToListAsync(ct);

    return TypedResults.Ok(rows);
});

// A procedure call returns no rows. This runs to completion inside the request, so it answers 204;
// an archive that takes minutes belongs in a scheduled BackgroundService, not behind a gateway timeout.
admin.MapPost("/admin/orders/archive", async (CatalogDb db, TimeProvider clock, CancellationToken ct) =>
{
    var cutoff = clock.GetUtcNow().AddYears(-2).UtcDateTime;
    await db.Database.ExecuteSqlAsync($"CALL archive_orders({cutoff})", ct);
    return TypedResults.NoContent();
});

app.Run();

public record ProductHit(int Id, string Sku, string Name, decimal Price);
public record DailyRevenue(DateOnly Day, decimal Revenue, int Orders);

public class Product
{
    public int Id { get; set; }
    public int CategoryId { get; set; }
    public string Sku { get; set; } = "";
    public string Name { get; set; } = "";
    public decimal Price { get; set; }
    public bool Discontinued { get; set; }
}

public class Category
{
    public int Id { get; set; }
    public int? ParentId { get; set; }
    public string Name { get; set; } = "";
}

public class CatalogDb(DbContextOptions<CatalogDb> options) : DbContext(options)
{
    public DbSet<Product> Products => Set<Product>();
    public DbSet<Category> Categories => Set<Category>();
}

When to use it

  • Return every product in a category tree with a recursive CTE in FromSql, then page and project it with LINQ.
  • Serve a daily revenue report from SqlQuery<DailyRevenue> over a GROUP BY that LINQ would translate poorly.
  • Let clients sort customers by email or creation date without ever putting their input into the SQL text.

More examples

The injection that compiles, and a whitelisted sort

Both methods call <code>FromSqlRaw</code> with an interpolated string, and only one is safe: the first splices a user value into the SQL, the second splices only text chosen from a dictionary the code owns, and passes the user value as a named <code>NpgsqlParameter</code>. Ordering by <code>Id</code> as well makes the sort deterministic. For a plain value filter, <code>FromSql</code> or LINQ is simpler; <code>FromSqlRaw</code> is only justified when part of the SQL text is dynamic.

Example · csharp
using Microsoft.EntityFrameworkCore;
using Npgsql;

public class Customer
{
    public int Id { get; set; }
    public string Email { get; set; } = "";
    public string Country { get; set; } = "";
    public DateTimeOffset CreatedAt { get; set; }
}

public class CrmDb(DbContextOptions<CrmDb> options) : DbContext(options)
{
    public DbSet<Customer> Customers => Set<Customer>();
}

public class CustomerSearch(CrmDb db)
{
    // Unsafe: the interpolation runs before EF sees the text. country = "x' OR '1'='1" returns everyone.
    // EF1002 warns on this line.
    public Task<List<Customer>> UnsafeAsync(string country, CancellationToken ct) =>
        db.Customers.FromSqlRaw($"SELECT * FROM \"Customers\" WHERE \"Country\" = '{country}'").ToListAsync(ct);

    private static readonly Dictionary<string, string> SortColumns = new(StringComparer.OrdinalIgnoreCase)
    {
        ["email"] = "\"Email\"",
        ["created"] = "\"CreatedAt\"",
    };

    // Safe: the identifier comes from a fixed list, the value travels as a parameter.
    public Task<List<Customer>> SafeAsync(string country, string sort, bool descending, CancellationToken ct)
    {
        var column = SortColumns.GetValueOrDefault(sort, "\"CreatedAt\"");
        var direction = descending ? "DESC" : "ASC";
        var sql = $"SELECT * FROM \"Customers\" WHERE \"Country\" = @country ORDER BY {column} {direction}, \"Id\"";

        return db.Customers
            .FromSqlRaw(sql, new NpgsqlParameter("country", country))
            .AsNoTracking()
            .ToListAsync(ct);
    }
}

A SQL Server stored procedure and a typed parameter

An <code>EXEC</code> cannot sit inside a subquery, so nothing may be composed on it in SQL: not the soft-delete filter (hence <code>IgnoreQueryFilters</code>, with the check redone in memory), not a <code>Where</code>, not an <code>Include</code>. On a multi-tenant entity the tenant filter is gone too, so the procedure would need a tenant id parameter. <code>AsAsyncEnumerable</code> ends the SQL part of the query. The typed <code>SqlParameter</code> avoids the implicit nvarchar-to-varchar conversion that would turn an index seek on <code>CustomerCode</code> into a scan of the whole table.

Example · csharp
using System.Data;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

public class Invoice
{
    public int Id { get; set; }
    public string Number { get; set; } = "";
    public string CustomerCode { get; set; } = "";
    public decimal Amount { get; set; }
    public DateOnly DueOn { get; set; }
    public bool IsDeleted { get; set; }
}

public class BillingDb(DbContextOptions<BillingDb> options) : DbContext(options)
{
    public DbSet<Invoice> Invoices => Set<Invoice>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Invoice>().Property(i => i.CustomerCode).HasColumnType("varchar(12)");
        modelBuilder.Entity<Invoice>().HasQueryFilter("SoftDelete", i => !i.IsDeleted);
    }
}

public class OverdueInvoices(BillingDb db)
{
    public async Task<List<Invoice>> ForCustomerAsync(string customerCode, DateOnly asOf, CancellationToken ct)
    {
        // varchar, not the default nvarchar, so the index on CustomerCode is used.
        var code = new SqlParameter("@customerCode", SqlDbType.VarChar, 12) { Value = customerCode };
        var date = new SqlParameter("@asOf", SqlDbType.Date) { Value = asOf };

        var invoices = new List<Invoice>();
        await foreach (var invoice in db.Invoices
            .FromSqlRaw("EXEC dbo.GetOverdueInvoices @customerCode, @asOf", code, date)
            .IgnoreQueryFilters()
            .AsNoTracking()
            .AsAsyncEnumerable()
            .WithCancellation(ct))
        {
            if (!invoice.IsDeleted) invoices.Add(invoice);
        }
        return invoices;
    }
}

Scalar queries need a column called Value

For scalar types EF has no property names to match, so it calls the single column <code>Value</code>. A bare <code>ToListAsync()</code> on uncomposed SQL happens to work without the alias, which is why the bug appears only when someone later adds <code>SingleAsync</code>, <code>Where</code> or <code>OrderBy</code>. Always alias scalar results <code>AS "Value"</code> (quoted on PostgreSQL, where unquoted names fold to lower case).

Example · csharp
using Microsoft.EntityFrameworkCore;

public class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options);

public class RevenueQueries(ShopDb db)
{
    public Task<decimal> OpenBalanceAsync(int customerId, CancellationToken ct) =>
        // SingleAsync wraps the SQL: SELECT s."Value" FROM (...) AS s LIMIT 2.
        // Without the alias this fails at run time with "column s.Value does not exist".
        db.Database.SqlQuery<decimal>($"""
            SELECT coalesce(sum(i."Amount" - i."Paid"), 0) AS "Value"
            FROM "Invoices" AS i
            WHERE i."CustomerId" = {customerId} AND i."Paid" < i."Amount"
            """).SingleAsync(ct);

    public Task<List<int>> ProductIdsBelowReorderPointAsync(CancellationToken ct) =>
        db.Database.SqlQuery<int>($"""SELECT "ProductId" AS "Value" FROM inventory_below_reorder_point""")
            .OrderBy(id => id)
            .ToListAsync(ct);
}

Related

net ref fromsqlnet dappernet efcore queryingnet secure headers and inputnet testcontainers

Discussion

  • Be the first to comment on this lesson.