Stored Procedures vs ORM — Choosing the Right Approach for the BlogApp

The decision between stored procedures and EF Core is not binary — the BlogApp uses both strategically. EF Core handles CRUD operations with its superior developer experience (change tracking, migrations, compile-time safety, testability). Stored procedures handle complex, performance-critical operations where T-SQL excels: multi-step transactional operations, complex analytics, and bulk data operations. Understanding how to call stored procedures and raw SQL from EF Core bridges both worlds without compromising on either.

EF Core + Stored Procedures #

// ── Calling a stored procedure from EF Core ───────────────────────────────

// 1. ExecuteSqlInterpolated — for procedures that don't return rows
public async Task PublishPostAsync(int postId, string userId, CancellationToken ct)
{
    var postIdParam  = new SqlParameter("@PostId",     postId);
    var userIdParam  = new SqlParameter("@PublishedBy", userId);
    var outParam     = new SqlParameter("@NewPostId",   SqlDbType.Int)
                           { Direction = ParameterDirection.Output };

    await _db.Database.ExecuteSqlRawAsync(
        "EXEC dbo.usp_PublishPost @PostId, @PublishedBy, @NewPostId OUTPUT",
        postIdParam, userIdParam, outParam, ct);

    int publishedId = (int)outParam.Value;
}

// 2. FromSqlInterpolated — for procedures that return result rows
public async Task<IReadOnlyList<PostSummaryDto>> GetPublishedAsync(
    int page, int size, string? category, CancellationToken ct)
{
    return await _db.Database
        .SqlQuery<PostSummaryDto>(
            $"EXEC dbo.usp_GetPublishedPosts {page}, {size}, {category}")
        .ToListAsync(ct);
    // FromSqlInterpolated parameterises automatically — no injection risk
}

// 3. Raw SQL queries for complex analytics (no stored procedure needed)
public async Task<IReadOnlyList<AuthorStatsDto>> GetAuthorLeaderboardAsync(
    CancellationToken ct)
{
    var sql = @"
        WITH AuthorStats AS (
            SELECT u.Id, u.DisplayName,
                   COUNT(p.Id) AS PostCount, SUM(p.ViewCount) AS TotalViews
            FROM   dbo.Users u JOIN dbo.Posts p ON p.AuthorId = u.Id
            WHERE  p.IsPublished = 1
            GROUP  BY u.Id, u.DisplayName
        )
        SELECT *, DENSE_RANK() OVER (ORDER BY TotalViews DESC) AS Rank
        FROM   AuthorStats ORDER BY TotalViews DESC";

    return await _db.Database
        .SqlQuery<AuthorStatsDto>(FormattableStringFactory.Create(sql))
        .ToListAsync(ct);
}

// ── Decision framework ────────────────────────────────────────────────────
// EF Core is better for:
// ✅ Simple CRUD (Posts.GetById, Posts.Create, Posts.Update)
// ✅ Migrations and schema changes (code-first)
// ✅ Unit testable code (mock DbContext)
// ✅ Change tracking and related entity loading (Include())
// ✅ LINQ-expressible queries (most data access)

// Stored Procedures / Raw SQL is better for:
// ✅ Multi-step transactional operations (PublishPost)
// ✅ Complex analytics with CTEs, window functions
// ✅ Bulk operations (usp_SetPostTags with TVP)
// ✅ Performance-critical queries the optimizer handles better as SQL
// ✅ Operations shared across multiple applications / non-.NET callers

Decision Matrix #

Common Mistakes #

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

❌ Wrong — FromSqlRaw($"SELECT * FROM Posts WHERE Slug = '{slug}'"); injection vulnerability.

✅ Correct — FromSqlInterpolated($"SELECT * FROM Posts WHERE Slug = {slug}"); auto-parameterised.

Mistake 2 — All data access in stored procedures (tightly coupled to SQL Server, hard to test) #

❌ Wrong — every SELECT and CRUD operation in a stored procedure; cannot unit test; schema changes require SP updates.

✅ Correct — EF Core for CRUD and standard queries; stored procedures only for operations that genuinely benefit.