TCIS.Persistence.EntityFrameworkCore 1.0.0-rc.19

TCIS.Persistence.EntityFrameworkCore

The standard write path of the TCIS ecosystem: IUnitOfWork, IGenericRepository<T>, the Specification pattern, audit stamping, and database exception classification — built on Entity Framework Core.

Use this package for writes. Use TCIS.Persistence.Dapper for complex reads. This package registers both.

Table of contents

1. Installation and registration

dotnet add package TCIS.Persistence.EntityFrameworkCore

appsettings.json

{
  "ConfigurationStore": {
    "ConnectionString": "Server=db;Database=Tcis;User Id=app;Password=***;TrustServerCertificate=True",
    "ReadConnectionString": "Server=db-replica;Database=Tcis;User Id=app_ro;Password=***;TrustServerCertificate=True"
  }
}

ConnectionString is required — if it is missing the application fails to start (ValidateOnStart) instead of dying on the first request in production.

ReadConnectionString is optional; when omitted, the read path falls back to the primary string.

Program.cs

using Microsoft.Data.SqlClient;      // NOT System.Data.SqlClient (deprecated)
using Microsoft.EntityFrameworkCore;

builder.Services.AddTCorePersistence<AppDbContext>(
    configuration: builder.Configuration,
    dbConnectionCreator: cs => new SqlConnection(cs),                 // for Dapper
    dbOptionsAction: (sp, options) =>                                  // for EF Core
    {
        options.UseSqlServer(
            builder.Configuration["ConfigurationStore:ConnectionString"],
            sql => sql.CommandTimeout(30));
    });

The last two parameters are separate because the two paths differ: dbConnectionCreator produces a raw IDbConnection for Dapper, while dbOptionsAction configures the provider for EF Core.

What gets registered

DbContext is mapped through the factory sp => sp.GetRequiredService<TAppDbContext>(), so IUnitOfWork and the repositories share exactly one instance with code that injects AppDbContext directly.

2. IUnitOfWork + IGenericRepository<T>

public sealed class CreateOrderHandler(IUnitOfWork uow)
{
    public async Task<Guid> HandleAsync(CreateOrderCommand cmd, CancellationToken ct)
    {
        var repo = uow.Repository<Order>();

        if (await repo.AnyAsync(o => o.OrderNo == cmd.OrderNo, ct))
        {
            throw new TValidationException("GUARD_DUPLICATE_ORDER", $"Order {cmd.OrderNo} already exists.", "orders");
        }

        var order = new Order { Id = Guid.NewGuid(), OrderNo = cmd.OrderNo, CustomerCode = cmd.CustomerCode };
        repo.Add(order);

        await uow.SaveChangesAsync(ct);      // <- nothing reaches the database until this runs
        return order.Id;
    }
}

The full IGenericRepository<T> surface

// Reads
Task<T?>              GetByIdAsync(object id, ct);
Task<List<T>>         GetAllAsync(ct);
Task<List<T>>         GetPagedAsync(int page, int pageSize, ct);   // page is 1-based
Task<List<T>>         FindAsync(Expression<Func<T,bool>> predicate, ct);
Task<T?>              FirstOrDefaultAsync(Expression<Func<T,bool>> predicate, ct);
Task<bool>            AnyAsync(Expression<Func<T,bool>> predicate, ct);
Task<int>             CountAsync(Expression<Func<T,bool>>? predicate, ct);

// Reads driven by a Specification — see section 3
Task<T?>               GetEntityWithSpecAsync(ISpecification<T> spec, ct);
Task<IReadOnlyList<T>> ListWithSpecAsync(ISpecification<T> spec, ct);
Task<int>              CountWithSpecAsync(ISpecification<T> spec, ct);
Task<bool>             AnyWithSpecAsync(ISpecification<T> spec, ct);

// Writes — these do NOT save; you must call SaveChangesAsync
void Add(T entity);      void AddRange(IEnumerable<T> entities);
void Update(T entity);
void Delete(T entity);   void DeleteRange(IEnumerable<T> entities);

GetPagedAsync numbers pages from 1. Passing page = 0 or pageSize < 1 throws TValidationException (GUARD_INVALID_PAGE → HTTP 422) at the boundary, instead of emitting OFFSET -n ROWS and getting a SQL syntax error back from the database.

A dedicated repository for richer behaviour

Derive from GenericRepository<T> and register it — uow.Repository<T>() prefers your implementation automatically:

public sealed class OrderRepository(DbContext context) : GenericRepository<Order>(context)
{
    // Query() is the extension point: every read method goes through it
    protected override IQueryable<Order> Query()
        => base.Query().Where(o => !o.IsArchived);

    public Task<List<Order>> GetOverdueAsync(DateTimeOffset asOf, CancellationToken ct)
        => Query().Where(o => o.DueDate < asOf && o.Status != OrderStatus.Paid).ToListAsync(ct);
}

// Program.cs
builder.Services.AddScoped<IGenericRepository<Order>, OrderRepository>();

3. Specification — reusable queries

Use this when a filter is repeated across call sites, or when you need to bundle filtering, includes, ordering and paging into a single object you can pass around.

public sealed class OverdueOrdersSpec : BaseSpecification<Order>
{
    public OverdueOrdersSpec(string customerCode, int page, int pageSize)
        : base(o => o.CustomerCode == customerCode && o.Status == OrderStatus.Pending)
    {
        AddInclude(o => o.Lines);
        AddInclude(o => o.Customer);
        ApplyOrderByDescending(o => o.CreatedAt);
        ApplyPaging((page - 1) * pageSize, pageSize);
        ApplyAsNoTracking();                       // read-only -> skip change tracking
    }
}

// Usage
var spec  = new OverdueOrdersSpec("CUST-01", page: 2, pageSize: 20);
var items = await uow.Repository<Order>().ListWithSpecAsync(spec, ct);
var total = await uow.Repository<Order>().CountWithSpecAsync(spec, ct);

The order of application is fixed: filter → includes → ordering → paging.

⚠️ Paging must always be accompanied by ordering. ApplyPaging without ApplyOrderBy/ApplyOrderByDescending produces undefined results: row order is up to the database and may change between runs, so two consecutive pages can repeat rows or skip rows with no error at all. See DB-030 (Chapter 6).

ApplyAsNoTracking() is for read-only queries: skipping change tracking is faster and uses less memory. Do not use it when you intend to modify the entity and call SaveChangesAsync — EF will not see the changes.

4. Transactions

4.1. Most of the time you do NOT need an explicit transaction

SaveChangesAsync is already a transaction. EF Core opens one itself whenever it has to send more than one command to the database.

// ✅ ENOUGH — all of it commits together or none of it does
foreach (var e in plan.ToInsert) repo.Add(e);
foreach (var e in plan.ToUpdate) repo.Update(e);
await uow.SaveChangesAsync(ct);

4.2. When you actually need one

Only when several SaveChanges calls must succeed or fail together:

4.3. The canonical pattern

await uow.BeginTransactionAsync(ct);
try
{
    var order = new Order { OrderNo = cmd.OrderNo };
    uow.Repository<Order>().Add(order);
    await uow.SaveChangesAsync(ct);                 // first save — order.Id is now assigned

    foreach (var line in BuildLines(order.Id))      // logic that needs the Id above
        uow.Repository<OrderLine>().Add(line);

    await uow.CommitTransactionAsync(ct);           // final SaveChanges + Commit
}
catch
{
    await uow.RollbackTransactionAsync(ct);
    throw;
}

Three things that are easy to get wrong:

• CommitTransactionAsync already calls SaveChangesAsync. Do not call SaveChangesAsync immediately before it.
• The try/catch is still required, even though CommitTransactionAsync rolls back on its own when saving or committing fails. Reason: an exception thrown before control reaches the commit line (say BuildLines throws) means the commit never runs — without the catch, the transaction is only disposed when the scope ends, and database locks are held for that whole stretch.
• No manual rollback is needed after CommitTransactionAsync throws — it has already rolled back and released the transaction.

4.4. Transactions do not nest

await uow.BeginTransactionAsync(ct);
await uow.BeginTransactionAsync(ct);   // ❌ TValidationException: SYS_TRANSACTION_ALREADY_STARTED

EF Core does not support nested transactions. The second call used to return silently — the caller believed it had opened its own transaction, and its CommitTransactionAsync then committed the outer transaction, pushing work the outer scope had not finished down to the database.

Design consequence: transaction ownership belongs to the outermost orchestrator (the command handler), not to inner services. Inner services only Add / Update / SaveChangesAsync.

For a Pluggable pipeline, open the transaction around the whole pipeline at the call site; steps keep calling SaveChangesAsync as usual:

await uow.BeginTransactionAsync(ct);
try
{
    await pipelineEngine.ExecuteAsync(context, ct);   // steps still call SaveChanges normally
    await uow.CommitTransactionAsync(ct);
}
catch
{
    await uow.RollbackTransactionAsync(ct);
    throw;
}

4.5. Dapper does NOT take part in this transaction

IDapperContext opens its own connection for every call. Inside a transaction it does not see uncommitted data, is not rolled back with it, and can block until CommandTimeout expires if it reads rows the EF transaction is holding locks on.

When you need raw SQL inside the transaction, borrow EF's connection:

var conn = dbContext.Database.GetDbConnection();
var tx   = dbContext.Database.CurrentTransaction?.GetDbTransaction();
await conn.ExecuteAsync("UPDATE orders SET status = @s WHERE id = @id",
                        new { s = "PAID", id }, transaction: tx);

5. Audit stamping

⚠️ AuditSaveChangesInterceptor is NOT registered automatically by AddTCorePersistence. If you do not register it yourself, no audit field is ever populated, and nothing warns you. This step is mandatory.

builder.Services.AddScoped<AuditSaveChangesInterceptor>();

builder.Services.AddTCorePersistence<AppDbContext>(
    builder.Configuration,
    cs => new SqlConnection(cs),
    (sp, options) => options
        .UseSqlServer(connectionString)
        .AddInterceptors(sp.GetRequiredService<AuditSaveChangesInterceptor>()));

The interceptor takes identity from IUserContext, which TCIS.AspNetCore supplies through IWorkContext.

Two audit contracts — pick one per product

The platform serves existing products and new products side by side, which is why there are two sets of audit fields. This is deliberate, not duplication waiting to be cleaned up:

public sealed class Order : IAuditableEntity
{
    public Guid Id { get; set; }
    public string OrderNo { get; set; } = string.Empty;

    public DateTimeOffset  CreatedAt { get; set; }
    public string?         CreatedBy { get; set; }
    public DateTimeOffset? UpdatedAt { get; set; }
    public string?         UpdatedBy { get; set; }
}

How to choose: an entity belonging to a legacy product's schema uses IAuditEntity so the types line up with existing columns; an entity of a new product uses IAuditableEntity — DateTimeOffset carries the offset, and a string principal matches accounts issued by the IdP.

The two interfaces declare CreatedBy with different types (int? and string?), so an entity implementing both is forced into explicit interface implementation. In practice, don't: each entity picks exactly one set, according to the product it belongs to. The interceptor does support the both-interfaces case — it stamps both sets, leaving neither blank.

The interceptor only touches entities in the Added and Modified states; CreatedAt/CreatedBy are not overwritten on update.

6. Database exception classification

Turns raw database errors into a classification carrying an owner and a support tier — so alerts reach the right team instead of waking everybody.

builder.Services.AddExceptionClassification();          // TCIS.Observability.Classification
builder.Services.AddDatabaseExceptionClassification();  // this package — register it AFTER

It must be registered after AddExceptionClassification() so this precise rule wins over the coarse type-name rule.

7. Pitfalls

8. Writing tests

GenericRepository, EfUnitOfWork and the interceptor are all testable against SQLite in-memory, no Docker required:

var connection = new SqliteConnection("DataSource=:memory:");
connection.Open();                                  // keep it open for the lifetime of the test

var context = new AppDbContext(
    new DbContextOptionsBuilder<AppDbContext>().UseSqlite(connection).Options);
context.Database.EnsureCreated();

var uow  = new EfUnitOfWork(context, new ServiceCollection().BuildServiceProvider());
var repo = uow.Repository<Order>();

repo.Add(new Order { OrderNo = "ORD-001" });
await uow.SaveChangesAsync();

Assert.Equal(1, await context.Set<Order>().CountAsync());

This package's own test suite (TCIS.Persistence.EntityFrameworkCore.Tests) is a fuller example: repository cache keying by Type, transaction semantics, disposal lifetime, paging guards, and audit stamping.