LowCodeHub.Dapper 0.0.1

LowCodeHub.Dapper

A Dapper-first data-access package for SQL Server and PostgreSQL. Its primary API is 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. IDapperRepository<TEntity> is an optional convenience API, not the package boundary.

Registration and mapping

using LowCodeHub.Dapper.Extensions;

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

    options.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");
    });
});

Use AddDapperSqlServer for SQL Server. Without an explicit section name, the configuration overloads read Dapper:SqlServer or Dapper:PostgreSql.

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 for multiple databases

Use a typed context when one application talks to more than one database, provider, tenant group, or read/write role. TContext is an application-defined marker used as a DI registration key. It is not an EF DbContext, is never constructed, needs no members or base class, and does not contain the entity mappings itself:

public sealed class CatalogDatabase;
public sealed class ReportingDatabase;

Register each marker independently. Its connection source, provider dialect, timeout, mappings, and application translators are isolated from registrations made for other markers:

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

    options.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");
    });
});

builder.Services.AddDapperSqlServer<ReportingDatabase>(options =>
{
    options.ConnectionString =
        builder.Configuration.GetConnectionString("Reporting")!;

    options.Model.ApplyConfigurationsFromAssembly(
        typeof(ReportingDatabase).Assembly);
});

Inject the marker-specific interface so the dependency cannot accidentally execute against a different database:

public sealed class ProductReader(IDapperDbContext<CatalogDatabase> catalog)
{
    public Task<List<Product>> ListActiveAsync(CancellationToken cancellationToken)
        => catalog.Query<Product>()
            .Where(product => product.IsActive)
            .OrderBy(product => product.Name)
            .ToListAsync(cancellationToken);
}

public sealed class SalesReportReader(
    IDapperDbContext<ReportingDatabase> reporting)
{
    public Task<IReadOnlyList<SalesRow>> RunAsync(
        CommandDefinition command,
        CancellationToken cancellationToken)
        => reporting.QueryAsync<SalesRow>(command, cancellationToken);
}

The same entity CLR type may be mapped differently in two typed contexts. The model is selected from the context that creates the query, so Product can target catalog.products in one context and another table or schema in another context.

The optional repository is typed in the same order: context first, entity second.

public sealed class ProductWriter(
    IDapperRepository<CatalogDatabase, Product> products);

OpenSessionAsync and InTransactionAsync return IDapperSession, not a generic session. That session was opened by the selected IDapperDbContext<TContext> and remains bound to its exact connection, provider, mapping model, and current transaction:

await catalog.InTransactionAsync(async (session, cancellationToken) =>
{
    await session.ExecuteAsync(insertCommand, cancellationToken);

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

    return inserted;
}, cancellationToken: ct);

A marker can be registered only once. Registering the same marker twice throws immediately, which prevents a later registration from silently replacing its database. Contexts and repositories are scoped services; inject them into scoped application services or resolve them from a DI scope.

For one database, use AddDapperSqlServer(...) or AddDapperPostgreSql(...) without a marker and inject IDapperDbContext or IDapperRepository<TEntity>. The non-generic registration internally uses DapperDefaultContext; applications normally do not reference that type.

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",
    model => model.ApplyConfigurationsFromAssembly(
        typeof(CatalogDatabase).Assembly));

builder.Services.AddDapperSqlServer<ReportingDatabase>(
    builder.Configuration,
    "Dapper:Databases:Reporting",
    model => model.ApplyConfigurationsFromAssembly(
        typeof(ReportingDatabase).Assembly));

Named sections are also available on the non-generic registration overloads. Configuration is bound during service registration and then validated, so a missing connection source or invalid timeout fails immediately rather than on the first command.

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

builder.Services.AddDapperPostgreSql(options =>
{
    options.ConnectionString = connectionString;
    options.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 IDapperDbContext and write normal LINQ:

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

public sealed class ProductReader(IDapperDbContext db)
{
    public Task<List<ProductSummary>> ListAsync(CancellationToken ct)
        => db.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 currently use the raw Dapper layer below. That layer is part of the main API so complex SQL is not forced through a lowest-common-denominator repository abstraction.

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(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), 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 IDapperDbContext 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.

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

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

Optional repository/specifications

IDapperRepository<TEntity> remains available for entity CRUD, typed specifications, paging, queued inserts with SaveChangesAsync, generated keys, and set-based updates/deletes. Existing DapperSpecification builders use the same expression translator and registered application translators. Prefer IDapperDbContext.Query<TEntity>() for ordinary reads and the raw/session APIs for advanced Dapper work.

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.