LowCodeHub.Dapper 0.0.2

LowCodeHub.Dapper

A Dapper-first data-access package for SQL Server and PostgreSQL. Its application boundary is a concrete, typed DapperDbContext<TContext> with context-owned DapperSet<TEntity> properties and an independent IQueryable provider; it has no EF Core dependency. Raw Dapper commands, native provider values, type handlers, unbuffered streams, multiple result sets, owned sessions, and transactions remain available without injecting repository infrastructure into feature services.

Registration and mapping

Define a real application database type, like an EF DbContext. Put entity mappings and named sets on that class, then inject the concrete type everywhere:

using LowCodeHub.Dapper.Abstractions;
using LowCodeHub.Dapper.Mapping;

public sealed class CatalogDatabase : DapperDbContext<CatalogDatabase>
{
    public DapperSet<Product> Products => Set<Product>();

    protected override void OnModelCreating(DapperModelBuilder model)
    {
        model.Entity<Product>(entity =>
        {
            entity.ToTable("products", "catalog");
            entity.HasKey(product => product.Id, generatedOnAdd: true);
            entity.Property(product => product.Id, "id");
            entity.Property(product => product.Name, "product_name");
            entity.Property(product => product.IsActive, "is_active");
        });
    }
}

Register its provider and connection in DI, matching EF's AddDbContext configuration pattern:

using LowCodeHub.Dapper.Extensions;

builder.Services.AddDapperPostgreSql<CatalogDatabase>(options =>
{
    options.ConnectionString =
        builder.Configuration.GetConnectionString("Catalog")!;
    options.CommandTimeoutSeconds = 30;
});

Use AddDapperSqlServer<TContext> for SQL Server.

Exactly one connection source is required. A configured DbDataSource is preferred for Npgsql; an application factory supports access tokens, credential rotation, and per-request tenant routing:

options.UseDataSource(npgsqlDataSource); // the application owns and disposes the data source

// Or, resolved in the consuming context's DI scope:
options.UseConnectionFactory(services =>
    services.GetRequiredService<ITenantDatabaseRouter>().CreateConnection());

Typed contexts and context-owned sets

The concrete context is always the application dependency. DapperSet<TEntity> keeps repository and specification operations behind that database boundary, like DbSet<TEntity> without EF tracking:

public sealed class ProductReader(CatalogDatabase database)
{
    public Task<IReadOnlyList<ProductSummary>> ListAsync(
        string namePrefix,
        CancellationToken cancellationToken)
        => database.Products.ListAsync(
            new ActiveProductSummaries(namePrefix), cancellationToken);

    public Task<List<Product>> QueryAsync(CancellationToken cancellationToken)
        => database.Query<Product>()
            .Where(product => product.IsActive)
            .OrderBy(product => product.Name)
            .ToListAsync(cancellationToken);
}

Use database.Set<TEntity>() directly when a named property adds no clarity. Repository infrastructure is internal; application constructors receive only their concrete database type.

This leaves one database dependency in an application repository:

public sealed class PaymentRepository(PaymentDatabase database) : IPaymentRepository
{
    public Task<IReadOnlyList<GetSchoolConsumerBundlesResponse>> ListAsync(
        CancellationToken cancellationToken)
        => database.Bundles.ListAsync(
            new SchoolConsumerBundlesSpecification(), cancellationToken);
}

OnModelCreating is evaluated once and cached for the context type. Keep it deterministic and do not read scoped request/tenant state there. The concrete context itself is scoped and may receive normal application services through constructor injection.

When one application talks to multiple databases, providers, tenant groups, or read/write roles, define a separate context class for each. Every context has its own connection source, provider dialect, timeout, mapping model, and application translators. The same entity CLR type may map to different tables in different contexts.

OpenSessionAsync and InTransactionAsync return IDapperSession. The session was opened by the concrete typed context and remains bound to its exact connection, provider, mapping model, and current transaction:

await catalog.InTransactionAsync(async (session, cancellationToken) =>
{
    await session.ExecuteAsync(new CommandDefinition(
        insertSql,
        parameters,
        cancellationToken: cancellationToken));

    Product inserted = await session.Query<Product>()
        .SingleAsync(product => product.Id == productId, cancellationToken);

    return inserted;
}, cancellationToken: ct);

A context type can be registered only once. Registering it twice throws immediately, preventing a later registration from silently replacing its database. Contexts and their sets are scoped; do not use a context concurrently or resolve it from the root service provider.

The short IConfiguration overloads bind the conventional Dapper:SqlServer or Dapper:PostgreSql section. Pass a section name when each typed context has its own configuration:

{
  "Dapper": {
    "Databases": {
      "Catalog": {
        "ConnectionString": "Host=localhost;Database=catalog",
        "CommandTimeoutSeconds": 30
      },
      "Reporting": {
        "ConnectionString": "Server=localhost;Database=reporting;...",
        "CommandTimeoutSeconds": 60
      }
    }
  }
}

builder.Services.AddDapperPostgreSql<CatalogDatabase>(
    builder.Configuration,
    "Dapper:Databases:Catalog");

builder.Services.AddDapperSqlServer<ReportingDatabase>(
    builder.Configuration,
    "Dapper:Databases:Reporting");

Connection configuration is bound and validated during service registration. The context model is created and validated when the context is first resolved.

Mappings can also live in reusable configuration classes and be discovered from an assembly:

public sealed class ProductConfiguration : IDapperEntityConfiguration<Product>
{
    public void Configure(DapperEntityMapBuilder<Product> entity)
    {
        entity.ToTable("products", "catalog");
        entity.HasKey(product => product.Id, generatedOnAdd: true);
        entity.Property(product => product.Id, "id");
        entity.Property(product => product.Name, "product_name");
    }
}

public sealed class CatalogDatabase : DapperDbContext<CatalogDatabase>
{
    public DapperSet<Product> Products => Set<Product>();

    protected override void OnModelCreating(DapperModelBuilder model)
        => model.ApplyConfigurationsFromAssembly(
            typeof(ProductConfiguration).Assembly);
}

Concrete configuration classes may be non-public but must have a parameterless constructor. A configuration may implement IDapperEntityConfiguration<TEntity> for more than one entity.

Standard data annotations work without any model-builder registration:

[Table("products", Schema = "catalog")]
public sealed class Product
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Column("id")]
    public long Id { get; set; }

    [Column("product_name")]
    public string Name { get; set; } = string.Empty;

    [NotMapped]
    public string DisplayName => $"#{Id}: {Name}";
}

Convention mapping uses public properties, the entity type name as the table, and Id or {EntityName}Id as the key. [Table], [Column], [Key], [DatabaseGenerated], and [NotMapped] are honored. Conventions and attributes are applied first; later fluent or scanned configurations override them.

Custom IQueryable

Inject the concrete application context and write normal LINQ:

using LowCodeHub.Dapper.Abstractions;
using LowCodeHub.Dapper.Querying;

public sealed class ProductReader(CatalogDatabase database)
{
    public Task<List<ProductSummary>> ListAsync(CancellationToken ct)
        => database.Query<Product>()
            .Where(product => product.IsActive)
            .OrderBy(product => product.Name)
            .Select(product => new ProductSummary(product.Id, product.Name, product.Price))
            .ToListAsync(ct);
}

The query object implements IOrderedQueryable<T> and IAsyncEnumerable<T>. It keeps the root expression, preserves it through CreateQuery<T> projections, compiles it into parameterized SQL, then lets Dapper materialize the result. There is no client-evaluation fallback: an unsupported operator throws NotSupportedException and names the missing translation.

The initial provider supports:

• Where, Select, chained projections, OrderBy/ThenBy, Distinct, Skip, and Take
• First, FirstOrDefault, Single, SingleOrDefault, Count, LongCount, and Any
• Sum, Min, Max, and Average for non-paged scalar queries
• comparisons, boolean/null composition, captured parameters, string Contains/StartsWith/EndsWith, arithmetic, coalescing, conditionals, ToLower, ToUpper, and Trim
• constructor, record, anonymous-type, member-initializer, and scalar projections
• sync LINQ execution plus ToListAsync, ToArrayAsync, AsAsyncEnumerable, FirstAsync, FirstOrDefaultAsync, SingleAsync, SingleOrDefaultAsync, CountAsync, LongCountAsync, and AnyAsync, SumAsync, MinAsync, MaxAsync, and AverageAsync

Inspect a query without executing it:

DapperQueryPlan plan = query.ToDapperQuery(); // SQL + DynamicParameters
string sql = query.ToQueryString();           // parameterized SQL only

This is intentionally not an EF clone. It does not provide tracking, change detection, navigation Include, or hidden lazy loading. Operators that need multi-source/subquery translation, such as Join and GroupBy, should use a database stored procedure or function for medium/complex work. Short, direct, single-purpose SELECT statements may use the raw Dapper layer below.

The compiler first creates a provider-independent query model and then renders dialect SQL. Applications can register IDapperMethodCallTranslator implementations for explicit semantics that belong to the application or provider. A translator receives safe child translation, captured-value parameterization, and the active provider. This is the opt-in path for translating an application method to = ANY(@ids); the built-in provider still never assigns meaning to CLR collections.

User-registered parameter types

The library does not assign SQL semantics to arrays, lists, JSON, ranges, spatial values, or other provider-specific types. For example, collection Contains is not translated to either PostgreSQL ANY or SQL Server IN. Applications choose the SQL and register the corresponding Dapper type handler while registering the package.

An opt-in PostgreSQL int[] example:

using System.Data;
using Dapper;
using Npgsql;
using NpgsqlTypes;

builder.Services.AddDapperPostgreSql<CatalogDatabase>(options =>
{
    options.ConnectionString = connectionString;
    options.AddTypeHandler(new PostgreSqlInt32ArrayHandler());
});

public sealed class PostgreSqlInt32ArrayHandler : SqlMapper.TypeHandler<int[]>
{
    public override void SetValue(IDbDataParameter parameter, int[]? value)
    {
        if (parameter is not NpgsqlParameter npgsqlParameter)
        {
            throw new InvalidOperationException("This handler requires Npgsql.");
        }

        npgsqlParameter.NpgsqlDbType = NpgsqlDbType.Array | NpgsqlDbType.Integer;
        npgsqlParameter.Value = value ?? (object)DBNull.Value;
    }

    public override int[] Parse(object value) => (int[])value;
}

The application then writes its intended provider SQL explicitly:

IReadOnlyList<Product> rows = await db.QueryAsync<Product>(new CommandDefinition(
    "SELECT * FROM catalog.products WHERE id = ANY(@Ids)",
    new { Ids = new[] { 3, 8, 13 } },
    cancellationToken: ct));

The same registration mechanism works for domain IDs, JSON documents, PostgreSQL ranges, spatial types, or any other SqlMapper.TypeHandler<T>. AddTypeHandler(Type, ITypeHandler) is available for runtime-selected types. Like SqlMapper.AddTypeHandler, registrations are process-wide. Dapper ICustomQueryParameter, DbString, dynamic parameters, output parameters, stored procedures, and provider-specific values can also be supplied through the raw APIs or an owned session connection.

Stored procedures

Stored procedures are first-class on both the concrete context and IDapperSession:

var parameters = new DynamicParameters();
parameters.Add("Id", productId);
parameters.Add("Affected", dbType: DbType.Int32, direction: ParameterDirection.Output);

await db.ExecuteStoredProcedureAsync(
    new DapperStoredProcedure("catalog.activate_product", parameters), ct);

int affected = parameters.Get<int>("Affected");

ExecuteStoredProcedureAsync, QueryStoredProcedureAsync<T>, ExecuteStoredProcedureScalarAsync<T>, and QueryStoredProcedureMultipleAsync<T> preserve Dapper dynamic/custom parameters, output and return values, flags, timeout, cancellation, result grids, and the current session transaction. Procedure naming and behavior remain provider-specific; PostgreSQL functions invoked through SELECT should use the raw query APIs.

Raw Dapper, streaming, and multiple results

CommandDefinition is the command currency, so timeout, command type, flags, cancellation, DynamicParameters, and callbacks are retained.

The concrete context and session raw command methods take only the CommandDefinition; place the cancellation token in that definition once.

var result = await db.QueryMultipleAsync(
    new CommandDefinition("select count(*) from products; select * from products limit 10", cancellationToken: ct),
    async (grid, _) =>
    {
        int count = await grid.ReadSingleAsync<int>();
        List<Product> products = (await grid.ReadAsync<Product>()).AsList();
        return new ProductPage(count, products);
    });

The callback is deliberate: Dapper's GridReader owns the live reader/command until all grids have been consumed. The context disposes it and its connection only after the callback finishes.

Unbuffered queries keep their connection alive for the whole async enumeration:

await foreach (Product product in db.QueryUnbufferedAsync<Product>(sql, parameters, cancellationToken: ct))
{
    await HandleAsync(product, ct);
}

For multi-mapping, bulk extensions, prepared/provider commands, or any other Dapper API, use the owned connection directly:

Order order = await db.WithConnectionAsync(async (connection, token) =>
    (await connection.QueryAsync<Order, Customer, Order>(
        sql,
        (order, customer) => { order.Customer = customer; return order; },
        splitOn: "customer_id"))
    .Single(), ct);

Transactions and sessions

await db.InTransactionAsync(async (session, token) =>
{
    await session.ExecuteAsync(new CommandDefinition(
        insertSql,
        product,
        cancellationToken: token));

    // Generated LINQ uses this same connection and active transaction.
    Product inserted = await session.Query<Product>()
        .SingleAsync(item => item.ExternalId == product.ExternalId, token);

    // The real connection and transaction are available for every Dapper extension.
    await session.Connection.ExecuteAsync(updateSql, parameters, session.Transaction);
    return product.Id;
}, cancellationToken: ct);

The helper commits after the callback succeeds and rolls back on failure. OpenSessionAsync is available when transaction boundaries or connection lifetime must be controlled explicitly.

Diagnostics and production behavior

Generated LINQ, repository operations, raw commands, multiple grids, stored procedures, and streams emit activities from LowCodeHub.Dapper and metrics from the meter with the same name. Register application hooks with AddDapperInterceptor<TInterceptor>(). Interceptors see command metadata but not parameter values. SQL text is excluded from activities by default; opt in with IncludeCommandTextInDiagnostics = true. Configure SlowQueryThreshold to mark slow commands.

The package does not automatically retry commands. Retrying streaming operations or commands inside a transaction can duplicate work, so resilience policy remains explicit at the application boundary. Direct DbConnection access remains available for provider bulk APIs and other Dapper extensions.

Assembly mapping scans use reflection and are marked RequiresUnreferencedCode. Trimmed/AOT applications should use attributes, direct ApplyConfiguration, or fluent mapping instead.

LINQ support boundary

Unsupported expressions fail with NotSupportedException; there is no client-evaluation fallback.

Context sets and specifications

DapperSet<TEntity> provides entity CRUD, typed specifications, paging, queued inserts with SaveChangesAsync, generated keys, and set-based updates/deletes without exposing repository interfaces in application constructors. DapperSpecification builders use the same expression translator and registered application translators as Query<TEntity>().

Database-context and repository interfaces are implementation details. Application code must derive and inject one concrete typed context and access repository behavior through its named DapperSet<TEntity> properties.

Provider verification

The test suite contains SQL Server and PostgreSQL Testcontainers coverage for LINQ, custom types, transactions, multiple results, data sources, and stored procedures. Set LOWCODEHUB_DAPPER_INTEGRATION_TESTS=1 when Docker is available; otherwise those tests exit without starting containers. SQLite tests always run for fast execution and semantic coverage.