Procedure Mapping

Write raw SQL files and get back strongly-typed C# methods generated at compile time.

updated 5 Jun 20266 min readv0.3.2View as Markdown

Overview

Procedure mapping lets you write plain SQL in .sql files, annotate them with a small header, and receive a generated C# method that runs the query with typed parameters and returns a typed result. No boilerplate DbCommand setup, no manual parameter binding.

This is the right tool for complex queries that are inconvenient to express through the fluent query builder: multi-table joins, aggregates, CTEs, window functions, or stored procedure calls.

Project Setup

Declare your .sql files as <AdditionalFiles> in the .csproj so the Roslyn source generator can read them at compile time:

<ItemGroup>
  <AdditionalFiles Include="Socigy\Procedures\**\*.sql" />
</ItemGroup>

The path prefix Socigy\Procedures\ is *required**, not a convention. The source generator only discovers .sql files located under Socigy/Procedures/, so the glob must be `Socigy\Procedures**.sql`. Files placed in any other directory are ignored.

Recommended File Structure

MyApp.DB/
└── Socigy/
    └── Procedures/
        ├── GetUserLoginByUsername.sql
        ├── Admin/
        │   ├── GetUsersByRole.sql
        │   └── DeactivateUser.sql
        └── Reporting/
            └── GetMonthlyStats.sql

Subdirectories translate to nested static classes in the generated output (see Subdirectory Organisation below).

SQL File Header Syntax

Every .sql file may contain a header block of -- @ comment lines before the SQL statement. By convention, @returns comes first, followed by @param lines, but the parser accepts either order.

-- @returns: ReturnTypeName
-- @param paramName: CSharpType

Header line	Required	Description
`-- @param name: Type`	One per parameter	Name and fully-qualified C# type
`-- @returns: FullTypeName`	No	C# type mapped to each result row
`-- @ignore warning[: reason]`	No	Suppresses the missing-placeholder warning (`SCGDB003`) for this file. See Suppressing the placeholder warning.

If @returns is omitted, the generated method returns Task<bool> (true on success).
If @returns is present, the generated method returns IAsyncEnumerable<ReturnType>.

Parameter names used in the SQL body (@username) must match the -- @param names exactly.

NOTE

The generator validates the header and body at build time and reports mismatches as warnings: a -- @param that is never used, an @name in the body with no matching -- @param, an unresolvable -- @returns type, and more. See Generator diagnostics for the full list.

Example: Void Procedure (No @returns)

A procedure that performs a write and returns no rows.

-- @param userId: System.Guid
-- @param reason: string
UPDATE "user_logins"
SET "deactivated_at" = timezone('utc', now()),
    "deactivation_reason" = @reason
WHERE "id" = @userId

Generated signature:

public static Task<bool> DeactivateUser(
    DbConnection conn,
    System.Guid userId,
    string reason)

Example: Query with Return Type

A procedure that returns typed rows.

-- @returns: MyApp.UserLogin
-- @param username: string

SELECT "id", "username", "password_hash"
FROM "user_logins"
WHERE "username" = @username

Generated signature:

public static async IAsyncEnumerable<UserLogin> GetUserLoginByUsername(
    DbConnection conn,
    string username,
    [EnumeratorCancellation] CancellationToken cancellationToken = default)

Schema Placeholders

Hard-coding table and column names in raw SQL means a renamed C# property (or a [Table("...")] / [Column("...")] override) silently drifts away from your queries until something breaks at runtime. To keep SQL tied to your schema, reference them through optional placeholders that the generator expands at build time to the real, double-quoted database names:

Placeholder	Expands to	Resolves like
`{{Type}}`	the table name (`"users"`)	the `Type.TableName` constant / `[Table("...")]`
`{{Type.Property}}`	the column name (`"username"`)	the `Type.{Property}ColumnName` constant / `[Column("...")]`

-- @returns: MyApp.UserLogin
-- @param username: string

SELECT {{UserLogin.Id}}, {{UserLogin.Username}}, {{UserLogin.PasswordHash}}
FROM {{UserLogin}}
WHERE {{UserLogin.Username}} = @username

becomes, in the generated method:

SELECT "id", "username", "password_hash"
FROM "user_logins"
WHERE "username" = @username

How a placeholder resolves:

Type is resolved against your compilation. Use the simple name (UserLogin) or, when that is ambiguous, a fully-qualified name (MyApp.UserLogin). It must be a [Table] (or [FlagTable]) class.
A single segment ({{UserLogin}}) is a table reference; two or more ({{UserLogin.Username}}) is a column reference. A fully-qualified table name such as {{MyApp.UserLogin}} is recognised because MyApp is a namespace, not a type, so it still resolves to the table.
Property is the C# property name. It resolves to the same value as the generated {Property}ColumnName constant, so a [Column("login_name")] override or snake_case conversion is honoured automatically.
The expansion is always the double-quoted identifier ("users", "username"), matching PostgreSQL quoting rules.

Placeholders are entirely optional and can be mixed freely with hand-written names. Any malformed or unresolvable placeholder is reported as a build error. See SCGDB004 to SCGDB008.

TIP

Because placeholders resolve through the same logic as the generated TableName and {Property}ColumnName constants, a rename in C# propagates to every .sql file that references it on the next build, with no manual find-and-replace.

Suppressing the placeholder warning

To encourage keeping SQL in sync with the schema, the generator emits a build warning (SCGDB003) for any .sql file that contains no {{Type.Property}} placeholder at all. The warning is attached to the .sql file itself, so it surfaces in the IDE and in build output.

When a file legitimately needs no placeholders (a query over a view, a one-off maintenance script, or a table you reference by literal name on purpose), opt out per-file with the -- @ignore warning header directive:

-- @ignore warning: Reporting view, columns are fixed by contract
-- @returns: MyApp.MonthlyStat
SELECT "month", "total" FROM "v_monthly_stats"

The text after the colon is a free-form reason for your team; only the presence of -- @ignore warning matters to the generator.

To change the warning project-wide instead of per file, set its severity from .editorconfig. For example, to make a missing placeholder a hard build error:

# .editorconfig
[*.sql]
dotnet_diagnostic.SCGDB003.severity = error

See Generator diagnostics → Configuring severity for details.

Generated Class Name

The source generator produces a single class for the entire project:

{AssemblyName}.Socigy.Generated.Procedures

For a project with assembly name MyApp.DB, the full type name is MyApp.DB.Socigy.Generated.Procedures.

Subdirectory Organisation

Files in subdirectories become nested static classes inside Procedures. The subdirectory name is used as the nested class name.

Procedures/Admin/GetUsersByRole.sql

becomes:

Procedures.Admin.GetUsersByRole(connection, ...)

Multiple levels of nesting are supported:

Procedures/Reporting/Monthly/GetStats.sql
→ Procedures.Reporting.Monthly.GetStats(connection, ...)

Calling Generated Methods

Streaming with await foreach

await foreach (var login in Procedures.GetUserLoginByUsername(conn, "alice"))
{
    Console.WriteLine(login.PasswordHash);
}

Collecting to a list

using System.Linq;

var logins = await Procedures.GetUserLoginByUsername(conn, "alice")
    .ToListAsync();

NOTE

ToListAsync() requires a reference to System.Linq.Async (NuGet package System.Linq.Async).

Calling a void procedure

bool ok = await Procedures.Admin.DeactivateUser(conn, userId, "Terms of service violation");

Supported Parameter Types

C# type	Example
`string`	names, text
`int`	counts, IDs
`bool`	flags
`System.Guid`	UUIDs
`System.DateTime`	timestamps
`System.DateOnly`	calendar dates
`System.TimeOnly`	time-of-day values

Any type that Npgsql accepts as a command parameter value is valid.

File: Socigy/Procedures/GetUserLoginByUsername.sql

-- @returns: MyApp.UserLogin
-- @param username: string

SELECT {{UserLogin.Id}}, {{UserLogin.Username}}, {{UserLogin.PasswordHash}}, {{UserLogin.CreatedAt}}
FROM {{UserLogin}}
WHERE {{UserLogin.Username}} = @username
  AND {{UserLogin.DeactivatedAt}} IS NULL
LIMIT 1

Usage in an endpoint:

app.MapPost("/login", async (LoginRequest req, IDbConnectionFactory factory) =>
{
    await using var conn = factory.Create();
    await conn.OpenAsync();

    await foreach (var login in Procedures.GetUserLoginByUsername(conn, req.Username))
    {
        if (!PasswordHasher.Verify(req.Password, login.PasswordHash))
            return Results.Unauthorized();

        return Results.Ok(new { login.Id });
    }

    return Results.NotFound();
});

TIP

For single-row results, use await foreach with a break or return inside the loop body. There is no single-row overload: the generator always returns IAsyncEnumerable<T> for queries with @returns.

← previousValue convertors

next →DB constants