Raw SQL and Stored Procedures — When to Go Beyond LINQ

EF Core LINQ translates most queries to efficient SQL automatically, but some queries are better expressed as raw SQL: complex reporting queries with window functions, full-text search, queries that benefit from SQL Server-specific syntax, or stored procedures. EF Core provides safe, parameterised raw SQL via FromSql() (for queries returning entities), SqlQuery<T>() (for arbitrary projections), and ExecuteSqlAsync() (for DML). All properly parameterise values — there is no SQL injection risk when used correctly.

Raw SQL Queries #

// ── FromSql — parameterised raw SQL returning entities ────────────────────
// Interpolated string → automatically parameterised (NOT string concatenation!)
string searchTerm = "dotnet";
var posts = await db.Posts
    .FromSql($"SELECT * FROM Posts WHERE Title LIKE '%' + {searchTerm} + '%'")
    .AsNoTracking()
    .OrderByDescending(p => p.CreatedAt)
    .ToListAsync(ct);
// Generated SQL: SELECT * FROM Posts WHERE Title LIKE '%' + @p0 + '%'
// {searchTerm} is extracted as SqlParameter — no injection possible

// ── DANGER: FromSqlRaw with string interpolation — SQL INJECTION ───────────
// NEVER do this — {searchTerm} is embedded in the SQL string, not parameterised!
// var posts = await db.Posts.FromSqlRaw($"SELECT * FROM Posts WHERE Title LIKE '%{searchTerm}%'");

// ── Full-text search with SQL Server CONTAINS ─────────────────────────────
var posts = await db.Posts
    .FromSql($"SELECT * FROM Posts WHERE CONTAINS(Body, {searchTerm})")
    .Where(p => p.IsPublished)   // additional LINQ can be composed on top
    .OrderByDescending(p => p.PublishedAt)
    .Take(20)
    .ToListAsync(ct);

// ── SqlQuery — arbitrary projection type (EF Core 7+) ─────────────────────
// Returns non-entity types — does not require DbSet registration
public record PostStats(int TotalPosts, int PublishedPosts, int TotalViews);

var stats = await db.Database
    .SqlQuery<PostStats>($@"
        SELECT
            COUNT(*) AS TotalPosts,
            SUM(CASE WHEN IsPublished = 1 THEN 1 ELSE 0 END) AS PublishedPosts,
            SUM(ViewCount) AS TotalViews
        FROM Posts")
    .FirstOrDefaultAsync(ct);

// ── ExecuteSqlAsync — DML statements (bypasses change tracker) ─────────────
// Safe for bulk updates that do not need entity loading
int rowsAffected = await db.Database.ExecuteSqlAsync(
    $"UPDATE Posts SET ViewCount = ViewCount + 1 WHERE Id = {postId}", ct);

// ── Stored procedure call ─────────────────────────────────────────────────
var posts = await db.Posts
    .FromSql($"EXEC sp_GetPublishedPostsByCategory {categorySlug}, {page}, {pageSize}")
    .ToListAsync(ct);

When to Use Raw SQL vs LINQ #

Common Mistakes #

Mistake 1 — Using FromSqlRaw with string interpolation (SQL injection) #

❌ Critical vulnerability:

db.Posts.FromSqlRaw($"SELECT * FROM Posts WHERE Slug = '{slug}'");  // INJECTION!

✅ Correct — use FromSql($"SELECT * FROM Posts WHERE Slug = {slug}") (automatically parameterised).

Mistake 2 — Reading stale tracked entity after ExecuteSqlAsync() update #

❌ Wrong — tracked entity cached in change tracker; shows old values after raw SQL update.

✅ Correct — call db.Entry(post).ReloadAsync(ct) after raw SQL updates, or use AsNoTracking() for subsequent reads.