FromSql, SqlQuery, Stored Procedures, Composability, and Safe Escape Hatches for Handwritten SQL

Learning outcomes

Sometimes ServiceHub needs SQL the LINQ translator cannot express cleanly, a database view/table-valued function, a provider-specific query, or an operational command. Raw SQL is a supported EF Core boundary—not a failure—but it removes some abstraction and increases responsibility for parameterization, result shape, composability, provider dialect, permissions, and tests.

1. Raw SQL is a boundary, not a separate data-access universe

// Existing course model — Chapter 09 extends queries, not persistence identity.public sealed partial class WorkOrder{    public int Id { get; private set; }    public string WorkOrderNumber { get; private set; } = string.Empty;    public string CustomerName { get; private set; } = string.Empty;    public WorkOrderPriority Priority { get; private set; }    public int? AssignedTechnicianId { get; private set; }    public Technician? AssignedTechnician { get; private set; }    public ICollection<WorkOrderTag> WorkOrderTags { get; } = new List<WorkOrderTag>();    public Guid Revision { get; private set; }}public sealed class Technician{    public int Id { get; private set; }    public string EmployeeCode { get; private set; } = string.Empty;    public string DisplayName { get; private set; } = string.Empty;    public IReadOnlyCollection<WorkOrder> WorkOrders => _workOrders;    private readonly List<WorkOrder> _workOrders = new();}public sealed class WorkOrderTag{    public int WorkOrderId { get; private set; }    public WorkOrder WorkOrder { get; private set; } = null!;    public int TagId { get; private set; }    public Tag Tag { get; private set; } = null!;    public DateTime AppliedUtc { get; private set; }    public string AppliedBy { get; private set; } = string.Empty;    public int DisplayOrder { get; private set; }}

2. FromSql: parameterized entity query

var minPriority = (int)WorkOrderPriority.High;var rows = await db.WorkOrders    .FromSql($"""        SELECT *        FROM work_orders        WHERE priority >= {minPriority}        """)    .AsNoTracking()    .OrderBy(w => w.Id)    .ToListAsync(ct);

The interpolated minPriority becomes a database parameter. Because this returns WorkOrder entities, the SQL must supply the columns EF needs for that entity mapping. SELECT * is used here only because the disposable lab table exactly matches the mapped entity; production code should be explicit when schema drift/projection control matters.

3. LINQ can compose over composable SQL

var baseQuery = db.WorkOrders    .FromSql($"""        SELECT *        FROM work_orders        WHERE priority >= {(int)WorkOrderPriority.Normal}        """);var query = baseQuery    .Where(w => w.AssignedTechnicianId == null)    .OrderBy(w => w.Id)    .Take(25);Console.WriteLine(query.ToQueryString());

EF treats composable SQL as a subquery and adds provider SQL around it. Composability is database-specific: SQL Server, for example, rejects composition over stored-procedure calls and has restrictions on trailing semicolons/query-level hints/ORDER BY forms inside subqueries.

4. SqlQuery for scalar/unmapped results

var ids = db.Database.SqlQuery<int>($"""    SELECT work_order_id AS Value    FROM work_orders    WHERE priority = {(int)WorkOrderPriority.High}    """);var aboveAverage = await ids    .Where(id => id > db.WorkOrders.Average(w => w.Id))    .ToListAsync(ct);

For scalar composition, Microsoft requires the output column to be named Value because EF needs a predictable column reference when wrapping your SQL. EF Core also supports unmapped mappable CLR result types through SqlQuery<T>; provider type mappings still apply.

public sealed class TechnicianLoadRow{    public string EmployeeCode { get; init; } = string.Empty;    public string DisplayName { get; init; } = string.Empty;    public long WorkOrderCount { get; init; }}var loads = await db.Database.SqlQuery<TechnicianLoadRow>($"""    SELECT t.employee_code AS EmployeeCode,           t.display_name AS DisplayName,           COUNT(w.work_order_id) AS WorkOrderCount    FROM technicians AS t    LEFT JOIN work_orders AS w      ON w.assigned_technician_id = t.technician_id    GROUP BY t.employee_code, t.display_name    """)    .ToListAsync(ct);

5. Deliberately unsafe: concatenate a value into SQL

// WRONG: user input changes SQL text/shape.var customer = request.Customer;var sql = $"SELECT * FROM work_orders WHERE customer_name = '{customer}'";var rows = await db.WorkOrders.FromSqlRaw(sql).ToListAsync(ct);

A value such as ' OR 1=1 -- can change semantics. Repair by using FromSql with interpolation or FromSqlRaw placeholders/DbParameter objects. Values belong in parameters.

var customer = request.Customer;var rows = await db.WorkOrders    .FromSql($"""        SELECT * FROM work_orders        WHERE customer_name = {customer}        """)    .AsNoTracking()    .ToListAsync(ct);

6. Identifiers are not values

You cannot parameterize a column/table name as though it were a scalar value. If the API allows user-selected sort columns, translate a small allow-listed token to known SQL identifiers or, preferably, build expression-tree ordering in LINQ.

var allowedSortColumns = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase){    ["number"] = "work_order_number",    ["priority"] = "priority",    ["created"] = "created_utc"};if (!allowedSortColumns.TryGetValue(request.Sort, out var column))    throw new ArgumentException("Unsupported sort column.");// column comes only from the fixed dictionary, never raw request text.var sql = $"SELECT * FROM work_orders ORDER BY {column} LIMIT @limit";var limit = new Microsoft.Data.Sqlite.SqliteParameter("@limit", 50);var rows = await db.WorkOrders.FromSqlRaw(sql, limit).ToListAsync(ct);

7. Stored procedures: label provider boundaries

SQLite does not implement stored procedures, so they are not mandatory for this free lab. On SQL Server, FromSql can invoke a stored procedure returning an entity-compatible result, but SQL Server does not allow arbitrary LINQ composition over the stored-procedure call. Materialize or cross to AsEnumerable/AsAsyncEnumerable immediately if further client work is intentional.

// SQL Server provider / stored procedure required — not the mandatory SQLite path.var rows = await sqlServerDb.WorkOrders    .FromSql($"EXEC dbo.SearchWorkOrders @Customer={customer}")    .AsNoTracking()    .ToListAsync(ct);

Do not copy that call into SQLite and expect portability. Stored procedure result schema, multiple result sets, output parameters, transaction behavior, and permissions are database-specific contracts.

8. Non-query SQL with ExecuteSqlAsync

var cutoff = DateTime.UtcNow.AddDays(-90);var affected = await db.Database.ExecuteSqlAsync($"""    DELETE FROM work_order_tags    WHERE applied_utc < {cutoff}    """, ct);Console.WriteLine($"Deleted {affected} rows");

ExecuteSqlAsync does not update already-tracked entity state for you. It also does not automatically start a transaction for an arbitrary command and does not use a retrying execution strategy automatically because idempotency cannot be assumed. For destructive experiments, use only the disposable lab and verify transaction/row-count semantics.

9. Tracking and raw SQL still interact

FromSql returning entity types follows normal EF tracking rules. If an entity with the same key is already tracked, identity resolution can return the tracked instance and affect what you observe. For read-only operational/report queries, use AsNoTracking unless tracking is intentional.

var rows = await db.WorkOrders    .FromSql($"SELECT * FROM work_orders WHERE priority = {(int)WorkOrderPriority.High}")    .AsNoTracking()    .ToListAsync(ct);

10. Hands-on lab: safe escape hatches

1. Run a parameterized FromSql query against the disposable SQLite ServiceHub database and capture command parameters.
2. Compose a LINQ filter/order over the raw SQL; save ToQueryString.
3. Run SqlQuery<int> with the Value alias and compose a LINQ predicate.
4. Run the unmapped technician-load report and compare it to the equivalent LINQ GroupBy from Lesson 2.
5. Demonstrate the unsafe concatenated-customer SQL only with harmless lab input, then replace it with parameterized interpolation.
6. Implement allow-listed dynamic sort identifiers; reject an unknown token.
7. Run a disposable ExecuteSqlAsync delete inside a transaction you roll back, then verify row count before/after.
8. If SQL Server is locally available, optionally test the stored-procedure boundary; otherwise document it without pretending SQLite can reproduce it.

11. Chapter 09 production judgment and bridge

Prefer LINQ while it expresses the query clearly and translates well; use raw SQL when database-specific capabilities, complex SQL, views/functions/procedures, or exact command control justify the tighter coupling. Keep values parameterized, identifiers allow-listed, entity/result shapes verified, and provider-specific SQL covered by integration tests. Chapter 10 now turns to related-data loading—Include, filtered includes, explicit/lazy loading, and single-vs-split query cardinality—building directly on this chapter’s join/subquery evidence.

Authoritative references

• SQL Queries - EF Core — FromSql, SqlQuery, composability, tracking, dynamic SQL safety
• ExecuteSqlAsync API - EF Core 10 — parameterized non-query command semantics
• Raw SQL queries for unmapped types — SqlQuery unmapped CLR results
• SQLite SQL language — mandatory local database syntax/limits
• SQL injection - OWASP — risk model for unsafe SQL construction