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

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

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

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