Procedure Mapping

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

updated 26 Jun 20269 min readv0.3.3View 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	A `[Table]` type (row stream) or a plain POCO/record DTO mapped per row
`-- @returns scalar: T`	No	A single value: a primitive, `string`, `Guid`, `DateTime`, `DateTimeOffset`, `TimeSpan`, `DateOnly`, `TimeOnly`, or a nullable thereof
`-- @returns affected`	No	The number of rows affected by a write
`-- @ignore warning[: reason]`	No	Suppresses the missing-placeholder warning (`SCGDB003`) for this file. See Suppressing the placeholder warning.

The header line maps directly to the generated return type:

Directive	Generated return type
(no `@returns`)	`Task<bool>` — `true` on success
`@returns affected`	`Task<int>` — the affected-row count
`@returns scalar: T`	`Task<T>`
`@returns: SomeTable` (a `[Table]` type)	`IAsyncEnumerable<SomeTable>`
`@returns: SomePoco` (a non-`[Table]` type)	`IAsyncEnumerable<SomePoco>`

NEW in 0.3.3 The scalar and affected return kinds, and mapping rows to a non-[Table] DTO, were added in 0.3.3. Files written for earlier versions are unaffected — a bare @returns: to a [Table] type still produces the same IAsyncEnumerable<T>.

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)

Scalar Returns

Use -- @returns scalar: T when a query returns a single value — a COUNT(*), a MAX(...), an EXISTS probe, or any one-cell result. The generated method returns Task<T>.

-- @returns scalar: int
-- @param status: string
SELECT COUNT(*) FROM {{User}} WHERE {{User.Status}} = @status

public static async Task<int> CountUsersByStatus(
    DbConnection conn,
    string status,
    CancellationToken cancellationToken = default)

Supported scalar types are the C# primitives, string, Guid, DateTime, DateTimeOffset, TimeSpan, DateOnly, TimeOnly, and their nullable forms. Anything else is reported as SCGDB019.

A NULL result (or no rows) yields default(T) — null for a nullable type, 0/Guid.Empty/etc. for a non-nullable value type. For MAX/MIN/SUM over a possibly-empty set, prefer a nullable return such as scalar: int?.
Numeric results tolerate provider widening: COUNT(*) returns bigint, but -- @returns scalar: int coerces it for you.

-- @returns scalar: int?
-- @param status: string
SELECT MAX({{User.LoginCount}}) FROM {{User}} WHERE {{User.Status}} = @status

Affected-Row Count

By default a write procedure (no @returns) returns Task<bool>. Use -- @returns affected to get the actual number of rows written instead, as Task<int>.

-- @returns affected
-- @param cutoff: System.DateTime
DELETE FROM {{Session}} WHERE {{Session.ExpiresAt}} < @cutoff

public static async Task<int> PurgeExpiredSessions(
    DbConnection conn,
    System.DateTime cutoff)   // returns the number of rows deleted

Mapping to a DTO

-- @returns: doesn't have to name a [Table] type. Point it at any plain POCO or record and the generator emits an AOT-safe materializer (no runtime reflection) that maps result columns to the type by name, case-insensitively. This is ideal for projections, joins, and report shapes that don't correspond to a single table.

-- @returns: MyApp.Reports.UserActivity
-- @param since: System.DateTime
SELECT u."username" AS "Username", COUNT(s."id") AS "SessionCount"
FROM {{User}} u
JOIN {{Session}} s ON s."user_id" = u."id"
WHERE s."started_at" >= @since
GROUP BY u."username"

namespace MyApp.Reports;

// A positional record (constructor-bound)…
public record UserActivity(string Username, int SessionCount);

// …or a property bag (parameterless ctor + settable properties) both work.
public sealed class UserActivity2
{
    public string Username { get; set; } = "";
    public int SessionCount { get; set; }
}

public static async IAsyncEnumerable<UserActivity> GetUserActivity(
    DbConnection conn,
    System.DateTime since,
    [EnumeratorCancellation] CancellationToken cancellationToken = default)

Binding rules:

Columns bind to constructor parameters (records / positional types) or public settable properties by name. Alias columns in the SELECT (AS "Username") to match member names when they differ from the raw column names.
A member with no matching result column is left at its default value (it is not an error).
The DTO must be constructible — a single public constructor, or a public parameterless constructor with settable properties. Otherwise the generator reports SCGDB021.
JSON, [Encrypted], and value-convertor handling apply only to [Table] types; DTO members are read as their plain CLR type.

TIP

Use a [Table] type when you want a real row you can also insert/update; use a DTO when you only need to read a projection.

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