# Procedure Mapping

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

## 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:

```xml
<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

```plaintext
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](#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.

```sql
-- @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`](/database/0.3.0/advanced/generator-diagnostics)) for this file. See [Suppressing the placeholder warning](#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](/database/0.3.0/advanced/generator-diagnostics) for the full list.

## Example: Void Procedure (No @returns)

A procedure that performs a write and returns no rows.

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

Generated signature:

```csharp
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.

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

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

Generated signature:

```csharp
public static async IAsyncEnumerable<UserLogin> GetUserLoginByUsername(
    DbConnection connection,
    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, you can 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("...")]` |

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

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

becomes, in the generated method:

```sql
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`–`SCGDB008`](/database/0.3.0/advanced/generator-diagnostics).

> **TIP** Because placeholders resolve through the same logic as the generated [`TableName` and `{Property}ColumnName` constants](/database/0.3.0/core-concepts/how-it-works), a rename in C# propagates to every `.sql` file that references it on the next build — 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`](/database/0.3.0/advanced/generator-diagnostics)) 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:

```sql
-- @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:

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

See [Generator diagnostics → Configuring severity](/database/0.3.0/advanced/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.

```plaintext
Procedures/Admin/GetUsersByRole.sql
```

becomes:

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

Multiple levels of nesting are supported:

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

## Calling Generated Methods

### Streaming with await foreach

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

### Collecting to a list

```csharp
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

```csharp
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`

```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:**

```csharp
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`.