Fluent Builders

Fluent Builders

Two builders: QueryBuilder for the provider-agnostic Query, and DapperQueryBuilder for the Dapper-specific DapperQuery. Both are conveniences — every property they set is public, so building a query by hand is equally valid.

---

QueryBuilder

A static entry point so you never need new. Each static method starts a chain and returns a QueryFluent.

public static class QueryBuilder
{
    public static QueryFluent New();
    public static QueryFluent Select(params string[] columns);
    public static QueryFluent Where(QueryCriteria criteria);
    public static QueryFluent Sort(params SortDescriptor[] sorts);
    public static QueryFluent GroupBy(params GroupByDescriptor[] groups);
    public static QueryFluent Page(int size, int number = 1);
}

QueryFluent carries the same methods plus Build():

public class QueryFluent
{
    public QueryFluent();
    public QueryFluent(Query query);          // wrap an existing instance

    public QueryFluent Select(params string[] columns);
    public QueryFluent Where(QueryCriteria criteria);
    public QueryFluent Sort(params SortDescriptor[] sorts);
    public QueryFluent GroupBy(params GroupByDescriptor[] groups);
    public QueryFluent Page(int size, int number = 1);

    public Query Build();
}

Usage

var query = QueryBuilder
    .Select("UserId", "FirstName", "LastName", "Score")
    .Sort(new SortDescriptor("Score", SortOrder.Descending),
          new SortDescriptor("LastName"))
    .Page(size: 20, number: 1)
    .Build();

Order does not matter — each method sets one property:

var same = QueryBuilder.New()
    .Page(20)
    .Sort(new SortDescriptor("Score", SortOrder.Descending))
    .Select("UserId", "FirstName", "LastName", "Score")
    .Build();

Semantics to know

• Each method replaces, it does not append. Calling .Select("A").Select("B") leaves SelectColumns = ["B"]. Pass everything in one call.
• Build() returns the same instance the chain has been mutating, not a copy. Two calls to Build() return the same object.
• The builder does no validation. Column names are not checked here; they are checked at execution against what the target exposes, and by Validate() against your policy.
• Page(size) defaults number to 1.

Building criteria

There is no fluent API for the filter tree — construct QueryCriteria directly. Collection expressions keep it readable:

var criteria = new QueryCriteria(
    logic: Logic.And,
    groups:
    [
        new ConditionGroup(
            logic: Logic.Or,
            conditions:
            [
                new Condition("Country", ConditionOperator.Equals, "Germany"),
                new Condition("Country", ConditionOperator.Equals, "Canada")
            ]),

        new ConditionGroup(
            logic: Logic.AndNot,                       // this group is negated
            conditions:
            [
                new Condition("Department", ConditionOperator.Equals, "HR")
            ]),

        new ConditionGroup(
        [
            new Condition("CreatedOn", ConditionOperator.Between, "2024-01-01", "2024-12-31")
        ])
    ]);

var query = QueryBuilder.Where(criteria).Page(20).Build();

Both QueryCriteria and ConditionGroup take their collection as the first parameter with logic optional, so a single-group filter is short:

var simple = new QueryCriteria(
    [ new ConditionGroup([ new Condition("IsActive", ConditionOperator.Equals, true) ]) ]);

A small helper of your own

If you build criteria often, a few local helpers pay for themselves:

static class Filter
{
    public static Condition Eq(string column, object? value)
        => new(column, ConditionOperator.Equals, value);

    public static Condition Like(string column, string value)
        => new(column, ConditionOperator.Contains, value);

    public static Condition Range(string column, object? from, object? to)
        => new(column, ConditionOperator.Between, from, to);

    public static QueryCriteria All(params Condition[] conditions)
        => new([ new ConditionGroup(conditions, Logic.And) ]);

    public static QueryCriteria Any(params Condition[] conditions)
        => new([ new ConditionGroup(conditions, Logic.Or) ]);
}

var query = QueryBuilder
    .Where(Filter.All(Filter.Eq("IsActive", true), Filter.Range("Score", 50, 100)))
    .Page(20)
    .Build();

---

DapperQueryBuilder

Everything QueryBuilder does, plus ForObject, and it builds a DapperQuery.

public static class DapperQueryBuilder
{
    public static DapperQueryFluent New();
    public static DapperQueryFluent New(DapperQuery dapperQuery);
    public static DapperQueryFluent FromBase(Query baseQuery);

    public static DapperQueryFluent ForObject(
        string name,
        string schema = "",
        DapperObjectType type = DapperObjectType.Auto,
        IReadOnlyDictionary<string, object?>? parameters = null);

    public static DapperQueryFluent Select(params string[] columns);
    public static DapperQueryFluent Where(QueryCriteria criteria);
    public static DapperQueryFluent Sort(params SortDescriptor[] sorts);
    public static DapperQueryFluent GroupBy(params GroupByDescriptor[] groups);
    public static DapperQueryFluent Page(int size, int number = 1);
}

DapperQueryFluent derives from QueryFluent and shadows every method so the chain stays typed — you never fall back to the base type mid-chain, and Build() returns DapperQuery.

FromBase — the important one

This is how a client-supplied query becomes an executable one.

app.MapPost("/api/users/query", async (Query clientQuery, IDapperQueryService svc) =>
{
    var dapperQuery = DapperQueryBuilder
        .FromBase(clientQuery)                              // filters, paging, sorting, grouping
        .ForObject("Users", "dbo", DapperObjectType.Table)  // the target — server-side only
        .Build();

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

FromBase copies the five query properties onto a fresh DapperQuery:

new DapperQuery
{
    Criteria       = baseQuery.Criteria,
    Paging         = baseQuery.Paging,
    SelectColumns  = baseQuery.SelectColumns,
    SortColumns    = baseQuery.SortColumns,
    GroupByColumns = baseQuery.GroupByColumns
}

It is a shallow copy: the criteria object is shared with the original, not cloned. That matters only if you validate one and expect the other to be unaffected — SilentStrip replaces the Criteria reference on the query it is called on, so the two diverge rather than corrupt each other.

There is also an instance form, DapperQueryFluent.FromBase(...), which does the same thing.

ForObject

// A table (Auto and Table behave identically)
.ForObject("Users", "dbo")
.ForObject("Users", "dbo", DapperObjectType.Table)

// A view
.ForObject("vw_ActiveUsers", "dbo", DapperObjectType.View)

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

// A stored procedure — arguments are named where the engine supports it
.ForObject("usp_GetUserReport", "dbo", DapperObjectType.SP,
    new Dictionary<string, object?> { ["IncludeDeleted"] = false })

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 makes one call portable across engines.

See Dapper Provider for what each object type supports on each engine.

Fully backend-built

No client input at all — useful for reports, exports and internal jobs:

var query = DapperQueryBuilder
    .Where(new QueryCriteria([ new ConditionGroup([
        new Condition("IsActive", ConditionOperator.Equals, true) ]) ]))
    .Select("UserId", "FirstName", "LastName", "Country", "Score")
    .Sort(new SortDescriptor("Score", SortOrder.Descending))
    .GroupBy(new GroupByDescriptor("Country"))
    .Page(size: 10, number: 1)
    .ForObject("Users", "dbo", DapperObjectType.Table)
    .Build();

---

No builder for EF Core or In-Memory

Neither needs one. Both take a plain Query and get their target from the call site:

await db.Users.AsNoTracking().ToQueryResultAsync<User>(query);   // the DbSet is the target
users.ToQueryResult(query);                                      // the list is the target

Use QueryBuilder to construct the Query, then hand it over.

---

Deciding between the builder and plain construction

The builder exists for readability, not capability. These are equivalent:

var a = QueryBuilder.Select("Id", "Name").Page(20).Build();

var b = new Query
{
    SelectColumns = ["Id", "Name"],
    Paging = new QueryPaging(20, 1)
};

Object-initializer syntax is often clearer when you are setting most of the properties, and the builder is clearer when you are setting one or two — particularly in a chain that ends in .ForObject(...).