PepperX.QueryForge.Dapper 1.0.6

PepperX.QueryForge.Dapper

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.

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.
• ⚡ Zero boilerplate — the background service deploys everything the engine needs on startup.

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

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:

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:

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

Database engine support (via the Dapper provider)

Legend: ✅ Released  |  🔧 In Development  |  📋 Planned

🤝 Contributing & License

This project is part of the PepperX Ecosystem. Licensed under the MIT License.