Sample Application

Sample Application

samples/PepperX.QueryForge.Sample.WebApi is a runnable ASP.NET Core 10 Minimal API with 20 endpoints across all three providers. Every endpoint accepts the same Query body, so you can post one payload to a Dapper endpoint, an EF Core endpoint and an in-memory endpoint and compare the three responses.

---

Running it

Group 7 — the in-memory endpoints — needs no database at all, so the fastest way to see QueryForge work is:

cd samples/PepperX.QueryForge.Sample.WebApi
dotnet run

then post to /api/inmemory/users/query. The project seeds 40 TestUser rows in memory at startup.

For groups 1–6 you need SQL Server and the setup script:

1. Set ConnectionStrings:DefaultConnection in appsettings.json.
2. Run Scripts.sql against that database. It creates:
   • the TestUsers table with sample data,
   • the vw_ActiveUsers view,
   • the tvf_GetUsersByTenant table-valued function,
   • the usp_GetUserReport stored procedure.

QueryForge itself needs nothing deployed. The script exists so the sample has a view, a function and a procedure to demonstrate against.

OpenAPI is exposed at /openapi/v1.json in development, and Requests.http carries ready-made requests for every endpoint.

---

The endpoints

1. Core flat queries — /api/users

Route	Shows
POST /query	the standard path through IDapperQueryService
POST /query-raw	the same thing through the IDbConnection extension, no service dependency
POST /projection	selectColumns narrowing the SELECT list
POST /deep-pagination	page 5 of a large set

2. Advanced filtering — /api/users/filters

Route	Shows
POST /text-search	Contains (2), StartsWith (4), EndsWith (5), with wildcard escaping
POST /null-checks	Equals with a null value → IS NULL; NotEquals → IS NOT NULL
POST /range	Between (10), requiring both value and valueTo
POST /complex-logic	nested Or and AndNot groups

3. Security and validation — /api/users/security

Route	Shows
POST /silent-strip	denied columns removed and page size clamped; the request still succeeds
POST /strict-validation	ThrowException turned into a 400 with Results.ValidationProblem

Ask /strict-validation for PasswordHash and you get a 400 listing it.

4. Hierarchies and grouping — /api/users/hierarchy

Route	Shows
POST /grouped-2-level	Country → Department
POST /grouped-3-level	Country → Department → Role
POST /grouped-filtered	a filter applied before grouping

The grouping levels are set server-side in these endpoints, so the client controls the filter and the paging but not the shape.

5. Database objects — /api/objects

Route	Shows
POST /view	querying vw_ActiveUsers with DapperObjectType.View
POST /tvf	tvf_GetUsersByTenant with a parameter
POST /stored-procedure	usp_GetUserReport, filtered in memory after the call

6. EF Core provider — /api/efcore/users

Route	Shows
POST /query	the same body, through EF Core
POST /grouped-query	grouping with an allow-list on the groupable columns
POST /scoped	composing with an existing Where the client cannot remove — the tenant-isolation pattern
POST /sql	returns the SQL EF Core would run, without executing it

/sql is the quickest way to confirm values become parameters rather than inlined literals.

7. In-memory provider — /api/inmemory/users

Route	Shows
POST /query	a flat query against a plain collection — no database needed
POST /grouped-query	the same hierarchy shape the other two produce
POST /validated	strict validation with no database at all

---

Comparing providers

The point of the sample is that this body works against all three:

{
  "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 } ]
}

curl -X POST localhost:5000/api/users/query          -H 'Content-Type: application/json' -d @body.json
curl -X POST localhost:5000/api/efcore/users/query   -H 'Content-Type: application/json' -d @body.json
curl -X POST localhost:5000/api/inmemory/users/query -H 'Content-Type: application/json' -d @body.json

The three responses should be identical apart from the underlying data. That is the parity guarantee in Cross-Provider Parity, demonstrated rather than asserted.

---

How it is wired

// A connection for the IDbConnection-extension endpoints
builder.Services.AddScoped<IDbConnection>(sp =>
    new SqlConnection(sp.GetRequiredService<IConfiguration>().GetConnectionString("DefaultConnection")));

// The Dapper provider, with a factory for the connectionless overload
builder.Services.AddQueryForgeDapper(options =>
{
    options.ConnectionFactory = sp =>
        new SqlConnection(sp.GetRequiredService<IConfiguration>().GetConnectionString("DefaultConnection"));
});

// EF Core over the same table
builder.Services.AddDbContext<SampleDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

// The in-memory provider needs no registration; this is just the sample data.
builder.Services.AddSingleton<IReadOnlyList<TestUser>>(SampleData.Users);

SampleDbContext maps TestUser to the same TestUsers table the Dapper endpoints use, so the two providers are pointed at identical data:

public class SampleDbContext(DbContextOptions<SampleDbContext> options) : DbContext(options)
{
    public DbSet<TestUser> Users => Set<TestUser>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
        => modelBuilder.Entity<TestUser>(e =>
        {
            e.ToTable("TestUsers");
            e.HasKey(u => u.UserId);
        });
}

The model

public class TestUser
{
    public int UserId { get; set; }
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public string Country { get; set; } = string.Empty;
    public string City { get; set; } = string.Empty;
    public string Department { get; set; } = string.Empty;
    public int Age { get; set; }
    public decimal Score { get; set; }
    public bool IsActive { get; set; }
    public DateTime CreatedOn { get; set; }
    public DateTime? DeletedAt { get; set; }
    public string Role { get; set; } = string.Empty;
}

DeletedAt is nullable and is used by the null-check and validation examples; Country, Department and Role are the grouping columns.

---

Patterns worth copying

Three things in the sample are the recommended way to do them:

1. The client sends Query, never DapperQuery. Every Dapper endpoint binds Query and upgrades it with DapperQueryBuilder.FromBase(...).ForObject(...), so the target is unreachable from the wire. See Security.
2. Validation sits between binding and execution, with SilentStrip on the public-shaped endpoints and ThrowException where a 400 is the right answer.
3. /api/efcore/users/scoped composes a server-side Where first, which is the tenant-isolation pattern — the restriction is part of the same IQueryable and cannot be filtered away.