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.

updated 5 Jun 20263 min readv0.3.0View as Markdown

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.

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:

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

AddAuthDbContext takes an optional configurator for connection behavior:

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.

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:

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:

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:

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.

← previousConnections & DI

next →Transactions