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 20264 min readv0.3.2View 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 (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.

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:

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

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<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 INSERTs (see INSERT → Bulk insert):

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

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

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

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:

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

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