Dapper Provider

Dapper Provider

PepperX.QueryForge.Dapper compiles a query into parameterized SQL at call time and executes it with Dapper. Five engines from one codebase, nothing deployed to your database.

• The SQL it emits, statement by statement: Dapper: Generated SQL
• What differs per engine: Dapper: Dialects

---

Install and register

dotnet add package PepperX.QueryForge.Dapper

The package references Dapper and Microsoft.Extensions.DependencyInjection.Abstractions — and no database driver. Bring the one you use:

dotnet add package Microsoft.Data.SqlClient      # SQL Server
dotnet add package Npgsql                        # PostgreSQL
dotnet add package MySqlConnector                # MySQL / MariaDB
dotnet add package Oracle.ManagedDataAccess.Core # Oracle
dotnet add package Microsoft.Data.Sqlite         # SQLite

builder.Services.AddQueryForgeDapper();

That registers all five dialects, an executor per engine (each owning its schema cache), and IDapperQueryService as scoped.

If you want the overload that manages its own connection, supply a factory:

builder.Services.AddQueryForgeDapper(options =>
{
    options.ConnectionFactory = sp =>
        new SqlConnection(sp.GetRequiredService<IConfiguration>().GetConnectionString("Default"));
});

public class DapperQueryForgeOptions
{
    public Func<IServiceProvider, IDbConnection>? ConnectionFactory { get; set; }
}

ConnectionFactory is the only option. Omit it if every call hands in its own connection; calling the connectionless overload without it throws InvalidOperationException.

---

Three ways to execute

1. The injected service, with your connection

public interface IDapperQueryService
{
    Task<QueryResult<TModel>> QueryAsync<TModel>(
        IDbConnection connection, DapperQuery query,
        int? commandTimeout = null, IDbTransaction? transaction = null);

    Task<QueryResult<TModel>> QueryAsync<TModel>(
        DapperQuery query,
        int? commandTimeout = null, IDbTransaction? transaction = null);
}

app.MapPost("/api/users/query", async (Query q, IDbConnection conn, IDapperQueryService svc) =>
{
    var dq = DapperQueryBuilder.FromBase(q).ForObject("Users", "dbo").Build();
    return await svc.QueryAsync<User>(conn, dq);
});

2. The injected service, letting it manage the connection

var result = await svc.QueryAsync<User>(dq);

It calls ConnectionFactory, opens the connection if it is not already open, runs the query, and disposes it. Requires ConnectionFactory to be configured.

3. Straight off an IDbConnection

using PepperX.QueryForge.Dapper;

var result = await connection.QueryForgeAsync<User>(dq);

No service dependency. This works even in an application that never called AddQueryForgeDapper() — the package keeps a default registry with all five built-in dialects. Calling AddQueryForgeDapper() replaces that registry, which is how a custom dialect reaches this path too.

Timeouts and transactions

Every overload takes both:

using var tx = connection.BeginTransaction();

var result = await svc.QueryAsync<User>(connection, dq, commandTimeout: 30, transaction: tx);

commandTimeout is in seconds and is passed to every statement the query issues, including the schema probe.

---

How the engine is chosen

From the connection type name, at call time. One registration serves an application talking to several databases:

Connection type name	Engine
SqlConnection	SQL Server
NpgsqlConnection	PostgreSQL
MySqlConnection, MariaDbConnection	MySQL / MariaDB
OracleConnection	Oracle
SqliteConnection	SQLite

await svc.QueryAsync<User>(new NpgsqlConnection(pg), query);      // LIMIT / OFFSET, "quoted"
await svc.QueryAsync<User>(new SqlConnection(mssql), query);      // OFFSET…FETCH, [bracketed]

Matching on the name rather than the type keeps the package free of a reference to every driver. An unrecognised connection throws:

NotSupportedException: QueryForge cannot infer a database engine from connection type 'X'.

Register a dialect for it — see Extending QueryForge.

---

The target object

public record DapperQueryObject(
    string Name,
    string Schema = "",
    DapperObjectType Type = DapperObjectType.Auto,
    IReadOnlyDictionary<string, object?>? Parameters = null);

public enum DapperObjectType { Auto = 0, Table = 1, View = 2, TVF = 3, SP = 4 }

Leave Schema empty to take the dialect's default — dbo on SQL Server, public on PostgreSQL, none on MySQL, Oracle or SQLite. That is what keeps one ForObject call portable.

Database objects

// Table or view — Auto, Table and View are all "select from it"
.ForObject("Users", "dbo")
.ForObject("vw_ActiveUsers", "dbo", DapperObjectType.View)

// Table-valued function — arguments are POSITIONAL, in dictionary order
.ForObject("tvf_GetUsersByTenant", "dbo", DapperObjectType.TVF,
    new Dictionary<string, object?> { ["TenantId"] = 1 })

// Stored procedure
.ForObject("usp_GetUserReport", "dbo", DapperObjectType.SP,
    new Dictionary<string, object?> { ["IncludeDeleted"] = false })

Filters, sorting, paging, projection and grouping apply on top of all of them.

Engine	Tables & views	Table-valued functions	Stored procedures
SQL Server	✅	✅	✅
PostgreSQL	✅	✅	✅
MySQL / MariaDB	✅	— no TVFs in MySQL	✅
Oracle	✅	✅ pipelined functions	— use a pipelined function
SQLite	✅	—	—

A dash means the engine has no such concept. QueryForge throws a NotSupportedException naming the alternative rather than generating SQL that cannot work.

Stored procedures behave differently

A procedure's result set cannot be composed into a larger SELECT portably. So for DapperObjectType.SP, QueryForge:

1. calls the procedure with its arguments,
2. materializes the whole result set,
3. applies filtering, sorting, paging, projection and grouping in the application, using the same InMemoryQueryEngine the in-memory provider uses.

The QueryResult<T> is identical. The data transfer is not — for a procedure returning a very large set, every row crosses the wire before being filtered. Tables, views and functions push everything down to the database and are unaffected.

Procedure call syntax per engine:

Engine	Emitted	Argument binding
SQL Server	EXEC [dbo].[usp_X] @Name = @p0, …	named
PostgreSQL	SELECT * FROM "public"."f"(name => @p0, …)	named
MySQL	CALL db.p(@p0, …)	positional
Oracle	not supported	—
SQLite	not supported	—

---

Schema discovery and caching

Before compiling, the provider needs to know the target's real columns and their types. It runs:

SELECT * FROM <target> WHERE 1 = 0

and reads the resulting reader's field names and field types. That gives both the column whitelist and the types used for value coercion.

Reading the object's own result set rather than a catalog view means discovery is identical on every engine and is correct for views and functions whose columns are computed.

Caching: per provider | schema | name | type, in a ConcurrentDictionary, for the lifetime of the registry — which is a singleton, so effectively the process. An object's shape changing under a running application is a deployment event, not a request-time concern.

If you change a table's shape without restarting, the cached whitelist is stale and newly added columns will be dropped from queries until the process restarts. There is no cache-invalidation API.

Stored procedures skip this entirely — their path never probes.

---

Statement counts

Query	Statements
flat	2 — a COUNT(*), and the page of rows
grouped	3 — a count of distinct keys, the page of keys, the rows for those keys
stored procedure	1 — the call; everything else happens in memory
first query against a new object	+1 — the one-off schema probe

No statement is ever issued per row.

---

Parameter binding

Parameters are named p0, p1, … in the order the compiler encounters them, numbered per statement. They are rendered with the dialect's prefix (@ everywhere except Oracle, which uses :).

Binding is forced to be by name. ODP.NET binds positionally unless BindByName is set, which would silently pair :p0 and :p1 by declaration order rather than by name and compare the wrong values. QueryForge sets the flag reflectively — any command type exposing a writable bool BindByName gets it, and anything else is left alone — so no driver becomes a dependency of the package.

Parameter names are never derived from column names, which avoids reserved-word collisions such as Oracle's ORA-01745.

---

Full example

app.MapPost("/api/users/query", async (Query clientQuery, IDapperQueryService svc) =>
{
    var dq = DapperQueryBuilder
        .FromBase(clientQuery)
        .ForObject("Users", "dbo", DapperObjectType.Table)
        .Build();

    dq.Validate(rules =>
    {
        rules.Select(c => c.Deny("PasswordHash"));
        rules.Where(c  => c.Deny("PasswordHash"));
        rules.PageSize(p => p.Max(100));
    }, QueryValidationMode.SilentStrip);

    return await svc.QueryAsync<User>(dq, commandTimeout: 30);
})
.Accepts<Query>("application/json")
.Produces<QueryResult<User>>();

Posting:

{
  "criteria": { "groups": [ { "conditions": [
      { "columnName": "Country",  "operator": 0, "value": "Germany" },
      { "columnName": "IsActive", "operator": 0, "value": true } ] } ] },
  "paging": { "size": 5, "number": 1 },
  "selectColumns": ["UserId", "FirstName", "LastName", "Score"],
  "sortColumns": [ { "columnName": "Score", "sortOrder": 1 } ]
}

Runs, on SQL Server:

SELECT COUNT(*) FROM [dbo].[Users] WHERE ([Country] = @p0 AND [IsActive] = @p1)

SELECT [UserId], [FirstName], [LastName], [Score] FROM [dbo].[Users]
WHERE ([Country] = @p0 AND [IsActive] = @p1)
ORDER BY [Score] DESC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY

and returns:

{
  "meta": { "total": { "rows": 4, "pages": 1 }, "type": "Flat" },
  "models": [ { "userId": 16, "firstName": "First16", "lastName": "Last16", "score": 66.00 } ]
}

---

Materialization

Dapper maps rows to TModel using its own conventions: public settable properties matched to column names case-insensitively. QueryForge does not interfere.

• A model property with no matching column is left at its default.
• A column with no matching property is ignored.
• Use Dapper's own SqlMapper.SetTypeMap or a custom type handler for non-trivial mapping.

For grouped queries, the tree is built from the materialized models using the same case-insensitive property lookup — so GroupByColumns naming a column that maps to a differently named property will find nothing. Keep property names matching column names, or use a view that aliases them.

---

Oracle specifics

Oracle needs more care than the others. Most of it is handled for you:

• Named binding is forced on, as described above.
• Derived tables are aliased without AS — Oracle rejects FROM (…) AS x, so the compiler emits the bare form that every engine accepts.
• Nulls are ordered explicitly with NULLS FIRST / NULLS LAST, because Oracle's default differs from most engines.
• Paging uses OFFSET … FETCH NEXT, which requires Oracle 12c or later.

What you must handle yourself:

• Object names are case-sensitive as written. Oracle folds unquoted identifiers to upper case and QueryForge quotes what it is given, so write object names the way Oracle stored them — usually upper case. Column names are discovered from the result set and are always correct.
• Stored procedures are not supported. Oracle returns result sets through REF CURSOR output parameters, which cannot be expressed as portable command text. Wrap the logic in a pipelined function and query it with DapperObjectType.TVF.
• Empty strings are NULL in Oracle. This is the database's own behaviour: a filter for Equals "" behaves as IS NULL there and as an empty-string match everywhere else. QueryForge cannot paper over it.

---

Thread safety

• DapperQueryService is registered scoped but holds no per-request state.
• The registry, executors and schema caches are singletons and are safe for concurrent use — the cache is a ConcurrentDictionary.
• SqlQueryCompiler and the dialects are stateless.
• IDbConnection is not thread-safe. Do not share one across concurrent calls; that is ADO.NET's rule, not QueryForge's.