Skip to content

PepperX.QueryForge.Dapper

Directory actions

More options

Directory actions

More options

Latest commit

 

History

History

PepperX.QueryForge.Dapper

README.md

Part of PepperX Ecosystem

PepperX.QueryForge Logo

PepperX.QueryForge.Dapper

NuGet Version .NET License

Why this exists

Tired of writing the same "list" endpoint for the hundredth time? A raw SELECT * dumped straight to the client, with no real filtering, no pagination, no security — just an unstructured blob, and every bit of that missing logic pushed onto the frontend to deal with?

PepperX.QueryForge.Dapper turns that into one call. Declare what data you want — filters, sorting, paging, grouping — and, optionally, the rules that keep it safe. Point it at a table, a view, a stored procedure, or a function, custom parameters and all. You always get the data back through the same standardized, predictable contract — fast, and secure by default.

It compiles your query into parameterized SQL at call time and runs it with Dapper. Nothing is deployed to your database, so it needs no schema permissions — and the same code works on SQL Server, PostgreSQL, MySQL/MariaDB, Oracle and SQLite.

At a glance

  • 🎯 One Query in, one QueryResult<T> out — for tables, views, stored procedures, and table-valued functions alike.
  • 🌳 Native hierarchical grouping — multi-level key / count / items trees, generated by the query engine itself.
  • 🛡️ Built-in validation — silently strip or hard-block denied columns, tables, schemas, and oversized page sizes before they ever touch SQL.
  • 🔌 Two ways to build a query — accept it as JSON from a client, or build it entirely in C# with a fluent API. Same models either way.
  • 🗄️ Five database engines — SQL Server, PostgreSQL, MySQL/MariaDB, Oracle and SQLite, from one codebase. The engine is inferred from the connection you pass.
  • ⚡ Nothing to deploy — no stored procedures, no DDL permissions, no startup migration. SQL is built per call and every value is a parameter.

Install

dotnet add package PepperX.QueryForge.Dapper

Register it

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

ConnectionFactory is only needed for the overloads that manage their own connection — if every call hands in its own IDbConnection, AddQueryForgeDapper() with no arguments is enough.

All five dialects are registered for you. The engine is chosen from the connection type at call time, so an application talking to more than one database needs no extra configuration:

await svc.QueryAsync<User>(new NpgsqlConnection(pgConnectionString), query);   // PostgreSQL syntax
await svc.QueryAsync<User>(new SqlConnection(mssqlConnectionString), query);   // SQL Server syntax

Two ways to execute

Inject IDapperQueryService (shown throughout this README) — or, if you'd rather not add a service dependency, call the IDbConnection extension method directly:

app.MapPost("/api/users/query-raw", async (Query clientQuery, IDbConnection connection) =>
{
    var dapperQuery = DapperQueryBuilder
        .FromBase(clientQuery)
        .ForObject("TestUsers", "dbo", DapperObjectType.Table)
        .Build();

    return await connection.QueryForgeAsync<TestUser>(dapperQuery); // same QueryResult<T>, no service needed
});

The model used in every example below:

public class TestUser
{
    public int UserId { get; set; }
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public string Country { get; set; } = string.Empty;
    public string Department { get; set; } = string.Empty;
    public decimal Score { get; set; }
    public bool IsActive { get; set; }
}

Example 1 — Flat results

A DapperQuery has 6 things you can control: Criteria, Paging, SelectColumns, SortColumns, GroupByColumns, and Object. This example touches every one of them except grouping (that's Example 2).

A. Client-driven, with validation

The frontend sends a plain Query — notice it has no object property, so it's physically impossible for a client to spoof which table gets hit.

POST /api/users/query
{
  "criteria": {
    "logic": 0,                    // Logic.And -> combine the groups below with AND (only one group here)
    "groups": [
      {
        "logic": 0,                // Logic.And -> Country = 'Germany' AND IsActive = true
        "conditions": [
          { "columnName": "Country", "operator": 0, "value": "Germany" },  // operator 0 = Equals
          { "columnName": "IsActive", "operator": 0, "value": true }       // operator 0 = Equals
        ]
      }
    ]
  },
  "paging": { "size": 5, "number": 1 },
  "selectColumns": ["UserId", "FirstName", "LastName", "Email", "Country", "Department", "Score"],
  "sortColumns": [
    { "columnName": "Score", "sortOrder": 1 }   // sortOrder 1 = Descending
  ]
}

Logic: 0=And, 1=Or, 2=AndNot, 3=OrNot  |  ConditionOperator 0=Equals  |  SortOrder: 0=Ascending, 1=Descending

app.MapPost("/api/users/query", async (Query clientQuery, IDapperQueryService svc) =>
{
    var dapperQuery = DapperQueryBuilder
        .FromBase(clientQuery)                                   // Criteria + Paging + SelectColumns + SortColumns from the client
        .ForObject("TestUsers", "dbo", DapperObjectType.Table)   // Object: locked server-side, never client-controlled
        .Build();

    dapperQuery.Validate(rules =>
    {
        rules.Select(c => c.Deny("Email"));   // never leak this column, even if asked for
        rules.PageSize(p => p.Max(50));       // no data-dump attacks
    }, QueryValidationMode.SilentStrip);

    return await svc.QueryAsync<TestUser>(dapperQuery);
});

B. Fully backend-built, no client input at all

Same DapperQuery, built end-to-end in C# with the fluent builder — useful for internal jobs, reports, or endpoints where the frontend shouldn't control the shape of the query at all.

app.MapGet("/api/users/top-active", async (IDapperQueryService svc) =>
{
    var dapperQuery = DapperQueryBuilder
        .Where(new QueryCriteria(
            logic: Logic.And,
            groups: [ new ConditionGroup([ new Condition("IsActive", ConditionOperator.Equals, true) ]) ]))
        .Select("UserId", "FirstName", "LastName", "Country", "Score")
        .Sort(new SortDescriptor("Score", SortOrder.Descending))
        .Page(size: 10, number: 1)
        .ForObject("TestUsers", "dbo", DapperObjectType.Table)
        .Build();

    return await svc.QueryAsync<TestUser>(dapperQuery);
});

The result — always the same shape

{
  "meta": { "total": { "rows": 4, "pages": 1 }, "type": "Flat" },
  "models": [
    { "userId": 16, "firstName": "First16", "lastName": "Last16", "country": "Germany", "department": "IT", "score": 66.00 },
    { "userId": 11, "firstName": "First11", "lastName": "Last11", "country": "Germany", "department": "Marketing", "score": 61.00 },
    { "userId": 6,  "firstName": "First6",  "lastName": "Last6",  "country": "Germany", "department": "Sales", "score": 56.00 },
    { "userId": 1,  "firstName": "First1",  "lastName": "Last1",  "country": "Germany", "department": "HR", "score": 51.00 }
  ]
}

Validation: two modes, your choice

Validate() always takes a QueryValidationMode:

Mode Behavior Best for
SilentStrip (used above) Quietly removes denied/disallowed columns and clamps paging to your limits. The request still succeeds. Public APIs — never break the client over a permissions mismatch.
ThrowException Throws a QueryValidationException listing every violated rule the moment one is found. Internal APIs / strict environments where an invalid request should fail loudly. 
try
{
    dapperQuery.Validate(rules => rules.Select(c => c.Deny("Email")), QueryValidationMode.ThrowException);
    return Results.Ok(await svc.QueryAsync<TestUser>(dapperQuery));
}
catch (QueryValidationException ex)
{
    // ex.InvalidProperties -> ["Email"]
    return Results.ValidationProblem(ex.InvalidProperties.ToDictionary(x => x, x => new[] { "Denied by security policy" }));
}

Example 2 — Grouped results

Add GroupByColumns and the flat table turns into a nested tree — with row counts at every level — computed by the engine, not in application code.

A. Client-driven, with validation

POST /api/users/grouped-query
{
  "criteria": {
    "groups": [
      { "conditions": [ { "columnName": "IsActive", "operator": 0, "value": true } ] }  // operator 0 = Equals
    ]
  },
  "paging": { "size": 5, "number": 1 },
  "selectColumns": ["UserId", "FirstName", "LastName", "Score"],
  "sortColumns": [ { "columnName": "Score", "sortOrder": 1 } ],   // sortOrder 1 = Descending
  "groupByColumns": [
    { "columnName": "Country", "sortOrder": 0 },      // sortOrder 0 = Ascending
    { "columnName": "Department", "sortOrder": 0 }    // sortOrder 0 = Ascending
  ]
}
app.MapPost("/api/users/grouped-query", async (Query clientQuery, IDapperQueryService svc) =>
{
    var dapperQuery = DapperQueryBuilder
        .FromBase(clientQuery)
        .ForObject("TestUsers", "dbo", DapperObjectType.Table)
        .Build();

    dapperQuery.Validate(rules =>
    {
        rules.GroupBy(c => c.Allow("Country", "Department"));  // only these two levels are groupable
        rules.PageSize(p => p.Max(20));                         // caps top-level groups per page
    }, QueryValidationMode.SilentStrip);

    return await svc.QueryAsync<TestUser>(dapperQuery);
});

B. Fully backend-built, no client input at all

app.MapGet("/api/users/by-country", async (IDapperQueryService svc) =>
{
    var dapperQuery = DapperQueryBuilder
        .Select("UserId", "FirstName", "LastName", "Score")
        .Sort(new SortDescriptor("Score", SortOrder.Descending))
        .GroupBy(
            new GroupByDescriptor("Country", SortOrder.Ascending),
            new GroupByDescriptor("Department", SortOrder.Ascending))
        .Page(5, 1)
        .ForObject("TestUsers", "dbo", DapperObjectType.Table)
        .Build();

    return await svc.QueryAsync<TestUser>(dapperQuery);
});

The result — a real hierarchy, straight from the engine

{
  "meta": { "total": { "rows": 4, "pages": 1 }, "type": "Grouped" },
  "groups": [
    {
      "key": "Canada",
      "count": 9,
      "subGroups": [
        { "key": "HR", "count": 5, "items": [ { "userId": 9, "firstName": "First9", "lastName": "Last9", "score": 59.00 } ] },
        { "key": "IT", "count": 4, "items": [ { "userId": 4, "firstName": "First4", "lastName": "Last4", "score": 54.00 } ] }
      ]
    },
    {
      "key": "Germany",
      "count": 12,
      "subGroups": [
        { "key": "IT",        "count": 4, "items": [ { "userId": 16, "firstName": "First16", "lastName": "Last16", "score": 66.00 } ] },
        { "key": "Marketing", "count": 3, "items": [ { "userId": 11, "firstName": "First11", "lastName": "Last11", "score": 61.00 } ] },
        { "key": "Sales",     "count": 3, "items": [ { "userId": 6,  "firstName": "First6",  "lastName": "Last6",  "score": 56.00 } ] },
        { "key": "HR",        "count": 2, "items": [ { "userId": 1,  "firstName": "First1",  "lastName": "Last1",  "score": 51.00 } ] }
      ]
    }
  ]
}

Built for enterprise data grids

QueryForge is architecturally purpose-built to be the backend counterpart for advanced UI data grids like DevExtreme (dxDataGrid), AG Grid, and Kendo UI.

These grids send rich loadOptions — nested filter groups, multi-level grouping, sorting, paging — that just need a thin frontend mapper to become a QueryForge Query. From there, QueryForge does the heavy lifting the grid actually demands:

Grid feature QueryForge capability
Complex filtering Deeply nested AND / OR / AND NOT / OR NOT groups (Criteria)
Multi-level grouping Native key / count / items hierarchy trees, any number of levels
Server-side paging Accurate row/page totals, on flat results or on top-level groups
Dynamic multi-column sorting SortColumns, independent direction per column

Database objects — not just tables

Point ForObject at a table, a view, a table-valued function, or a stored procedure — filters, sorting, and paging still apply on top, and each can take its own parameters:

// Table-Valued Function
var tvfQuery = DapperQueryBuilder.New()
    .ForObject("tvf_GetUsersByTenant", "dbo", DapperObjectType.TVF,
        new Dictionary<string, object?> { { "TenantId", 1 } })
    .Build();

// Stored Procedure
var spQuery = DapperQueryBuilder.New()
    .ForObject("usp_GetUserReport", "dbo", DapperObjectType.SP,
        new Dictionary<string, object?> { { "IncludeDeleted", false } })
    .Page(20, 1)
    .Build();

Where things stand

Execution providers

Provider Package Status
Dapper PepperX.QueryForge.Dapper ✅ Released
Entity Framework Core PepperX.QueryForge.EFCore ✅ Released
In-Memory included in PepperX.QueryForge ✅ Released

Database engine support (via the Dapper provider)

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

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

Need an engine that isn't listed? Implement ISqlDialect — it is a small interface covering identifier quoting, parameter prefix, paging syntax and LIKE escaping — and register it with services.AddQueryForgeDialect(new MyDialect()).

Portability guarantees

Two things differ between engines by default. QueryForge pins both, so moving a query from one database to another does not silently change the answer:

  • Null ordering. PostgreSQL and Oracle sort nulls last ascending; SQL Server, MySQL and SQLite sort them first. QueryForge standardises on nulls first ascending and nulls last descending, by emitting an explicit NULLS FIRST/NULLS LAST where the engine's default differs.
  • Loosely-typed filter values. A value arriving from JSON is often text even when the column is numeric or a date. Permissive engines coerce it; PostgreSQL rejects integer > text outright. QueryForge discovers each column's real type from the result set and coerces the value before binding it — so "30" works everywhere, and the comparison uses the column's type and its indexes.

Both were found by running the test suites against live PostgreSQL and MySQL servers rather than only asserting generated SQL.

Oracle specifics

Oracle needs a little more care than the others, and QueryForge handles most of it for you:

  • Named parameter binding is forced on. ODP.NET binds parameters positionally by default, which would silently pair :p0 and :p1 by insertion order rather than by name. QueryForge sets BindByName on the command, so you do not have to.
  • Derived tables are aliased without AS. Oracle rejects FROM (...) AS x, so the compiler emits the bare form that every engine accepts.
  • 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 instead.
  • Empty strings are NULL in Oracle. This is the database's own behaviour, not something QueryForge can paper over: a filter for Equals "" behaves as IS NULL there and as an empty-string match everywhere else.
  • Paging needs Oracle 12c or later, where OFFSET … FETCH NEXT became available.

Upgrading from 1.x

2.0 replaces the T-SQL stored-procedure engine with a SQL compiler written in C#. That is what makes the other four databases possible, and it changes a few things:

1.x 2.0
usp_QueryForge* procedures deployed into your database at startup Nothing is deployed; SQL is compiled per call
DapperExecutionApproach (RawQuery / UseReadySp / DevelopAndUseSp) Removed — there is one execution path
DDL permissions needed for the default mode No schema permissions needed at all
DapperQueryObject.Schema defaulted to "dbo" Defaults to empty, meaning "use the dialect's default" (dbo on SQL Server, public on PostgreSQL)

To upgrade, delete the Approach line from your AddQueryForgeDapper call. Everything else — IDapperQueryService, DapperQuery, DapperQueryBuilder, QueryForgeAsync, validation — is unchanged. You can drop the old usp_QueryForge, usp_QueryForge_BuildWhere and usp_QueryForge_ExecGrouped procedures from your database; nothing calls them any more.

Two behaviour improvements worth knowing about, because they are also behaviour changes:

  • Comparisons now use the column's real type. The old engine inlined every value as text, so Age > 9 compared strings and ranked 9 above 30. Values are now parameters, and numbers, dates and booleans compare correctly.
  • Stored procedure results are filtered in the application. A procedure's result set cannot be composed into a larger SELECT portably, so its rows are materialized and then filtered, sorted, paged and grouped in memory. The result is identical; for a very large procedure result set the data transfer is not. Tables, views and functions are unaffected — those push everything down to the database.

🤝 Contributing & License

This project is part of the PepperX Ecosystem. Licensed under the MIT License — see the LICENSE file for details.