Fluent API

Overview

The Jaunty Fluent API provides a type-safe, LINQ-like query builder that enables constructing SQL queries using method chaining. This API offers compile-time validation and IntelliSense support for building complex queries.

Getting Started

From<T>(IDbConnection connection, string? alias = null)

Entry point for the fluent query API. Creates a query builder for the specified entity type.

Signature:

public static IFromClause<T> From<T>(this IDbConnection connection, string? alias = null) where T : new()

Type Parameters:

• T: The entity type to query (must have a parameterless constructor)

Parameters:

• connection: The database connection
• alias: Optional alias for the table (useful for joins with string-based conditions)

Returns:

• IFromClause<T>: A query builder for chaining WHERE, ORDER BY, JOIN, and SELECT operations

Example:

// Basic query
var products = connection.From<Product>()
    .Select();

// With alias for joins
var products = connection.From<Product>("p")
    .InnerJoin<Category>("c")
    .On("p.category_id", "c.id")
    .Select();

Query Building Methods

WHERE Clauses

Where(string column, object? value)

Adds a WHERE condition comparing a column to a value using equality.

Signature:

IWhereClause<T> Where(string column, object? value)

Example:

var products = connection.From<Product>()
    .Where("category_id", 1)
    .Select();

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

Adds a WHERE condition using a strongly-typed expression predicate.

Signature:

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

Example:

var products = connection.From<Product>()
    .Where(p => p.CategoryId == 1)
    .Select();

WhereRaw(string rawSql)

Adds a raw SQL WHERE condition without parameterization (use with caution).

Signature:

IWhereClause<T> WhereRaw(string rawSql)

WhereRaw(string rawSql, object parameters)

Adds a raw SQL WHERE condition with parameters.

Signature:

IWhereClause<T> WhereRaw(string rawSql, object parameters)

AND/OR Conditions

And(string column, object? value)

Adds an AND condition to the existing WHERE clause.

Signature:

IWhereClause<T> And(string column, object? value)

And(Expression<Func<T, bool>> predicate)

Adds an AND condition using a strongly-typed expression.

Signature:

IWhereClause<T> And(Expression<Func<T, bool>> predicate)

Or(string column, object? value)

Adds an OR condition to the existing WHERE clause.

Signature:

IWhereClause<T> Or(string column, object? value)

Or(Expression<Func<T, bool>> predicate)

Adds an OR condition using a strongly-typed expression.

Signature:

IWhereClause<T> Or(Expression<Func<T, bool>> predicate)

Example:

var products = connection.From<Product>()
    .Where(p => p.CategoryId == 1)
    .And(p => p.Price > 50m)
    .Or(p => p.CategoryId == 2)
    .Select();

Collection-Based Filtering

WhereIn<TValue>(Expression<Func<T, TValue>> selector, IEnumerable<TValue> values)

Filters results where the selected property value is in the specified collection.

Signature:

IWhereClause<T> WhereIn<TValue>(Expression<Func<T, TValue>> selector, IEnumerable<TValue> values)

Example:

var products = connection.From<Product>()
    .WhereIn(p => p.CategoryId, new[] { 1, 2, 3 })
    .Select();

WhereNotIn<TValue>(Expression<Func<T, TValue>> selector, IEnumerable<TValue> values)

Filters results where the selected property value is NOT in the specified collection.

Signature:

IWhereClause<T> WhereNotIn<TValue>(Expression<Func<T, TValue>> selector, IEnumerable<TValue> values)

AndIn<TValue>(Expression<Func<T, TValue>> selector, IEnumerable<TValue> values)

Adds an AND condition where the selected property value is in the specified collection.

Signature:

IWhereClause<T> AndIn<TValue>(Expression<Func<T, TValue>> selector, IEnumerable<TValue> values)

OrIn<TValue>(Expression<Func<T, TValue>> selector, IEnumerable<TValue> values)

Adds an OR condition where the selected property value is in the specified collection.

Signature:

IWhereClause<T> OrIn<TValue>(Expression<Func<T, TValue>> selector, IEnumerable<TValue> values)

Range Filtering

WhereBetween<TValue>(Expression<Func<T, TValue>> selector, TValue from, TValue to)

Filters results where the selected property value is between the specified range (inclusive).

Signature:

IWhereClause<T> WhereBetween<TValue>(Expression<Func<T, TValue>> selector, TValue from, TValue to)

Example:

var products = connection.From<Product>()
    .WhereBetween(p => p.Price, 10m, 100m)
    .Select();

WhereNotBetween<TValue>(Expression<Func<T, TValue>> selector, TValue from, TValue to)

Filters results where the selected property value is NOT between the specified range.

Signature:

IWhereClause<T> WhereNotBetween<TValue>(Expression<Func<T, TValue>> selector, TValue from, TValue to)

Example:

var products = connection.From<Product>()
    .WhereNotBetween(p => p.Price, 10m, 100m)
    .Select();

ORDER BY Clauses

OrderBy(Expression<Func<T, object?>> keySelector)

Orders the results by the specified property in ascending order.

Signature:

IOrderByClause<T> OrderBy(Expression<Func<T, object?>> keySelector)

Example:

var products = connection.From<Product>()
    .OrderBy(p => p.ProductName)
    .Select();

OrderByDescending(Expression<Func<T, object?>> keySelector)

Orders the results by the specified property in descending order.

Signature:

IOrderByClause<T> OrderByDescending(Expression<Func<T, object?>> keySelector)

OrderBy(string column)

Orders the results by the specified column name in ascending order.

Signature:

IOrderByClause<T> OrderBy(string column)

OrderByDescending(string column)

Orders the results by the specified column name in descending order.

Signature:

IOrderByClause<T> OrderByDescending(string column)

JOIN Operations

InnerJoin<TJoin>(string? alias = null)

Adds an INNER JOIN to another table.

Signature:

IJoinClause<T, TJoin> InnerJoin<TJoin>(string? alias = null) where TJoin : new()

Example:

var products = connection.From<Product>()
    .InnerJoin<Category>()
    .On((p, c) => p.CategoryId == c.Id)
    .Select();

LeftJoin<TJoin>(string? alias = null)

Adds a LEFT JOIN to another table.

Signature:

IJoinClause<T, TJoin> LeftJoin<TJoin>(string? alias = null) where TJoin : new()

RightJoin<TJoin>(string? alias = null)

Adds a RIGHT JOIN to another table.

Signature:

IJoinClause<T, TJoin> RightJoin<TJoin>(string? alias = null) where TJoin : new()

Example:

var products = connection.From<Product>()
    .RightJoin<Category>()
    .On((p, c) => p.CategoryId == c.Id)
    .Select();

JOIN Conditions

On<TLeftKey, TRightKey>(Expression<Func<T, TLeftKey>> leftKey, Expression<Func<TJoin, TRightKey>> rightKey)

Specifies the join condition using strongly-typed expressions for the keys.

Signature:

IJoinedQuery<T, TJoin> On<TLeftKey, TRightKey>(Expression<Func<T, TLeftKey>> leftKey, Expression<Func<TJoin, TRightKey>> rightKey)

On(Expression<Func<T, TJoin, bool>> predicate)

Specifies the join condition using a strongly-typed expression predicate.

Signature:

IJoinedQuery<T, TJoin> On(Expression<Func<T, TJoin, bool>> predicate)

On(string leftColumn, string rightColumn)

Specifies the join condition as an equality between two column names. Each side is qualified, by alias or by table name.

Signature:

IJoinedQuery<T, TJoin> On(string leftColumn, string rightColumn)

On(string condition)

Specifies the join condition as raw SQL, passed through untouched.

Signature:

IJoinedQuery<T, TJoin> On(string condition)

Example:

var products = connection.From<Product>("p")
    .InnerJoin<Category>("c")
    .On("p.category_id", "c.category_id")
    .Select();

Aliases and string conditions

A lambda On aliases both tables after the parameter names you wrote, and an alias retires the table name for the rest of the statement. Which qualifier a later string may use therefore depends on what the earlier On did.

connection.From<Product>()
    .InnerJoin<Category>()
    .On((p, cat) => p.CategoryId == cat.CategoryId)

FROM products p INNER JOIN categories cat ON (p.category_id = cat.category_id)

From that point p and cat are the qualifiers, and every string-form call on the query is passed through as written:

A query that never uses a lambda On has nothing aliased, so the table name stays valid throughout:

connection.From<Product>()
    .InnerJoin<Category>()
    .On("products.category_id", "categories.category_id")
    .Where("products.unit_price > 20")     // runs

Inference is per join and all-or-nothing. A parameter name that is a SQL keyword in the dialect, that another table or alias in the query already holds, or that equals the table it would alias, is declined, and that join keeps the fully qualified form. An explicit From<Product>("prd") or InnerJoin<Category>("cat") always wins. A self-join with neither side aliased takes t1/t2, because two occurrences of one table name cannot be told apart.

Call ToSql() if you are unsure — it returns the statement without executing it, so the qualifier to use is visible before the query runs.

DISTINCT

Distinct()

Applies DISTINCT to the query results.

Signature:

IDistinctClause<T> Distinct()

Example:

var categories = connection.From<Product>()
    .Distinct()
    .SelectPartial(p => p.CategoryId);

Pagination

Take(int count)

Limits the number of results returned (equivalent to LIMIT/TOP).

Signature:

IPagedClause<T> Take(int count)

Skip(int count)

Skips the specified number of results (equivalent to OFFSET).

Signature:

IPagedClause<T> Skip(int count)

IPagedClause<T> derives from IFromClause<T>, so the chain continues as before; the narrower type exists so a paged query cannot reach DeleteAll or UpdateAll, where the paging would have been silently discarded.

Example:

// Get products 11-20 (pagination)
var products = connection.From<Product>()
    .Skip(10)
    .Take(10)
    .OrderBy(p => p.ProductId)
    .Select();

Terminal Operations (Sync)

Select Methods

Select()

Executes the query and returns all results as a list of entities using strict mapping mode.

Signature:

List<T> Select()

SelectPartial(params string[] columns)

Executes the query and returns results with only the specified columns mapped using partial mapping mode.

Signature:

List<T> SelectPartial(params string[] columns)

Example:

var products = connection.From<Product>()
    .SelectPartial("ProductId", "ProductName", "Price");

SelectPartial(params Expression<Func<T, object?>>[] columns)

Executes the query and returns results with only the specified properties mapped using partial mapping mode.

Signature:

List<T> SelectPartial(params Expression<Func<T, object?>>[] columns)

Example:

var products = connection.From<Product>()
    .SelectPartial(p => p.ProductId, p => p.ProductName, p => p.Price);

Single Result Methods

SelectFirst()

Returns the first result from the query. Throws an exception if the result set is empty.

Signature:

T SelectFirst()

SelectFirstOrDefault()

Returns the first result from the query or the default value if the result set is empty.

Signature:

T? SelectFirstOrDefault()

SelectSingle()

Returns the single result from the query. Throws an exception if the result set is empty or contains more than one element.

Signature:

T SelectSingle()

SelectSingleOrDefault()

Returns the single result from the query or the default value if the result set is empty. Throws an exception if the result set contains more than one element.

Signature:

T? SelectSingleOrDefault()

Aggregate Methods

Count()

Returns the count of records in the result set.

Signature:

int Count()

LongCount()

Returns the count of records in the result set as a long value.

Signature:

long LongCount()

Count<TResult>(Expression<Func<T, TResult>> selector)

Returns the count of records for the specified property.

Signature:

int Count<TResult>(Expression<Func<T, TResult>> selector)

Sum<TResult>(Expression<Func<T, TResult>> selector)

Returns the sum of the specified property values.

Signature:

TResult Sum<TResult>(Expression<Func<T, TResult>> selector)

Avg<TResult>(Expression<Func<T, TResult>> selector)

Returns the average of the specified property values.

Signature:

double Avg<TResult>(Expression<Func<T, TResult>> selector)

Min<TResult>(Expression<Func<T, TResult>> selector)

Returns the minimum value of the specified property.

Signature:

TResult Min<TResult>(Expression<Func<T, TResult>> selector)

Max<TResult>(Expression<Func<T, TResult>> selector)

Returns the maximum value of the specified property.

Signature:

TResult Max<TResult>(Expression<Func<T, TResult>> selector)

Example:

var query = connection.From<Product>().Where(p => p.CategoryId == 1);
var totalValue = query.Sum(p => p.Price);
var averagePrice = query.Avg(p => p.Price);
var mostExpensive = query.Max(p => p.Price);

Terminal Operations (Async)

SelectAsync Methods

SelectAsync(CancellationToken cancellationToken = default)

Asynchronously executes the query and returns all results as a list of entities using strict mapping mode.

Signature:

Task<List<T>> SelectAsync(CancellationToken cancellationToken = default)

SelectPartialAsync(string[] columns, CancellationToken cancellationToken = default)

Asynchronously executes the query and returns results with only the specified columns mapped using partial mapping mode.

Signature:

Task<List<T>> SelectPartialAsync(string[] columns, CancellationToken cancellationToken = default)

SelectPartialAsync(Expression<Func<T, object?>>[] columns, CancellationToken cancellationToken = default)

Asynchronously executes the query and returns results with only the specified properties mapped using partial mapping mode.

Signature:

Task<List<T>> SelectPartialAsync(Expression<Func<T, object?>>[] columns, CancellationToken cancellationToken = default)

Async Single Result Methods

SelectFirstAsync(CancellationToken cancellationToken = default)

Asynchronously returns the first result from the query. Throws an exception if the result set is empty.

Signature:

Task<T> SelectFirstAsync(CancellationToken cancellationToken = default)

SelectFirstOrDefaultAsync(CancellationToken cancellationToken = default)

Asynchronously returns the first result from the query or the default value if the result set is empty.

Signature:

Task<T?> SelectFirstOrDefaultAsync(CancellationToken cancellationToken = default)

SelectSingleAsync(CancellationToken cancellationToken = default)

Asynchronously returns the single result from the query. Throws an exception if the result set is empty or contains more than one element.

Signature:

Task<T> SelectSingleAsync(CancellationToken cancellationToken = default)

SelectSingleOrDefaultAsync(CancellationToken cancellationToken = default)

Asynchronously returns the single result from the query or the default value if the result set is empty. Throws an exception if the result set contains more than one element.

Signature:

Task<T?> SelectSingleOrDefaultAsync(CancellationToken cancellationToken = default)

Async Aggregate Methods

CountAsync(CancellationToken cancellationToken = default)

Asynchronously returns the count of records in the result set.

Signature:

Task<int> CountAsync(CancellationToken cancellationToken = default)

LongCountAsync(CancellationToken cancellationToken = default)

Asynchronously returns the count of records in the result set as a long value.

Signature:

Task<long> LongCountAsync(CancellationToken cancellationToken = default)

CountAsync<TResult>(Expression<Func<T, TResult>> selector, CancellationToken cancellationToken = default)

Asynchronously returns the count of records for the specified property.

Signature:

Task<int> CountAsync<TResult>(Expression<Func<T, TResult>> selector, CancellationToken cancellationToken = default)

SumAsync<TResult>(Expression<Func<T, TResult>> selector, CancellationToken cancellationToken = default)

Asynchronously returns the sum of the specified property values.

Signature:

Task<TResult> SumAsync<TResult>(Expression<Func<T, TResult>> selector, CancellationToken cancellationToken = default)

AvgAsync<TResult>(Expression<Func<T, TResult>> selector, CancellationToken cancellationToken = default)

Asynchronously returns the average of the specified property values.

Signature:

Task<double> AvgAsync<TResult>(Expression<Func<T, TResult>> selector, CancellationToken cancellationToken = default)

MinAsync<TResult>(Expression<Func<T, TResult>> selector, CancellationToken cancellationToken = default)

Asynchronously returns the minimum value of the specified property.

Signature:

Task<TResult> MinAsync<TResult>(Expression<Func<T, TResult>> selector, CancellationToken cancellationToken = default)

MaxAsync<TResult>(Expression<Func<T, TResult>> selector, CancellationToken cancellationToken = default)

Asynchronously returns the maximum value of the specified property.

Signature:

Task<TResult> MaxAsync<TResult>(Expression<Func<T, TResult>> selector, CancellationToken cancellationToken = default)

Example:

var query = connection.From<Product>().Where(p => p.CategoryId == 1);
var totalValue = await query.SumAsync(p => p.Price, cancellationToken);
var mostExpensive = await query.MaxAsync(p => p.Price, cancellationToken);

SQL Generation Methods

ToSql()

Generates the SQL string for the current query without executing it.

Signature:

string ToSql()

ToSql(params string[] columns)

Generates the SQL string for the current query with the specified columns.

Signature:

string ToSql(params string[] columns)

ToSql(params Expression<Func<T, object?>>[] columns)

Generates the SQL string for the current query with the specified properties.

Signature:

string ToSql(params Expression<Func<T, object?>>[] columns)

Example:

var sql = connection.From<Product>()
    .Where(p => p.CategoryId == 1)
    .OrderBy(p => p.ProductName)
    .ToSql();
// SELECT product_id, product_name, category_id, price FROM products
// WHERE (category_id = @category_id) ORDER BY product_name

Every mapped column is listed, never *, and a parameter is named after its column.

Advanced Features

GROUP BY Operations

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

Groups results by the specified key.

Signature:

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

Example:

var groupedProducts = connection.From<Product>()
    .GroupBy(p => p.CategoryId)
    .Select(g => new { g.Key, Count = g.Count(), AveragePrice = g.Avg(p => p.Price) });

The aggregate methods on the group are Count, Sum, Avg, Min and Max; there is no LINQ Average because IGrouping<TKey, T> is Jaunty's own interface, not System.Linq's.

HAVING parameter names

The alias rule reaches the parameters a HAVING clause binds. An operand is named after the aggregate it is compared to, read off the expression tree rather than the rendered SQL:

var sql = connection.From<Product>()
    .InnerJoin<Category>()
    .On((p, c) => p.CategoryId == c.CategoryId)
    .GroupBy((p, c) => p.CategoryId)
    .Having(g => g.Sum((p, c) => p.UnitPrice) > 150m)
    .ToSql(g => new { g.Key, Count = g.Count() });

SELECT p.category_id AS "Key", COUNT(*) AS Count
FROM products p
INNER JOIN categories c ON (p.category_id = c.category_id)
GROUP BY p.category_id
HAVING SUM(p.unit_price) > @sum_p_unit_price

g.Count() > 3 binds @count, and so does 3 < g.Count(): the side that is an aggregate names the side that is a value, whichever way round you wrote it. A number is spent only where one query compares the same aggregate twice, which gives @count_2.

Kitchen Sink Example

A single query combining a join, multi-condition filtering and ordering. Aliases come from the lambda parameter names, so p and c below are what the SQL uses:

var products = connection.From<Product>()
    .InnerJoin<Category>()
    .On((p, c) => p.CategoryId == c.Id)
    .Where((p, c) => p.Price >= 10m && p.Price <= 250m)
    .And((p, c) => c.Name == "Beverages")
    .OrderBy(p => p.ProductName)
    .Select();

SELECT p.product_id, p.product_name, p.category_id, p.price
FROM products p
INNER JOIN categories c ON (p.category_id = c.id)
WHERE (((p.price >= @p_price) AND (p.price <= @p_price_2)) AND (c.name = @c_name))
ORDER BY p.product_name

After On(...) the query is an IJoinedQuery<Product, Category>: its Where/And/Or take a two-parameter lambda, and WhereIn, WhereBetween, Skip and Take are not available on a join. Page a joined result with Take/Skip on the single-table query before the join, or in SQL.

Important Notes