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 connectionalias: 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:
| 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:
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 a DELETE or an UPDATE. See
Writes after Take or Skip.
Example:
// 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:
// 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:
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:
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
- 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