# 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 — and 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.0/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`. |
| `IUserSet` / `UserSet`, … | A mockable data-access seam per table. |
| `AuthDbFactory` | The DI-resolvable `ISocigyDatabaseFactory<IAuthDb>`. |
| `AddAuthDbContext()` | DI registration extension. |

## 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: **commits** when it returns, **rolls back** if it throws.

Both have a `<TResult>` overload that returns a value.

```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<bool>        InsertAsync(User entity);
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);
```

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

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

## 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; only the outermost commits.

## Stored procedures & raw ADO

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

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

## 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.0/observability/diagnostics).