Three Ways to Call a Stored Procedure

The attendance platform's database holds thirty-one stored procedures and the web application calls about twenty-five of them, so “how do we call a proc?” is not a footnote here — it is the data-access architecture. The codebase answers the question three different ways, all live at once, layered on top of the model from part five. There is also a fourth answer — an entire framework that could never have run — but that one deserves its own part, and gets it next.

Three coexisting mechanisms for one job is itself the finding. Each was presumably the best idea available on the day it was written, and nobody ever went back to unify them. Let me take them in order of sophistication.

Mechanism one: reflection builds the EXEC

The workhorse. A repository base class inspects its own method's parameters via reflection and assembles an EXEC statement for EF Core's FromSqlRaw, materialising results into keyless entity types:

public class SpRepository : DbFunctionBase
{
    public List<MonthOverviewResult> GetMonthOverview(int employeeId, DateTime from, DateTime to)
    {
        try
        {
            // Parameter order MUST match the procedure definition. Never forget this!!!!!
            return Context.MonthOverviewResults
                .FromSqlRaw(BuildExec(nameof(GetMonthOverview), employeeId, from, to))
                .ToList();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
}

BuildExec produces "EXEC [dbo].[GetMonthOverview] @p0, @p1, @p2" with a SqlParameter per argument, and each result shape is registered in the model as HasNoKey() — the EF Core 3 idiom for query-only types. Two things in this small method are worth long looks.

The comment first. It is illustrative here, but the original carried the same warning with the same five exclamation marks, and the author was right to shout: the binding is positional. The generated SQL names its parameters @p0, @p1, @p2 — names the procedure has never heard of — so SQL Server binds them by position. Reorder two parameters in the procedure, or in the C# signature, and nothing fails: an employeeId flows into a @from slot, types permitting, and you get wrong data with a 200 status. A rename-proof alternative existed the whole time — new SqlParameter("@EmployeeId", employeeId) with named passing — and the comment is what you write instead of adopting it. A comment that says never forget is a compiler check that nobody wrote.

Then the catch block. catch (Exception ex) { throw ex; } appears in roughly twenty-five methods in this layer. It looks like a no-op; it is worse. throw ex; resets the stack trace — every exception now appears to originate at the rethrow line, and the actual failing frame is gone. The three-line block exists only to destroy the one piece of information you will want at 2 a.m. The form that preserves the trace is bare throw;, and the form that is better still is no try/catch at all, because a catch that neither handles nor enriches is pure noise. This pattern is the single most copied piece of code in the estate, which tells you how patterns propagate: not by being good, but by being adjacent.

Mechanism two: a TVF by string interpolation

The database's one table-valued function — the recursive org-hierarchy expansion — got its own repository and its own calling convention:

public List<HierarchyRow> GetHierarchy(int managerId)
{
    return Context.HierarchyRows
        .FromSqlRaw($"SELECT * FROM dbo.ufn_GetEmployeeHierarchy({managerId})")
        .ToList();
}

This line is a security lesson in miniature, because it is accidentally safe. FromSqlRaw given an interpolated string performs plain string concatenation — the $"..." is resolved before the method ever sees it, so nothing is parameterised. Its sibling FromSqlInterpolated captures the same syntax as a FormattableString and turns every hole into a DbParameter. The two calls look identical at the call site and behave completely differently. Here, managerId is an int, so the worst it can inject is a number — but the pattern is one refactor away from disaster. The day someone adds a string department overload and follows the house style, the org chart becomes an injection endpoint. EF Core's analysers now warn on exactly this; in the 3.1 era, code review was the only guard, and the house style was the vulnerability.

FromSqlRaw with interpolation is concatenation wearing parameterisation's clothes. If the string has a $, the method should end in Interpolated.

Mechanism three: raw ADO.NET, lists of lists

At the bottom sat a low-level runner for the places EF's shapes didn't fit — a DbCommand loop reading everything as strings:

public List<List<string>> Execute(string sql)
{
    using var command = _context.Database.GetDbConnection().CreateCommand();
    command.CommandText = sql;
    _context.Database.OpenConnection();

    using var reader = command.ExecuteReader();
    var rows = new List<List<string>>();
    while (reader.Read())
    {
        var row = new List<string>();
        for (var i = 0; i < reader.FieldCount; i++)
            row.Add(reader[i]?.ToString());
        rows.Add(row);
    }
    return rows;
}

List<List<string>> is the untyped heart of the report-builder feature: column lists are dynamic, so no compile-time shape exists, and every value rides as a string until the UI formats it. Defensible — dynamic reporting genuinely has no static type. Less defensible is the helper beside it, which fetched distinct column values by concatenating a table name into the SQL unparameterised. Identifiers cannot be parameterised, which is precisely why they must be validated against an allowlist instead — a theme that explodes into a full article when we reach the report builder.

Three mechanisms, one missing decision

Lay them side by side:

Each row is a local optimum. The column that never got filled in is why this one here — no ADR, no comment, no convention doc. The next developer chose by proximity: whichever mechanism the nearest file used. That is how a codebase ends up with three, and how the third's identifier-concatenation habit leaked into new code that had no dynamic-SQL excuse.

There is a deeper reading of why three mechanisms coexisted, and it is not incompetence — it is that EF Core in this era was genuinely awkward about stored procedures, and each mechanism was the least-bad fit for a different awkwardness. Keyless entity types plus FromSqlRaw handled the common “proc returns one tabular shape” case, so it became the default. The TVF needed to appear inside a FROM clause, which FromSqlRaw on a keyless set could express and the EXEC builder could not — hence mechanism two. And the report builder's shape was unknowable at compile time, which no typed mechanism can serve, so it fell through to raw ADO.NET. Read charitably, the three mechanisms are three honest answers to three real constraints. Read as a whole, they are a layer with no owner: nobody was responsible for the question “how do we call procedures here”, so the question got answered three times by three people and never once decided.

What I would do now is boring and short: one thin, typed gateway per result shape; named SqlParameters always; FromSqlInterpolated as the only interpolation-shaped call permitted in review; and Dapper considered honestly for the untyped cases, because List<List<string>> is just a dynamic row with extra steps.

But the most interesting answer to “how do we call a procedure” in this codebase is the one that never executed at all: a complete invocation framework, built on an EF6 extension library, shipped inside this EF Core application — folder helpfully named Future. The autopsy is next.