Class QueryBuilder<T>

Namespace

CL.MySQL2.Services

Assembly

CL.MySQL2.dll

Fluent query builder for entity type T. Chains WHERE, ORDER BY, JOIN, GROUP BY, LIMIT/OFFSET clauses and executes terminal operations returning CodeLogic.Core.Results.Result<T>.

public sealed class QueryBuilder<T> where T : class, new()

Type Parameters

T

Inheritance

object

QueryBuilder<T>

Inherited Members

object.Equals(object)

object.Equals(object, object)

object.GetHashCode()

object.GetType()

object.ReferenceEquals(object, object)

object.ToString()

Constructors

QueryBuilder(ConnectionManager, ILogger?, TransactionScope, int)

public QueryBuilder(ConnectionManager connectionManager, ILogger? logger, TransactionScope transactionScope, int slowQueryThresholdMs = 1000)

Parameters

connectionManager ConnectionManager

logger ILogger

transactionScope TransactionScope

slowQueryThresholdMs int

QueryBuilder(ConnectionManager, ILogger?, string, int)

public QueryBuilder(ConnectionManager connectionManager, ILogger? logger = null, string connectionId = "Default", int slowQueryThresholdMs = 1000)

Parameters

connectionManager ConnectionManager

logger ILogger

connectionId string

slowQueryThresholdMs int

Methods

After(string?)

Continues a cursor-paged query after cursor. The cursor must have been returned by ToCursorPagedListAsync(int, CancellationToken) for the same entity and ordering. A null cursor selects the first page.

public QueryBuilder<T> After(string? cursor)

Parameters

cursor string

Returns

QueryBuilder<T>

AverageAsync<TResult>(Expression<Func<T, TResult>>, CancellationToken)

public Task<Result<double>> AverageAsync<TResult>(Expression<Func<T, TResult>> selector, CancellationToken ct = default)

Parameters

selector Expression<Func<T, TResult>>

ct CancellationToken

Returns

Task<Result<double>>

Type Parameters

TResult

CountAsync(CancellationToken)

public Task<Result<long>> CountAsync(CancellationToken ct = default)

Parameters

ct CancellationToken

Returns

Task<Result<long>>

DeleteAsync(CancellationToken)

public Task<Result<int>> DeleteAsync(CancellationToken ct = default)

Parameters

ct CancellationToken

Returns

Task<Result<int>>

FirstOrDefaultAsync(CancellationToken)

public Task<Result<T?>> FirstOrDefaultAsync(CancellationToken ct = default)

Parameters

ct CancellationToken

Returns

Task<Result<T>>

GroupBy<TKey>(Expression<Func<T, TKey>>)

Groups rows by the key selector and returns a GroupedQuery<TKey, TSource>. Collapse the groups with .Select(g => new { … g.Sum(...) … }); nothing is executed until a terminal on the projected query.

public GroupedQuery<TKey, T> GroupBy<TKey>(Expression<Func<T, TKey>> keySelector)

Parameters

keySelector Expression<Func<T, TKey>>

Returns

GroupedQuery<TKey, T>

Type Parameters

TKey

IncludeDeleted()

Includes soft-deleted rows in the results. Only meaningful when the entity carries SoftDeleteAttribute; otherwise a no-op. Reads exclude soft-deleted rows by default.

public QueryBuilder<T> IncludeDeleted()

Returns

QueryBuilder<T>

Join(string, string, JoinType)

public QueryBuilder<T> Join(string table, string condition, JoinType type = JoinType.Inner)

Parameters

table string

condition string

type JoinType

Returns

QueryBuilder<T>

Join<TRight, TKey, TResult>(Expression<Func<T, TKey>>, Expression<Func<TRight, TKey>>, Expression<Func<T, TRight, TResult>>, JoinType)

Strongly-typed equi-join to TRight, translated to real SQL with table aliases (left t0, right t1) and a compiled projection into TResult — only the referenced columns are transferred.

Example:

await mysql.Query<Order>()
    .Where(o => o.Total > 100)
    .Join<Customer, long, OrderView>(
        o => o.CustomerId,             // left key
        c => c.Id,                     // right key
        (o, c) => new OrderView { OrderId = o.Id, Customer = c.Name })
    .OrderByDescending((o, c) => o.Total)
    .ToListAsync();

Keys may be single members or composite anonymous keys (o => new { o.A, o.B } matched with c => new { c.X, c.Y }). .Where(...) calls made before .Join are carried over and re-qualified to the left table. Apply ordering and paging after the join via the returned JoinedQuery<TLeft, TRight, TResult>.

public JoinedQuery<T, TRight, TResult> Join<TRight, TKey, TResult>(Expression<Func<T, TKey>> leftKey, Expression<Func<TRight, TKey>> rightKey, Expression<Func<T, TRight, TResult>> resultSelector, JoinType type = JoinType.Inner) where TRight : class, new()

Parameters

leftKey Expression<Func<T, TKey>>

rightKey Expression<Func<TRight, TKey>>

resultSelector Expression<Func<T, TRight, TResult>>

type JoinType

Returns

JoinedQuery<T, TRight, TResult>

Type Parameters

TRight

TKey

TResult

Limit(int)

public QueryBuilder<T> Limit(int count)

Parameters

count int

Returns

QueryBuilder<T>

MaxAsync<TResult>(Expression<Func<T, TResult>>, CancellationToken)

public Task<Result<TResult>> MaxAsync<TResult>(Expression<Func<T, TResult>> selector, CancellationToken ct = default)

Parameters

selector Expression<Func<T, TResult>>

ct CancellationToken

Returns

Task<Result<TResult>>

Type Parameters

TResult

MinAsync<TResult>(Expression<Func<T, TResult>>, CancellationToken)

public Task<Result<TResult>> MinAsync<TResult>(Expression<Func<T, TResult>> selector, CancellationToken ct = default)

Parameters

selector Expression<Func<T, TResult>>

ct CancellationToken

Returns

Task<Result<TResult>>

Type Parameters

TResult

Offset(int)

public QueryBuilder<T> Offset(int count)

Parameters

count int

Returns

QueryBuilder<T>

OrderByDescending<TKey>(Expression<Func<T, TKey>>)

public QueryBuilder<T> OrderByDescending<TKey>(Expression<Func<T, TKey>> keySelector)

Parameters

keySelector Expression<Func<T, TKey>>

Returns

QueryBuilder<T>

Type Parameters

TKey

OrderBy<TKey>(Expression<Func<T, TKey>>)

public QueryBuilder<T> OrderBy<TKey>(Expression<Func<T, TKey>> keySelector)

Parameters

keySelector Expression<Func<T, TKey>>

Returns

QueryBuilder<T>

Type Parameters

TKey

Select<TResult>(Expression<Func<T, TResult>>)

Typed projection: transforms rows of T into rows of TResult. Emits a real SELECT col1, col2 AS alias column list and returns a pipeline whose terminals hydrate TResult directly — skipping T materialization and any columns not referenced by the projection.

public ProjectedQuery<T, TResult> Select<TResult>(Expression<Func<T, TResult>> selector)

Parameters

selector Expression<Func<T, TResult>>

Returns

ProjectedQuery<T, TResult>

Type Parameters

TResult

Skip(int)

public QueryBuilder<T> Skip(int count)

Parameters

count int

Returns

QueryBuilder<T>

SmartCache(string)

Opt this query into a named SmartCachePool. The pool's background timer keeps the cache entry warm — readers never block on the DB once the entry is populated. The pool must be registered via MySQL2Library.RegisterCachePool beforehand; an unknown name falls back to non-cached execution (logged at warn).

Mutually exclusive with WithCache(TimeSpan) — if both are set, SmartCache wins and the TTL is derived from the pool's refresh interval.

public QueryBuilder<T> SmartCache(string poolName)

Parameters

poolName string

Returns

QueryBuilder<T>

SumAsync<TResult>(Expression<Func<T, TResult>>, CancellationToken)

public Task<Result<TResult>> SumAsync<TResult>(Expression<Func<T, TResult>> selector, CancellationToken ct = default)

Parameters

selector Expression<Func<T, TResult>>

ct CancellationToken

Returns

Task<Result<TResult>>

Type Parameters

TResult

Take(int)

public QueryBuilder<T> Take(int count)

Parameters

count int

Returns

QueryBuilder<T>

ToCursorPagedListAsync(int, CancellationToken)

Executes a forward-only keyset page. At least one explicit ordering is required; the mapped primary key is appended automatically when it is not already present.

public Task<Result<CursorPagedResult<T>>> ToCursorPagedListAsync(int pageSize, CancellationToken ct = default)

Parameters

pageSize int

ct CancellationToken

Returns

Task<Result<CursorPagedResult<T>>>

ToListAsync(CancellationToken)

public Task<Result<List<T>>> ToListAsync(CancellationToken ct = default)

Parameters

ct CancellationToken

Returns

Task<Result<List<T>>>

ToPagedListAsync(int, int, CancellationToken)

public Task<Result<PagedResult<T>>> ToPagedListAsync(int page, int pageSize, CancellationToken ct = default)

Parameters

page int

pageSize int

ct CancellationToken

Returns

Task<Result<PagedResult<T>>>

UpdateAsync(Dictionary<string, object?>, CancellationToken)

public Task<Result<int>> UpdateAsync(Dictionary<string, object?> updates, CancellationToken ct = default)

Parameters

updates Dictionary<string, object>

ct CancellationToken

Returns

Task<Result<int>>

UpdateAsync(Expression<Func<T, T>>, CancellationToken)

Bulk-update matching rows with a typed setter expression. Emits a single UPDATE … SET … WHERE … statement; no rows are fetched client-side.

Example:

await mysql.Query<Ticket>()
    .Where(t => t.Status == "open" && t.CreatedUtc < cutoff)
    .UpdateAsync(t => new Ticket { Status = "stale", ReviewedUtc = now });

Each new T { Prop = value } binding maps to one SET col = value. Values may reference captured variables (parameterized) or other columns of the same row (new T { Counter = t.Counter + 1 }).

public Task<Result<int>> UpdateAsync(Expression<Func<T, T>> setExpression, CancellationToken ct = default)

Parameters

setExpression Expression<Func<T, T>>

ct CancellationToken

Returns

Task<Result<int>>

Where(Expression<Func<T, bool>>)

public QueryBuilder<T> Where(Expression<Func<T, bool>> predicate)

Parameters

predicate Expression<Func<T, bool>>

Returns

QueryBuilder<T>

WhereExists<TInner>(Expression<Func<T, TInner, bool>>)

Adds a correlated EXISTS filter. The predicate may reference both the outer row and a row of TInner; any non-correlated condition on the inner side belongs in the same predicate. Translates to EXISTS (SELECT 1 FROM inner WHERE …).

Example:

mysql.Query<Order>()
  .WhereExists<Shipment>((o, s) => s.OrderId == o.Id && s.Status == "sent")

<p><b>Not supported:</b> EXISTS against the same table as the outer query
(unqualified inner columns would be ambiguous). A query carrying a subquery filter
is not cacheable and cannot be turned into a typed <code>.Join</code>.</p>

public QueryBuilder<T> WhereExists<TInner>(Expression<Func<T, TInner, bool>> predicate) where TInner : class, new()

Parameters

predicate Expression<Func<T, TInner, bool>>

Returns

QueryBuilder<T>

Type Parameters

TInner

WhereIn<TInner, TKey>(Expression<Func<T, TKey>>, Expression<Func<TInner, TKey>>, Expression<Func<TInner, bool>>?)

Adds an IN (subquery) filter: outerColumn IN (SELECT innerColumn FROM inner [WHERE innerFilter]). The inner filter is uncorrelated (it sees only TInner).

Example:

mysql.Query<Order>()
  .WhereIn<Customer, long>(o => o.CustomerId, c => c.Id, c => c.IsVip)

<p>A query carrying a subquery filter is not cacheable and cannot be turned into
a typed <code>.Join</code>.</p>

public QueryBuilder<T> WhereIn<TInner, TKey>(Expression<Func<T, TKey>> outerColumn, Expression<Func<TInner, TKey>> innerColumn, Expression<Func<TInner, bool>>? innerFilter = null) where TInner : class, new()

Parameters

outerColumn Expression<Func<T, TKey>>

innerColumn Expression<Func<TInner, TKey>>

innerFilter Expression<Func<TInner, bool>>

Returns

QueryBuilder<T>

Type Parameters

TInner

TKey

WhereNotExists<TInner>(Expression<Func<T, TInner, bool>>)

Correlated NOT EXISTS filter. See WhereExists<TInner>(Expression<Func<T, TInner, bool>>).

public QueryBuilder<T> WhereNotExists<TInner>(Expression<Func<T, TInner, bool>> predicate) where TInner : class, new()

Parameters

predicate Expression<Func<T, TInner, bool>>

Returns

QueryBuilder<T>

Type Parameters

TInner

WhereNotIn<TInner, TKey>(Expression<Func<T, TKey>>, Expression<Func<TInner, TKey>>, Expression<Func<TInner, bool>>?)

Adds a NOT IN (subquery) filter. See WhereIn<TInner, TKey>(Expression<Func<T, TKey>>, Expression<Func<TInner, TKey>>, Expression<Func<TInner, bool>>?).

public QueryBuilder<T> WhereNotIn<TInner, TKey>(Expression<Func<T, TKey>> outerColumn, Expression<Func<TInner, TKey>> innerColumn, Expression<Func<TInner, bool>>? innerFilter = null) where TInner : class, new()

Parameters

outerColumn Expression<Func<T, TKey>>

innerColumn Expression<Func<TInner, TKey>>

innerFilter Expression<Func<TInner, bool>>

Returns

QueryBuilder<T>

Type Parameters

TInner

TKey

WithCache(TimeSpan)

Enable result caching for this query. Cached results are automatically invalidated when any mutation (INSERT/UPDATE/DELETE) occurs on the same table. Has no effect inside a transaction scope (reads in transactions see uncommitted writes).

public QueryBuilder<T> WithCache(TimeSpan ttl)

Parameters

ttl TimeSpan

Returns

QueryBuilder<T>

WithConnection(string)

public QueryBuilder<T> WithConnection(string connectionId)

Parameters

connectionId string

Returns

QueryBuilder<T>