PepperX.QueryForge.Dapper 1.0.5

PepperX.QueryForge.Dapper

PepperX.QueryForge.Dapper is the high-performance execution provider for the PepperX.QueryForge core library. It translates abstract query models into optimized SQL and executes them against Microsoft SQL Server using Dapper, seamlessly handling hierarchical grouping, complex filtering, and Stored Procedure/TVF integration.

📑 Table of Contents

• ✨ Features
• 🖥️ Frontend Grid Compatibility
• 🧩 Provider Ecosystem
• 📦 Installation
• 🚀 Quick Start
• 🏗️ Core Concepts
• 🔍 Advanced Filtering
• 🌳 Hierarchical Grouping
• 🛡️ Security & Validation
• 🗄️ Database Objects (Views, TVFs, SPs)
• ⚙️ Configuration & Execution Approaches
• 📚 Enum Reference

✨ Features

• Provider-Agnostic Core: Define query intent once. Execute anywhere — Dapper today, EF Core and InMemory coming soon.
• Native Hierarchical Grouping: Generate deeply nested key/count/items JSON trees directly from the database engine.
• Bulletproof Security: Built-in SilentStrip validation prevents schema enumeration, data dumps, and malicious column requests.
• Zero Boilerplate: Beautiful Fluent API for building queries in C# or accepting them as raw JSON from frontends.
• Automated Schema Deployment: Background IHostedService automatically deploys required Stored Procedures on app startup.

🖥️ Frontend Grid Compatibility

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

While these grids send complex loadOptions (nested filters, multi-level grouping, summaries) that require a lightweight frontend mapper to translate into QueryForge's Query JSON structure, QueryForge natively handles the exact heavy-lifting SQL execution these grids demand:

🧩 Provider Ecosystem

QueryForge is designed as a modular ecosystem. Install only the provider you need — the core library is included automatically as a dependency.

Execution Providers

Database Engine Support (via Dapper Provider)

Legend: ✅ Released  |  🔧 In Development  |  📋 Planned

📦 Installation

Install the provider package for your execution engine. The core PepperX.QueryForge library is automatically included.

dotnet add package PepperX.QueryForge.Dapper

🚀 Quick Start

1. Register in Program.cs

using PepperX.QueryForge.Dapper;
using Microsoft.Data.SqlClient;

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

2. Build and Execute

Define your Model first

public record User(
    int Id,
    string Name,
    string Email,
    string Country,
    bool IsActive
);

Simple Query (HTTP GET) Perfect for simple paging and sorting. Uses [AsParameters] to bind from the query string.

// GET /api/users?Paging.Size=10&Paging.Number=1&SortColumns[0].ColumnName=Score
app.MapGet("/api/users", async ([AsParameters] Query baseQuery, IDapperQueryService queryService) =>
{
    var dapperQuery = DapperQueryBuilder
        .FromBase(baseQuery)
        .ForObject("Users", "dbo", DapperObjectType.Table)
        .Build();

    return await queryService.QueryAsync<User>(dapperQuery);
});

Complex Query (HTTP POST)

💡 Pro-Tip: For complex filtering involving nested Criteria groups, HTTP POST is the industry standard for CQRS read-models. It avoids URL length limits and complex query-string binding issues.

// POST /api/users/query
app.MapPost("/api/users/query", async (Query baseQuery, IDapperQueryService queryService) =>
{
    var dapperQuery = DapperQueryBuilder
        .FromBase(baseQuery)
        .ForObject("Users", "dbo", DapperObjectType.Table)
        .Build();

    return await queryService.QueryAsync<User>(dapperQuery);
});

🏗️ Core Concepts

The Base Query vs. Provider DapperQuery

To prevent frontends from spoofing target tables (e.g., sending "object": {"name": "AdminPasswords"}), your API endpoints should accept the base PepperX.QueryForge.Query class. The backend then securely upgrades it to a DapperQuery.

// Frontend sends this JSON (Notice: No 'object' property — it's physically impossible to spoof)
// { "paging": { "size": 10 }, "sortColumns": [{"columnName": "Score", "sortOrder": 1}] }

var dapperQuery = DapperQueryBuilder
    .FromBase(baseQuery)             // Maps frontend criteria, paging, and sorting
    .ForObject("Users", "dbo")       // Backend hardcodes the secure target
    .Build();

🔍 Advanced Filtering

QueryForge supports deeply nested logical groups (AND, OR, AND NOT, OR NOT) and a comprehensive set of comparison operators.

C# Builder Syntax

var criteria = new QueryCriteria(
    logic: Logic.And,
    groups: [
        new ConditionGroup(
            logic: Logic.Or,
            conditions: [
                new Condition("Country", ConditionOperator.Equals, "USA"),
                new Condition("Country", ConditionOperator.Equals, "Germany")
            ]
        ),
        new ConditionGroup(
            logic: Logic.AndNot,
            conditions: [
                new Condition("Role", ConditionOperator.Equals, "Guest"),
                new Condition("Age", ConditionOperator.LessThan, 21)
            ]
        )
    ]
);

var query = DapperQueryBuilder.New()
    .ForObject("Users")
    .Where(criteria)
    .Build();

Equivalent JSON Payload (from Frontend)

{
  "criteria": {
    "logic": 0,
    "groups": [
      {
        "logic": 1,
        "conditions": [
          { "columnName": "Country", "operator": 0, "value": "USA" },
          { "columnName": "Country", "operator": 0, "value": "Germany" }
        ]
      },
      {
        "logic": 2,
        "conditions": [
          { "columnName": "Role", "operator": 0, "value": "Guest" },
          { "columnName": "Age", "operator": 6, "value": 21 }
        ]
      }
    ]
  }
}

Generated SQL

WHERE (Country = N'USA' OR Country = N'Germany') 
  AND NOT (Role = N'Guest' AND Age < N'21')

Null Checks & Ranges

{
  "criteria": {
    "groups": [
      {
        "conditions": [
          { "columnName": "DeletedAt", "operator": 0, "value": null },
          { "columnName": "Score", "operator": 10, "value": 75.5, "valueTo": 95.0 }
        ]
      }
    ]
  }
}

Generates: WHERE DeletedAt IS NULL AND Score BETWEEN N'75.5' AND N'95.0'

🌳 Hierarchical Grouping

By specifying GroupByColumns, the engine transforms a flat tabular result into a deeply nested hierarchical tree, complete with aggregated counts at every level.

var query = DapperQueryBuilder.New()
    .ForObject("Users")
    .GroupBy(
        new GroupByDescriptor("Country", SortOrder.Ascending),
        new GroupByDescriptor("Department", SortOrder.Ascending)
    )
    .Page(5, 1) // Paginates the TOP-LEVEL groups (Countries)
    .Build();

var result = await queryService.QueryAsync<User>(query);

Resulting JSON Structure

{
  "meta": { "total": { "rows": 2, "pages": 1 }, "type": "Grouped" },
  "groups": [
    {
      "key": "USA",
      "count": 45,
      "subGroups": [
        {
          "key": "IT",
          "count": 20,
          "items": [ { "userId": 1, "name": "Alice" }, { "userId": 2, "name": "Bob" } ]
        },
        {
          "key": "HR",
          "count": 25,
          "items": [ { "userId": 3, "name": "Charlie" } ]
        }
      ]
    },
    {
      "key": "UK",
      "count": 30,
      "subGroups": [
        {
          "key": "Sales",
          "count": 30,
          "items": [ ... ]
        }
      ]
    }
  ]
}

🛡️ Security & Validation

Never trust frontend query payloads. QueryForge includes a powerful validation engine that can either silently sanitize malicious requests or throw strict errors.

Silent Strip (Recommended for Public APIs)

Silently removes denied columns and clamps pagination limits without throwing errors.

dapperQuery.Validate(rules =>
{
    // Dapper-specific: Restrict which tables and schemas can be queried
    rules.Object(o => o.AllowSchema("dbo").AllowTable("Users", "Products"));
    
    // Base: Prevent sensitive column leakage
    rules.Select(c => c.Deny("PasswordHash", "SecretKey", "DeletedAt"));
    
    // Base: Prevent data-dump attacks
    rules.PageSize(p => p.Max(100));
}, QueryValidationMode.SilentStrip);

Strict Validation (Recommended for Internal APIs)

Throws a QueryValidationException containing a list of all violated rules.

try 
{
    dapperQuery.Validate(rules => rules.Select(c => c.Deny("PasswordHash")), QueryValidationMode.ThrowException);
}
catch (QueryValidationException ex)
{
    // ex.InvalidProperties contains ["PasswordHash"]
    return Results.ValidationProblem(
        ex.InvalidProperties.ToDictionary(x => x, x => new[] { "Denied by security policy" }));
}

🗄️ Database Objects (Views, TVFs, SPs)

QueryForge is not limited to tables. You can seamlessly query Views, Table-Valued Functions, and Stored Procedures — and still apply filters, sorting, and pagination on top of their results.

Table-Valued Function (TVF)

var query = DapperQueryBuilder.New()
    .ForObject("tvf_GetUsersByTenant", "dbo", DapperObjectType.TVF, new Dictionary<string, object?> 
    { 
        { "TenantId", 1 } 
    })
    .Build();

Stored Procedure

var query = DapperQueryBuilder.New()
    .ForObject("usp_GetUserReport", "dbo", DapperObjectType.SP, new Dictionary<string, object?> 
    { 
        { "IncludeDeleted", false } 
    })
    .Where(new QueryCriteria(
        groups: [new ConditionGroup([new Condition("Age", ConditionOperator.GreaterThan, 30)])]
    ))
    .Page(20, 1)
    .Build();

⚙️ Configuration & Execution Approaches

Configure how QueryForge interacts with your database schema via ExecutionApproach:

📚 Enum Reference

When sending JSON payloads from the frontend, enums are serialized as integers by default.

ConditionOperator

Logic

SortOrder

ObjectType

QueryResultType (Response Only)

🤝 Contributing & License

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