jaunty Api reference › Fluent API

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:

C#
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:

C#
// 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:

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

Example:

C#
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:

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

Example:

C#
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:

C#
IWhereClause<T> WhereRaw(string rawSql)

WhereRaw(string rawSql, object parameters)

Adds a raw SQL WHERE condition with parameters.

Signature:

C#
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:

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

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

Adds an AND condition using a strongly-typed expression.

Signature:

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

Or(string column, object? value)

Adds an OR condition to the existing WHERE clause.

Signature:

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

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

Adds an OR condition using a strongly-typed expression.

Signature:

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

Example:

C#
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:

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

Example:

C#
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:

C#
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:

C#
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:

C#
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:

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

Example:

C#
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:

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

Example:

C#
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:

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

Example:

C#
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:

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

OrderBy(string column)

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

Signature:

C#
IOrderByClause<T> OrderBy(string column)

OrderByDescending(string column)

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

Signature:

C#
IOrderByClause<T> OrderByDescending(string column)

JOIN Operations

InnerJoin<TJoin>(string? alias = null)

Adds an INNER JOIN to another table.

Signature:

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

Example:

C#
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:

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

RightJoin<TJoin>(string? alias = null)

Adds a RIGHT JOIN to another table.

Signature:

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

Example:

C#
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:

C#
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:

C#
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:

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

On(string condition)

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

Signature:

C#
IJoinedQuery<T, TJoin> On(string condition)

Example:

C#
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.

C#
connection.From<Product>()
    .InnerJoin<Category>()
    .On((p, cat) => p.CategoryId == cat.CategoryId)
SQL
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:

string result
Where("p.unit_price > 20") runs
Where("p.product_id IN (SELECT product_id FROM products WHERE discontinued = 0)") runs — the subquery opens its own scope, where products is a table again
Where("products.unit_price > 20") fails. SQLite: SQLite Error 1: 'no such column: products.unit_price'.; SQL Server: Msg 4104, The multi-part identifier "products.unit_price" could not be bound.

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

C#
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:

C#
IDistinctClause<T> Distinct()

Example:

C#
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:

C#
IPagedClause<T> Take(int count)

Skip(int count)

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

Signature:

C#
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 a DELETE or an UPDATE. See Writes after Take or Skip.

Example:

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

Writes after Take or Skip

Take and Skip page reads only. A DELETE or UPDATE after them is refused, because the statement would run without the paging and touch every matching row:

C#
// Reads as "delete 5 rows". Would delete every order in the table.
connection.From<Order>().Take(5).DeleteAll();                          // CS0619

// Same, with a filter. Would delete every stale order, not 5 of them.
connection.From<Order>().Take(5).Where(o => o.Status == "stale").Delete();      // CS0619
connection.From<Order>().Distinct().Take(5).Where(o => o.Status == "stale").Delete(); // CS0619
connection.From<Order>().Take(5).Set(o => o.Status, "archived").UpdateAll();    // CS0619

Each of those is a compile error that names the problem and the fix. One route gets past the compiler: a paged query stored in a variable of an un-paged type, such as IFromClause<Order> q = ...Take(5); or a query built up conditionally. That route throws InvalidOperationException when the SQL is built, before anything reaches the database.

Why Jaunty does not translate it. No SQL form of "delete the first N rows" works on every database Jaunty supports:

Database What it offers Why it is not enough
SQL Server DELETE TOP (n) No ORDER BY and no offset, so which N rows go is up to the server
PostgreSQL No LIMIT on DELETE or UPDATE Needs a subquery on the key
SQLite DELETE ... ORDER BY ... LIMIT Only in builds compiled with SQLITE_ENABLE_UPDATE_DELETE_LIMIT; most are not
MySQL / MariaDB DELETE ... ORDER BY ... LIMIT n No offset, so Skip cannot be expressed; MySQL also rejects LIMIT inside an IN (...) subquery
DuckDB No LIMIT on DELETE or UPDATE Needs a subquery on the key

A translation would need an OrderBy (otherwise "the first 5" is whatever the database returns first), a primary key, and different SQL per database, with Skip impossible on MySQL. That is a lot of new surface for something nobody has asked for, so it is listed as a feature candidate rather than built.

What to write instead. Select the keys of the rows you want, then write by key, in one transaction so no other writer changes the set in between:

C#
using var tx = connection.BeginTransaction();
var options = CommandOptions.WithTransaction(tx);

List<int> ids = connection.From<Order>()
    .Where(o => o.Status == "stale")
    .OrderBy(o => o.CreatedAt)
    .Take(5)
    .Select(options)
    .ConvertAll(o => o.Id);

int deleted = connection.From<Order>()
    .WhereIn(o => o.Id, ids)
    .Delete(options);

// Or update the same rows instead:
int updated = connection.From<Order>()
    .Set(o => o.Status, "archived")
    .WhereIn(o => o.Id, ids)
    .Update(options);

tx.Commit();

This costs one extra round trip, and the SQL it sends is the same on every database. For a large cleanup, run it in a loop until deleted is 0, which also keeps each transaction short.

Terminal Operations (Sync)

Select Methods

Select()

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

Signature:

C#
List<T> Select()

SelectPartial(params string[] columns)

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

Signature:

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

Example:

C#
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:

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

Example:

C#
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:

C#
T SelectFirst()

SelectFirstOrDefault()

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

Signature:

C#
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:

C#
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:

C#
T? SelectSingleOrDefault()

Aggregate Methods

Count()

Returns the count of records in the result set.

Signature:

C#
int Count()

LongCount()

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

Signature:

C#
long LongCount()

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

Returns the count of records for the specified property.

Signature:

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

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

Returns the sum of the specified property values.

Signature:

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

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

Returns the average of the specified property values.

Signature:

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

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

Returns the minimum value of the specified property.

Signature:

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

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

Returns the maximum value of the specified property.

Signature:

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

Example:

C#
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:

C#
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:

C#
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:

C#
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:

C#
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:

C#
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:

C#
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:

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

Async Aggregate Methods

CountAsync(CancellationToken cancellationToken = default)

Asynchronously returns the count of records in the result set.

Signature:

C#
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:

C#
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:

C#
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:

C#
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:

C#
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:

C#
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:

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

Example:

C#
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:

C#
string ToSql()

ToSql(params string[] columns)

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

Signature:

C#
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:

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

Example:

C#
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:

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

Example:

C#
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:

C#
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() });
SQL
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:

C#
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();
SQL
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

  • Type Safety: The fluent API provides compile-time validation of property names
  • SQL Generation: The API automatically generates appropriate SQL for different database providers
  • Parameterization: All values are automatically parameterized to prevent SQL injection
  • Async Support: All operations have both synchronous and asynchronous variants
  • Mapping Mode: All query operations use strict mapping by default
  • Performance: The fluent API builds SQL dynamically but still leverages Jaunty's performance optimizations
  • Database Compatibility: The API works with all supported database providers (SQL Server, SQLite, PostgreSQL, MySQL)
  • Method Chaining: All methods (except terminal operations) return interfaces that allow further method chaining
  • IntelliSense: Full IntelliSense support for property names and method chaining
  • Alias Support: Table aliases can be used for complex queries with joins