/DB

Procedure Mapping

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

updated 3 May 20263 min readv0.1.82

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 a convention, not a requirement. Any directory works as long as the glob matches your files.

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
  • 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.

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 connection,
    System.Guid userId,
    string reason,
    CancellationToken cancellationToken = default)

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 connection,
    string username,
    [EnumeratorCancellation] CancellationToken cancellationToken = default)

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.

Full Example: Get User Login by Username

File: Socigy/Procedures/GetUserLoginByUsername.sql

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

SELECT "id", "username", "password_hash", "created_at"
FROM "user_logins"
WHERE "username" = @username
  AND "deactivated_at" 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.