LowCodeHub.Dapper 0.0.4

LowCodeHub.Dapper

A Dapper-first data-access package for SQL Server and PostgreSQL. Its application boundary is a concrete, typed DapperDbContext 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
{
    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
{
    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). Ordinary CLR collections keep their existing provider semantics; collections explicitly wrapped in Json<T> have the built-in translations below.

JSON arrays and complex values

Use Json<T> for a value stored or passed as one JSON document. The same wrapper handles int[], string[], Guid[], lists, arrays of objects, and individual complex objects:

using LowCodeHub.Dapper.Models;

var parameters = new
{
    CustomerId = 42,
    Name = "Order A",
    Ids = Json.From(new[] { 1, 2, 3 }),
    Items = Json.From(new[] { new OrderItem(7, 2), new OrderItem(8, 4) })
};

IReadOnlyList<OrderResult> results = await db.QueryStoredProcedureAsync<OrderResult>(
    new DapperStoredProcedure("dbo.save_order", parameters), ct);

public sealed record OrderItem(int ProductId, int Quantity);
public sealed class OrderResult
{
    public int CustomerId { get; set; }
    public string Name { get; set; } = "";
    public Json<int[]> Ids { get; set; } = null!;
    public Json<OrderItem[]> Items { get; set; } = null!;
}

Read the deserialized array through results[0].Ids.Value. No procedure DTO interface, TSelf, UDT, or per-type handler registration is required. Entity properties use the same Json<T> type for repository inserts, updates and materialization. Raw queries, scalar commands, streaming, sessions and multiple-result callbacks use the same handlers.

AddDapperSqlServer<TContext> and AddDapperPostgreSql<TContext> discover closed Json<T> properties and fields in the context assembly and loaded assemblies that reference this package. Typed command results, parameters, entity maps and Json.From also prepare types on first use, including closed generic DTOs. Discovery is cached and synchronized. This uses reflection and runtime generic construction; trimmed/Native AOT deployment is not supported by this mechanism. Assemblies used with direct Dapper calls outside the context should be loaded before registration.

SQL Server parameters are nvarchar(max) with unbounded size; Npgsql parameters are jsonb. The database signature must accept that representation. The package does not create or alter a procedure, table, database compatibility level, or UDT. For example, the four-input SQL Server procedure above can accept and return both arrays directly:

CREATE PROCEDURE dbo.save_order
    @CustomerId int,
    @Name nvarchar(100),
    @Ids nvarchar(max),
    @Items nvarchar(max)
AS
BEGIN
    SET NOCOUNT ON;
    -- Use OPENJSON(@Ids) to read primitive elements, or a typed schema for objects:
    -- SELECT ProductId, Quantity FROM OPENJSON(@Items)
    --     WITH (ProductId int, Quantity int);
    SELECT @CustomerId AS CustomerId, @Name AS Name, @Ids AS Ids, @Items AS Items;
END;

For PostgreSQL, declare the collection inputs as jsonb and consume them with jsonb_array_elements or jsonb_to_recordset. PostgreSQL procedures return values through OUT/INOUT parameters; use a function called with raw SELECT when the database contract returns a table. SQL Server JSON querying requires compatibility level 130 or later.

Querying JSON

JSON collections and scalar object members work in the typed LINQ/specification translator:

var orders = await db.Query<Order>()
    .Where(order => order.Ids.Value.Contains(7))
    .Where(order => order.Items.Value.Any(item => item.Quantity > 2))
    .Where(order => order.Settings.Value.Address.City == "Cairo")
    .Select(order => new
    {
        order.Id,
        ItemCount = order.Items.Value.Length,
        City = order.Settings.Value.Address.City,
        Address = Json.From(order.Settings.Value.Address)
    })
    .ToListAsync(ct);

Supported collection operations are primitive Contains, Any, All, Count, LongCount, array Length, and collection Count properties. Any, All, Count and LongCount accept element predicates; object predicates can traverse nested properties and collections. Both Enumerable and C# 14 array/span Contains forms are recognized. Comparer overloads and object equality via Contains are rejected. Keep Json<T> in structured projections (or use Json.From for a nested object/collection); projecting a whole bare complex .Value would bypass decoding and is rejected. Individual scalar members can be projected normally.

JSON serialization uses the fixed System.Text.Json defaults, including case-sensitive property names and numeric enums. JsonPropertyName is honored by serialization and query paths. Custom converters remain usable for storage and reading, but querying their custom shapes requires raw SQL. Dictionary/indexer traversal, collection indexing, JSON mutation functions, and SelectMany are also raw-SQL operations. SQL Server decimal member extraction uses decimal(38,18); use explicit SQL when another precision/scale is needed. String matching uses the database collation. SQL Server scalar string extraction supports values longer than 4,000 characters. A wrapped byte[] follows System.Text.Json's base64-string format, so it is not a queryable JSON array.

A null wrapper represents SQL NULL; Json.From<T?>(null) represents JSON null; an empty array represents []. These round-trip distinctly. Missing/null scalar paths yield SQL NULL, and nullable comparisons use two-valued boolean semantics. Missing, SQL-null and JSON-null collections behave as empty collections in queries: Any is false, All is true, and counts are zero. Stored documents should match their declared CLR shape; malformed documents or incompatible scalar values fail instead of being silently repaired.

JSON output parameters

Use the extensions on Dapper's existing DynamicParameters:

var parameters = new DynamicParameters();
parameters.Add("CustomerId", 42);
parameters.AddJson("Ids", new[] { 1, 2, 3 });
parameters.AddJsonOutput("Result");
parameters.AddJson("State", currentState, ParameterDirection.InputOutput);

await db.ExecuteStoredProcedureAsync(new DapperStoredProcedure("process_order", parameters), ct);
OrderSummary? result = parameters.GetJson<OrderSummary>("Result");

AddJson accepts an unwrapped value and treats a null input as SQL NULL. AddJsonOutput and input/output parameters receive the correct provider type and SQL Server size automatically. GetJson<T> returns the unwrapped T, including default(T) for SQL NULL. A JSON null requires a reference or nullable T, following System.Text.Json's rules; use a nullable scalar type when distinguishing null from the default value matters. It also decodes ordinary JSON string output parameters. Read outputs after the command completes and any result reader has been consumed/disposed. Dapper's ordinary Get<T> performs a cast and does not deserialize JSON. Like ordinary DynamicParameters, a parameter bag is not for concurrent command execution. Scalar return codes remain ordinary Dapper parameters.

User-registered parameter types

Bare arrays retain Dapper's SQL Server IN @Ids expansion and Npgsql's native array handling. Wrapping a value in Json.From is the explicit choice to send JSON. Do not register a global JSON handler for bare int[], string[], or Guid[]: that would replace their native behavior. Native collection LINQ predicates still require an application translator or raw SQL. For example, Npgsql already supports this input without a custom array handler:

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

options.AddTypeHandler(new MyDomainIdHandler()) remains available for domain IDs, ranges, spatial types, or another SqlMapper.TypeHandler<T>. AddTypeHandler(Type, ITypeHandler) supports runtime-selected types. Configured handlers are installed during database registration. Like SqlMapper.AddTypeHandler, registrations are process-wide: use stateless provider-aware handlers, and do not configure different serializers for the same CLR type in different contexts. The built-in Json<T> handlers use one fixed serializer contract on both providers. 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. Automatic JSON handler discovery also requires reflection and runtime generic construction. The package does not promise trimmed/Native AOT support.

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

Use set.Add(entity) and set.AddRange(entities) directly when no insert specification is needed. SaveChangesAsync flushes only that set's queued inserts, in one transaction. It is not a context-wide change tracker or unit of work: updates/deletes execute immediately, and changes to previously read objects are not tracked. Use InTransactionAsync/IDapperSession for a transaction spanning several commands. [DatabaseGenerated(DatabaseGeneratedOption.None)] preserves application-assigned conventional integer keys.

Numeric casts that safely widen a value are emitted as provider SQL casts, so (double)x.A / x.B uses floating-point division. Narrowing casts with different rounding/overflow rules across providers fail with NotSupportedException. Numeric precision and ordering still follow the database's types and collation. DISTINCT projections can order by their projected members; use raw SQL when the required ordering needs an additional subquery.

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 are reported as skipped. SQLite tests always run for fast execution and semantic coverage.

$env:LOWCODEHUB_DAPPER_INTEGRATION_TESTS = '1'
dotnet test test/LowCodeHub.Dapper.Tests/LowCodeHub.Dapper.Tests.csproj -c Release

Version 0.0.4 adds typed JSON values, automatic handler discovery, JSON outputs and JSON LINQ operations. It also fixes numeric cast translation, negated nullable comparisons, connection cleanup after interceptor failures, conventional non-generated keys, scalar projection counts, DISTINCT paging/order SQL, ordered zero-row counts and raw-command cancellation propagation.