TCIS.Persistence.Dapper 1.0.0-rc.19

TCIS.Persistence.Dapper

The read path of the TCIS ecosystem: a Dapper wrapper with self-managed connection lifetime, built-in pagination, multiple result sets in a single round trip, and an escape hatch for the awkward cases.

Use TCIS.Persistence.EntityFrameworkCore for writes. That package already registers this one — no manual wiring needed.

Table of contents

1. When to use Dapper, and when to use EF Core

EF Core is strong on the write path (change tracking, transactions, auditing) but generates poor SQL for read queries with many joins. Dapper gives you full control — along with full responsibility for tenant isolation, which EF otherwise handles for you (see section 9).

2. Installation and registration

dotnet add package TCIS.Persistence.Dapper

Option 1 — alongside EF Core (recommended)

AddTCorePersistence already registers IDbConnectionFactory and IDapperContext. Nothing else to do.

Option 2 — standalone (read-only service)

using Microsoft.Data.SqlClient;

builder.Services.AddOptions<PersistenceOptions>()
    .Bind(builder.Configuration.GetSection("ConfigurationStore"))
    .ValidateDataAnnotations()
    .ValidateOnStart();

builder.Services.AddScoped<IDbConnectionFactory>(sp =>
    new DefaultDbConnectionFactory(
        sp.GetRequiredService<IOptions<PersistenceOptions>>(),
        cs => new SqlConnection(cs)));

builder.Services.AddScoped<IDapperContext, DapperContext>();

3. Basic queries

public sealed class VesselReportQuery(IDapperContext db)
{
    private const string Sql = """
        SELECT v.Id, v.VesselName, v.Eta, b.BerthCode
        FROM   VesselVisits v
        JOIN   Berths b ON b.Id = v.BerthId
        WHERE  v.Eta >= @From AND v.Eta < @To
        ORDER  BY v.Eta
        """;

    public Task<IEnumerable<VesselVisitDto>> RunAsync(DateOnly from, DateOnly to, CancellationToken ct)
        => db.QueryAsync<VesselVisitDto>(Sql, new { From = from, To = to }, ct: ct);
}

Every method accepts param, commandTimeout and ct:

var count = await db.ExecuteScalarAsync<int>(
    "SELECT COUNT(1) FROM VesselVisits WHERE Status = @Status",
    new { Status = "ARRIVED" },
    commandTimeout: 60,
    ct: ct);

Always pass values through an anonymous parameter object; never concatenate them into the SQL — that is a SQL injection hole (DB-034).

4. Pagination

private const string CountSql = "SELECT COUNT(1) FROM VesselVisits WHERE Status = @Status";

private const string DataSql = """
    SELECT Id, VesselName, Eta
    FROM   VesselVisits
    WHERE  Status = @Status
    ORDER  BY Eta                       -- REQUIRED: without ORDER BY the page is undefined
    OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY
    """;

PagedResult<VesselVisitDto> page = await db.QueryPageAsync<VesselVisitDto>(
    CountSql, DataSql,
    pageIndex: 2, pageSize: 20,
    param: new { Status = "ARRIVED", Offset = 20, PageSize = 20 },
    ct: ct);

PagedResult<T> exposes TotalPages, HasPreviousPage and HasNextPage out of the box.

Two optimisations are built in: one connection serves both queries, and the data query is skipped entirely when TotalCount is 0.

⚠️ Both queries run on the same connection but not in a shared transaction, so TotalCount and Items are two snapshots taken at two moments. A write landing between them makes the total disagree with the rows returned — acceptable for a list screen, not acceptable when the number is used for reconciliation. When you need a consistent figure, use ExecuteCustomAsync with a transaction (section 7).

5. Multiple result sets in one round trip

Instead of three trips to the database, make one:

const string Sql = """
    SELECT Id, VesselName FROM VesselVisits WHERE Id = @Id;
    SELECT Id, ContainerNo FROM Containers  WHERE VisitId = @Id;
    SELECT Id, Amount      FROM Charges     WHERE VisitId = @Id;
    """;

var (visits, containers, charges) =
    await db.QueryMultipleAsync<VesselDto, ContainerDto, ChargeDto>(Sql, new { Id = visitId }, ct: ct);

Overloads exist for 2, 3 and 4 result sets. The order of the type parameters must match the order of the SELECT statements.

6. Multi-mapping — joining tables

const string Sql = """
    SELECT o.Id, o.OrderNo, c.Id, c.CustomerName
    FROM   Orders o
    JOIN   Customers c ON c.Id = o.CustomerId
    """;

var orders = await db.QueryAsync<OrderDto, CustomerDto, OrderDto>(
    Sql,
    map: (order, customer) => { order.Customer = customer; return order; },
    splitOn: "Id",          // the column where the second object starts
    ct: ct);

splitOn names the first column of the next object — it defaults to "Id". For more objects, separate the names with commas: splitOn: "Id,Id".

7. ExecuteCustom — the escape hatch

For anything the standard surface does not cover: a complex GridReader, a bulk insert, or several statements inside one transaction.

// Count and fetch within the SAME snapshot
var page = await db.ExecuteCustomAsync(async conn =>
{
    using var tx = ((DbConnection)conn).BeginTransaction(IsolationLevel.Snapshot);

    var total = await conn.ExecuteScalarAsync<int>(CountSql, param, tx);
    var items = await conn.QueryAsync<VesselVisitDto>(DataSql, param, tx);

    tx.Commit();
    return new PagedResult<VesselVisitDto> { TotalCount = total, Items = items };
}, ct);

The connection is handed to the callback already open, and is closed automatically when the callback finishes — including when it throws. Do not dispose it yourself.

8. Connection strings and read replicas

DefaultDbConnectionFactory picks the string in this order:

ReadConnectionString  (when set and not blank)
   ↓ absent
ConnectionString
   ↓ also absent
TInvalidConfigException — code CONFIG_MISSING → HTTP 500

The check uses IsNullOrWhiteSpace, so "ReadConnectionString": "" in appsettings still falls back correctly to the primary string.

Pointing ReadConnectionString at a replica means accepting replication lag: a read issued right after a write may return stale data. A "write then read it back to display it" flow should read from the primary string.

In the multi-tenant Pluggable stack the connection string does not come from appsettings at all — it is resolved per request from the Tenant Store. See TCIS.Pluggable.Persistence.*.

9. ⚠️ Two things you must know

9.1. Dapper has none of EF Core's tenant filtering

IDapperContext emits raw SQL. EF Core's Global Query Filter takes no part in this path — the SQL you write is the SQL that runs.

// ❌ DATA LEAK — reads across every tenant
await db.QueryAsync<OrderDto>("SELECT * FROM Orders");

// ✅ Add the tenant predicate yourself
await db.QueryAsync<OrderDto>(
    "SELECT * FROM Orders WHERE TenantId = @TenantId",
    new { TenantId = workContext.Tenant.TenantId });

On Tier 1 the remaining line of defence is Row-Level Security in the database — and it only defends if the DBA has enabled it. On Tier 3 nothing stops it: a perfectly innocent-looking query reads across tenants. That is why this is a rule, not a suggestion.

9.2. Dapper does not take part in an IUnitOfWork transaction

Every call opens its own connection and closes it again. Inside an open EF transaction, a Dapper call:

• cannot see data EF has written but not committed;
• is not rolled back when the transaction is abandoned;
• may block until CommandTimeout expires if it reads rows the EF transaction holds locks on — and because this is not a deadlock, the database will not break it up.

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(sql, param, transaction: tx);

The upside of this design: because the two sides use separate sessions, the read path runs in parallel with the write path without competing for a connection.

10. Pitfalls

Inputs are validated at the boundary: a blank or null sql, or a null callback, throws TValidationException (GUARD_EMPTY / GUARD_NULL → HTTP 422) carrying the provider name, instead of a NullReferenceException from deep inside Dapper.

11. Writing tests

Use a shared SQLite in-memory database (cache=shared) so each call still opens its own connection — exactly as DapperContext does — while seeing the same data:

var cs = $"DataSource=file:{Guid.NewGuid():N}?mode=memory&cache=shared";

using var keepAlive = new SqliteConnection(cs);   // keeps the database alive
keepAlive.Open();
keepAlive.Execute("CREATE TABLE rows (Id INTEGER PRIMARY KEY, Name TEXT NOT NULL);");

var db = new DapperContext(new SqliteFactory(cs));
var rows = await db.QueryAsync<Row>("SELECT Id, Name FROM rows");

SQLite returns INTEGER columns as long. Use a class with property setters, not a positional record — Dapper only coerces types on the setter path; the constructor path demands an exact signature match.

See TCIS.Persistence.Dapper.Tests for a fuller example: queries, pagination, multiple result sets, parallel execution, and connection release on the failure path.