# Database context & unit of work

The generated, interface-based database context. Inject ISocigyDatabaseFactory, run work inside ExecuteAsync/ExecuteTransactionAsync scopes, and never pass a DbConnection around. Fully mockable for unit tests.

## Why a context?

Passing a raw `DbConnection` into every call works, but it leaks connection lifetime into your business code and makes services hard to unit-test. The database context (new in 0.2.0) is a generated, fully interface-based facade. You inject a factory, run your work inside a scope, and the connection (plus an optional transaction) is acquired from the registered `IDbConnectionFactory` and disposed for you.

Because every seam is an interface, services that depend on it are 100% unit-testable with no database. See [Unit testing](/database/0.3.2/testing/unit-testing).

## What gets generated

For a database named `AuthDb` (the `databaseName` in `socigy.json`), the generator emits:

| Type | Role |
|------|------|
| `IAuthDb` / `AuthDbContext` | The context: one table-set accessor per table, plus `WithConnectionAsync` and `DynamicTable<T>`. |
| `IUserSet` / `UserSet`, … | A mockable data-access seam per table. |
| `AuthDbFactory` | The DI-resolvable `ISocigyDatabaseFactory<IAuthDb>`. |
| `AddAuthDbContext()` | DI registration extension. |

Table-set accessors are pluralized from the entity name: `User` becomes `Users`, `Category` becomes `Categories`, `Class` becomes `Classes`, `TaskItem` becomes `TaskItems`.

## Registration

`AddAuthDbContext()` registers the factory. It relies on `AddAuthDb()` (which registers the connection factory) having been called too:

```csharp
builder.AddAuthDb();                 // registers IDbConnectionFactory (existing)
builder.Services.AddAuthDbContext(); // registers ISocigyDatabaseFactory<IAuthDb> (new)
```

`AddAuthDbContext` takes an optional configurator for connection behavior:

```csharp
builder.Services.AddAuthDbContext(o =>
{
    o.ConnectionLifetime = ConnectionLifetime.PerScope; // default
    o.ConnectionKey = null;                              // appsettings sub-key, e.g. "ReadOnly"
});
```

## Running work

Inject `ISocigyDatabaseFactory<IAuthDb>` and run work inside a scope. Two entry points:

- `ExecuteAsync(...)`: a non-transactional scope, ideal for reads.
- `ExecuteTransactionAsync(...)`: wraps the work in a transaction. It commits when the delegate returns and rolls back if it throws.

Both have a `<TResult>` overload that returns a value, and both accept an optional `CancellationToken`.

```csharp
public class EnrollmentService(ISocigyDatabaseFactory<IAuthDb> db)
{
    public Task<Guid> EnrollAsync(Guid userId, string courseName) =>
        db.ExecuteTransactionAsync(async d =>
        {
            var course = new Course { Id = Guid.NewGuid(), Name = courseName };
            await d.Courses.InsertAsync(course);
            await d.UserCourses.InsertAsync(new UserCourse { UserId = userId, CourseId = course.Id });
            return course.Id;   // committed here; rolled back automatically if anything above throws
        });

    public Task<List<User>> FindActiveAsync() =>
        db.ExecuteAsync(d => d.Users.ToListAsync(u => u.IsActive));
}
```

## Table-set methods

Each `I{Table}Set` exposes terminal async methods (which materialize before the scope closes) plus a streaming callback:

```csharp
Task<bool>        ExistsAsync(Expression<Func<User,bool>> predicate);
Task<User?>       FirstOrDefaultAsync(Expression<Func<User,bool>> predicate);
Task<List<User>>  ToListAsync(Expression<Func<User,bool>>? predicate = null);
Task<long>        CountAsync(Expression<Func<User,bool>>? predicate = null);
Task<TResult?>    SumAsync<TResult>(Expression<Func<User,object?>> selector, Expression<Func<User,bool>>? predicate = null) where TResult : struct;
Task<TResult?>    AvgAsync<TResult>(Expression<Func<User,object?>> selector, Expression<Func<User,bool>>? predicate = null) where TResult : struct;
Task<TResult?>    MinAsync<TResult>(Expression<Func<User,object?>> selector, Expression<Func<User,bool>>? predicate = null) where TResult : struct;
Task<TResult?>    MaxAsync<TResult>(Expression<Func<User,object?>> selector, Expression<Func<User,bool>>? predicate = null) where TResult : struct;
Task<TResult?>    ScalarAsync<TResult>(Expression<Func<User,object?>> selector, Expression<Func<User,bool>>? predicate = null);
Task<bool>        InsertAsync(User entity, bool includeAutoFields = false);
Task<int>         InsertMultipleAsync(IEnumerable<User> entities, bool includeAutoFields = false, CancellationToken ct = default);
Task<int>         UpdateAsync(User entity);
Task<int>         DeleteAsync(Expression<Func<User,bool>> predicate);
Task              ForEachAsync(Expression<Func<User,bool>>? predicate, Func<User,Task> onRow, CancellationToken ct = default);
Task<List<TResult>> ForEachAsync<TResult>(Expression<Func<User,bool>>? predicate, Func<User,Task<TResult>> onRow, CancellationToken ct = default);
```

`InsertAsync` and `InsertMultipleAsync` skip auto-generated columns by default (the database fills them in). Pass `includeAutoFields: true` to write them yourself: the context equivalent of the builder's `WithAllFields()`. `InsertMultipleAsync` batches the whole collection into multi-row `INSERT`s (see [INSERT → Bulk insert](/database/0.3.2/querying/writing/insert)):

```csharp
await db.ExecuteTransactionAsync(async d => await d.Users.InsertMultipleAsync(newUsers));
```

`ForEachAsync` streams rows while the connection is open. Process each row inside the callback:

```csharp
await db.ExecuteAsync(d => d.Users.ForEachAsync(u => u.IsActive, async user =>
{
    await SendWelcomeAsync(user);
}));
```

The `ForEachAsync<TResult>` overload projects each streamed row and returns the results, collected inside the scope so nothing lazy escapes the connection:

```csharp
var names = await db.ExecuteAsync(d =>
    d.Users.ForEachAsync(u => u.IsActive, async user => await ResolveDisplayNameAsync(user)));
```

> **WARNING** PostgreSQL connections do not support multiple active result sets. Issuing another command on the same context while a `ForEachAsync` stream is open (or running operations in parallel within one scope) throws a clear `InvalidOperationException`. Buffer with `ToListAsync`, apply changes after the stream completes, or use a separate scope.

> **NOTE** If a query or `ForEachAsync` stream is still active when the delegate returns (typically a missing `await`), the scope throws `InvalidOperationException` instead of letting an opaque error surface from the commit. Await every database call in the delegate: `async ctx => await ctx.Users.ForEachAsync(...)`.

## Connection lifetime

`ConnectionLifetime.PerScope` (default) opens one connection per scope and reuses it. With scoped DI that's one connection per request. `ConnectionLifetime.PerOperation` opens a fresh connection per operation, relying on Npgsql pooling.

> **NOTE** A transaction always pins a single connection regardless of this setting: a transaction cannot span connections. Nested `ExecuteTransactionAsync` calls join the ambient transaction, and only the outermost commits.

## Stored procedures & raw ADO

Generated stored-procedure methods still take a `DbConnection`. Inside a scope, reach the scope's connection through the escape hatch:

```csharp
var rows = await db.ExecuteAsync(d => d.WithConnectionAsync(conn =>
    Procedures.GetUserLoginByUsername(conn, username).ToListAsync()));
```

`WithConnectionAsync` has both a `Task<TResult>` and a void (`Task`) overload. The context also exposes `DynamicTable<T>(string tableName)` for runtime-named tables (see [Declaring dynamic tables](/database/0.3.2/dynamic-tables/declaring)).

## Diagnostics

A transaction scope opens a parent span (`TRANSACTION (postgresql)`) and every command nests under it. The context carries the `ILogger` and diagnostics options from DI, so SQL logging works without any static configuration. See [Diagnostics & OpenTelemetry](/database/0.3.2/observability/diagnostics).
</content>