RoomSharp.QueryExtensions 0.5.4

RoomSharp.QueryExtensions

Designed for developers who want control, not magic.

RoomSharp.QueryExtensions is a small companion package to RoomSharp that adds a Dapper-like API on top of RoomSharp.Core.RoomDatabase for ad-hoc SQL:

• Query POCOs/DTOs/records without declaring a DAO method
• Fluent Query Builder - Laravel-style db.From<T>().Where().OrderBy().Get()
• Eager Loading - Include related entities with batched queries
• Stream results with IAsyncEnumerable<T>
• Run raw commands and scalars (Execute*, ExecuteScalar*)
• Multi-mapping up to 5 types with splitOn
• Drop down to low-level, allocation-free row access (RowReader)

It keeps the call-site ergonomic, while caching the expensive parts (parameter binding and result materialization) behind the scenes.

Note: This is a feature-complete preview. APIs may evolve, but the overall design is considered stable.

⚠️ QueryExtensions is not a full ORM. It focuses on querying and mapping, not change tracking or migrations.

When Should I Use QueryExtensions?

Use QueryExtensions when:

• You need ad-hoc queries without creating a DAO
• You want Dapper-like control with RoomSharp integration
• You need streaming or zero-allocation access

Prefer DAOs when:

• Queries are part of your core domain
• You want compile-time SQL ownership
• You rely on migrations and schema coupling

Install

dotnet add package RoomSharp.QueryExtensions

Target frameworks: net8.0, net9.0, net10.0.

Quick Examples

// Fluent query (type-safe)
var users = await db.From<User>()
    .Where(u => u.IsActive)
    .OrderBy(u => u.Name)
    .GetAsync();

// With eager loading
var orders = await db.From<Order>()
    .Include(o => o.Customer)
    .GetAsync();

// Raw SQL
var count = await db.ExecuteScalarAsync<long>(
    "SELECT COUNT(*) FROM users WHERE IsActive = @active",
    new { active = true });

// Streaming large results
await foreach (var item in db.QueryStreamAsync<Item>("SELECT * FROM items"))
{
    Process(item);
}

---

Fluent Query Builder

A Laravel-style fluent API for building queries without writing raw SQL. Supports both String-based and Expression-based syntax.

Basic Usage

using RoomSharp.QueryExtensions.FluentQuery;

// String-based
var todos = await db.Table<Todo>("todos")
    .Where("IsDone", false)
    .Where("CategoryId", ">", 5)
    .OrderBy("CreatedAt")
    .Take(20)
    .GetAsync();

// Expression-based (Type-safe)
var todos = await db.From<Todo>()
    .Where(t => t.IsDone == false)
    .Where(t => t.CategoryId > 5)
    .OrderBy(t => t.CreatedAt)
    .Take(20)
    .GetAsync();

Where Methods

Method	Example
Where(col, val)	.Where("IsActive", true)
Where(col, op, val)	.Where("Age", ">=", 18)
OrWhere(col, val)	.OrWhere("Status", "pending")
WhereIn(col, vals)	.WhereIn("Status", "active", "pending")
WhereNotIn(col, vals)	.WhereNotIn("Status", "deleted")
WhereNull(col)	.WhereNull("DeletedAt")
WhereNotNull(col)	.WhereNotNull("Email")
WhereBetween(col, a, b)	.WhereBetween("Price", 10, 100)
WhereContains(col, val)	.WhereContains("Title", "test")
WhereStartsWith(col, val)	.WhereStartsWith("Name", "A")
WhereRaw(sql, params)	.WhereRaw("LOWER(name) = @p0", "test")

Expression-based Where

// Comparison operators
.Where(t => t.Price > 100)
.Where(t => t.Status == "active")

// String methods
.Where(t => t.Title.Contains("test"))
.Where(t => t.Name.StartsWith("A"))

// Boolean properties
.Where(t => t.IsActive)
.Where(t => !t.IsDeleted)

// Method calls are supported (evaluated at runtime)
.Where(t => t.CreatedAt > DateTime.Now.AddDays(-7))
.Where(t => t.ExpiresAt < DateTime.UtcNow)

Advanced Expression Features

Enum Comparisons

public enum Status { Pending, InProgress, Completed }

// Direct enum comparison
.Where(x => x.Status == Status.InProgress)
// Generates: WHERE Status = 1

Compound Expressions (&& and ||)

// AND conditions in single Where
.Where(x => x.IsActive && x.Price > 100)

// OR conditions in single Where
.Where(x => x.Title.Contains("#4") || x.Title.Contains("#10"))

// Complex combinations
.Where(x => (x.Status == Status.Active && x.Price > 50) || x.IsFeatured)

Null Comparisons

// IS NULL
.Where(x => x.DeletedAt == null)
// Generates: WHERE DeletedAt IS NULL

// IS NOT NULL
.Where(x => x.Email != null)
// Generates: WHERE Email IS NOT NULL

Negation (NOT operators)

// NOT LIKE
.Where(x => !x.Title.Contains("draft"))
// Generates: WHERE Title NOT LIKE '%draft%'

// Negated Equals
.Where(x => !x.Name.Equals("test"))
// Generates: WHERE Name <> 'test'

IN / NOT IN Clauses

var ids = new[] { 1, 2, 3 };
var statuses = new List<string> { "active", "pending" };

// IN clause from array/list
.Where(x => ids.Contains(x.Id))
// Generates: WHERE Id IN (1, 2, 3)

// NOT IN clause
.Where(x => !statuses.Contains(x.Status))
// Generates: WHERE Status NOT IN ('active', 'pending')

String Methods

// Equals method
.Where(x => x.Title.Equals("exact match"))
// Generates: WHERE Title = 'exact match'

// Case-insensitive Equals using StringComparison
.Where(x => x.Name.Equals("john", StringComparison.OrdinalIgnoreCase))
// Generates: WHERE LOWER(Name) = 'john'

// Case-insensitive comparison with ToLower/ToUpper
.Where(x => x.Title.ToLower().Contains("test"))
// Generates: WHERE LOWER(Title) LIKE '%test%'

.Where(x => x.Name.ToUpper() == "JOHN")
// Generates: WHERE UPPER(Name) = 'JOHN'

// Chained methods: ToLower().Equals()
.Where(x => x.Email.ToLower().Equals("admin@test.com"))
// Generates: WHERE LOWER(Email) = 'admin@test.com'

// Chained methods: ToLower().StartsWith()
.Where(x => x.Name.ToLower().StartsWith("a"))
// Generates: WHERE LOWER(Name) LIKE 'a%'

// Trim
.Where(x => x.Code.Trim() == "ABC")
// Generates: WHERE TRIM(Code) = 'ABC'

String Length

.Where(x => x.Title.Length > 10)
// Generates: WHERE LENGTH(Title) > 10
// Note: Uses LEN() for SQL Server, CHAR_LENGTH() for MySQL

Expression-based Helper Methods

All helper methods also support type-safe Expression syntax:

Method	Example
WhereNull(x => x.Prop)	.WhereNull(x => x.DeletedAt)
WhereNotNull(x => x.Prop)	.WhereNotNull(x => x.Email)
WhereIn(x => x.Prop, vals)	.WhereIn(x => x.Status, "active", "pending")
WhereIn(x => x.Prop, list)	.WhereIn(x => x.Id, userIds)
WhereNotIn(x => x.Prop, vals)	.WhereNotIn(x => x.Type, "deleted", "spam")
WhereBetween(x => x.Prop, a, b)	.WhereBetween(x => x.Price, 10m, 100m)
WhereLike(x => x.Prop, pattern)	.WhereLike(x => x.Name, "A%")
WhereContains(x => x.Prop, val)	.WhereContains(x => x.Description, "keyword")
WhereStartsWith(x => x.Prop, val)	.WhereStartsWith(x => x.Code, "PRD")
WhereEndsWith(x => x.Prop, val)	.WhereEndsWith(x => x.Email, "@gmail.com")

// Example: Type-safe query with Expression helpers
var orders = await db.From<Order>()
    .WhereNotNull(o => o.ShippedAt)
    .WhereBetween(o => o.Total, 100m, 1000m)
    .WhereIn(o => o.Status, "completed", "shipped")
    .OrderByDescending(o => o.CreatedAt)
    .GetAsync();

⚠️ Limitation: Only direct properties are supported in Expression-based methods. Nested navigation properties (e.g., x => x.Customer.Name) are NOT supported. Use Join() with string-based methods for filtering on related entities:

// ❌ NOT supported
.WhereEndsWith(x => x.Customer.Name, "Smith")

// ✅ Use Join instead
.Join("customers", "orders.customer_id", "customers.id")
.WhereEndsWith("customers.name", "Smith")

Ordering & Pagination

.OrderBy("CreatedAt")
.OrderByDescending("Priority")
.OrderBy(t => t.Name)
.Skip(20)
.Take(10)
.ForPage(3, 10)  // Page 3, 10 items per page

Terminal Methods

Method	Description
Get()	Returns IEnumerable<T>
GetAsync()	Returns List<T> async
GetStreamAsync()	Returns IAsyncEnumerable<T> (streaming)
First() / FirstAsync()	First row or throws
FirstOrDefault() / FirstOrDefaultAsync()	First row or null
Count() / CountAsync()	Row count
Exists() / ExistsAsync()	Any rows exist?

Aggregations

// String-based
var total = db.Table<Order>("orders").Sum<decimal>("Amount");
var avg = db.Table<Product>("products").Avg<double>("Price");
var max = db.Table<Order>("orders").Max<DateTime>("CreatedAt");
var min = db.Table<Product>("products").Min<decimal>("Price");

// Expression-based (type-safe)
var total = db.From<Order>().Sum(o => o.Amount);
var avg = db.From<Product>().Avg(p => p.Price);
var max = db.From<Order>().Max(o => o.CreatedAt);
var min = db.From<Product>().Min(p => p.Price);

// Async versions
var totalAsync = await db.From<Order>().SumAsync(o => o.Amount);
var maxAsync = await db.From<Order>().MaxAsync(o => o.CreatedAt);

Debugging & Configuration

// Get generated SQL without executing (for debugging/logging)
var sql = db.From<Order>()
    .Where(o => o.Status == "active")
    .ToSql();
// "SELECT * FROM orders WHERE Status = @p0"

// Get SQL with parameters
var (sql, parameters) = query.ToSqlWithParameters();

// Set command timeout
var orders = await db.From<Order>()
    .WithTimeout(TimeSpan.FromSeconds(60))
    .GetAsync();

// Or with seconds directly
db.From<Order>().WithTimeout(120).Get();

Select Projection

// Select specific columns using expression (type-safe)
var orders = db.From<Order>()
    .Select(o => new { o.Id, o.Total, o.Status })
    .Get();

// Generates: SELECT Id, Total, Status FROM orders

Multi-Provider Support

Auto-detects dialect from RoomDatabase.Dialect or DbSession.Dialect, or specify explicitly:

using RoomSharp.QueryExtensions.FluentQuery.Dialects;

db.Table<Todo>("todos", SqliteDialect.Instance)
db.Table<Todo>("todos", MySqlDialect.Instance)
db.Table<Todo>("todos", PostgreSqlDialect.Instance)
db.Table<Todo>("todos", SqlServerDialect.Instance)

Pessimistic Locking

Use LockForUpdate() when a row must be read and then updated safely inside the same transaction.

await db.RunInTransactionAsync(async () =>
{
    var order = await db.From<Order>()
        .Where(o => o.Id == orderId)
        .LockForUpdate()
        .FirstOrDefaultAsync();

    if (order is null)
        return;

    await db.From<Order>()
        .Where(o => o.Id == order.Id)
        .UpdateAsync(new { Status = "processing" });
});

LockForUpdate() requires an active transaction. Use RunInTransaction / RunInTransactionAsync, or start a transaction on a DbSession.

Provider behavior:

Provider	SQL behavior
PostgreSQL	Appends FOR UPDATE
MySQL	Appends FOR UPDATE
SQL Server	Adds WITH (UPDLOCK, ROWLOCK, HOLDLOCK) after the table name
SQLite	Not supported by default

SQLite does not support row-level FOR UPDATE semantics. By default RoomSharp throws NotSupportedException so the application does not silently run without a lock. If you intentionally want portable code that ignores the lock on unsupported providers, opt in explicitly:

var order = await db.From<Order>()
    .Where(o => o.Id == orderId)
    .LockForUpdate(ignoreIfUnsupported: true)
    .FirstOrDefaultAsync();

LockForUpdate() is only valid for fluent SELECT materialization methods such as Get, First, and Single. It is rejected for Count, aggregate methods, Update, Delete, and CachedAsync.

Joins

// INNER JOIN
var results = db.Table<OrderDto>("orders")
    .Join("customers", "orders.CustomerId", "customers.Id")
    .Select("orders.*", "customers.Name as CustomerName")
    .GetAsync();

// LEFT JOIN
db.Table<UserDto>("users")
    .LeftJoin("addresses", "users.AddressId", "addresses.Id")
    .Select("users.*", "addresses.City")
    .Get();

// Multiple JOINs
db.Table<OrderDto>("orders")
    .Join("customers", "orders.CustomerId", "customers.Id")
    .LeftJoin("products", "orders.ProductId", "products.Id")
    .Select("orders.Id", "customers.Name", "products.Title")
    .Get();

// With operator
db.Table<T>("a").Join("b", "a.Col", ">", "b.Col");  // a.Col > b.Col

// CROSS JOIN
db.Table<T>("a").CrossJoin("b");

Method	SQL Output
.Join(table, col1, col2)	INNER JOIN table ON col1 = col2
.Join(table, col1, op, col2)	INNER JOIN table ON col1 op col2
.LeftJoin(...)	LEFT JOIN ...
.RightJoin(...)	RIGHT JOIN ...
.CrossJoin(table)	CROSS JOIN table

⚠️ Column Ambiguity: When using Join(), columns with the same name in multiple tables (e.g., Id) will cause SQL errors. Always use table prefix in Select():

// ✅ Correct - use table prefix to avoid ambiguity
db.From<Order>()
    .Select("orders.id", "orders.total")
    .Join("customers", "orders.customer_id", "customers.id")
    .Get();

// ❌ Wrong - "ambiguous column name: Id" error
db.From<Order>()
    .Select(o => new { o.Id, o.Total })
    .Join("customers", ...)
    .Get();

Include (Eager Loading)

Load related entities efficiently with batched queries (solves N+1 problem):

// Expression-based (type-safe, recommended)
var orders = await db.From<Order>()
    .Include(o => o.Customer)
    .Include(o => o.Items)
    .Where(o => o.Status == "active")
    .GetAsync();

// String-based (fallback)
var orders = await db.From<Order>()
    .Include("customer")
    .GetAsync();

// Nested includes (string path)
var orders = await db.From<Order>()
    .Include("customer.address")
    .GetAsync();

// Nested includes (ThenInclude - type-safe)
var orders = await db.From<Order>()
    .Include(o => o.Customer)
    .ThenInclude<Customer, Address>(c => c.Address)
    .GetAsync();

// Also works with DbSession
await using var session = await db.OpenSessionAsync();
var items = await session.From<Order>()
    .Include(o => o.Customer)
    .GetAsync();

Filtered Includes

Filter related entities in-memory after loading. The filter parameter is optional — omit it to load all related entities.

// Load only active items for each order
var orders = await db.From<Order>()
    .Include(o => o.Items, q => q.Where("IsActive", true))
    .GetAsync();

// Filter with comparison operators
var customers = await db.From<Customer>()
    .Include(c => c.Orders, q => q.Where("Total", ">", 100))
    .GetAsync();

// Multiple filter conditions (AND)
var orders = await db.From<Order>()
    .Include(o => o.Items, q => q
        .Where("IsActive", true)
        .Where("Quantity", ">", 0))
    .GetAsync();

// Also works with DbSession
await using var session = await db.OpenSessionAsync();
var orders = await session.From<Order>()
    .Include(o => o.Items, q => q.Where("IsActive", true))
    .GetAsync();

Supported filter operators:

Operator	Example
=	.Where("Status", "Active")
!= / <>	.Where("Status", "!=", "Deleted")
>, >=, <, <=	.Where("Price", ">", 100)
IS NULL	.WhereNull("DeletedAt")
IS NOT NULL	.WhereNotNull("Name")
IN	.WhereIn("Status", "A", "B")
NOT IN	.WhereNotIn("Status", "X")
BETWEEN	.WhereBetween("Age", 18, 65)
LIKE	.WhereLike("Name", "%john%")

Note: Filtered includes use in-memory filtering — all related entities are loaded from the database first, then filtered in memory. This is safe and covers most use cases, but for very large relation sets, consider using explicit queries instead.

Note: Related properties must have [Relation] attribute defined.

// One-to-One / One-to-Many
public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    
    [Relation(Entity = typeof(Customer), 
              ParentColumn = nameof(CustomerId), 
              EntityColumn = nameof(Customer.Id))]
    public Customer? Customer { get; set; }
    
    [Relation(Entity = typeof(OrderItem), 
              ParentColumn = nameof(Id), 
              EntityColumn = "OrderId")]
    public List<OrderItem>? Items { get; set; }
}

// Many-to-Many (with junction table)
public class Student
{
    public int Id { get; set; }
    
    [Relation(Entity = typeof(Course), 
              ParentColumn = nameof(Id), 
              EntityColumn = nameof(Course.Id),
              AssociateBy = "student_courses")]  // Junction table
    public List<Course>? Courses { get; set; }
}

For junction tables whose column names differ from the parent/related entity keys, use [Junction]:

[Relation(Entity = typeof(Course),
          ParentColumn = nameof(Id),
          EntityColumn = nameof(Course.Id))]
[Junction(Value = typeof(StudentCourse),
          ParentColumn = "student_id",
          EntityColumn = "course_id")]
public List<Course>? Courses { get; set; }

Note: Include executes additional batched queries. It does not generate JOINs or a single large SQL statement.

---

API Reference

All methods are available as extension methods on both RoomDatabase and DbSession.

Query Methods

Query<T> - Returns all rows as IEnumerable<T>

// Basic usage
var todos = db.Query<Todo>("SELECT * FROM todos").AsList();

// With parameters
var activeTodos = db.Query<Todo>(
    "SELECT * FROM todos WHERE IsDone = @isDone AND CategoryId = @categoryId",
    new { isDone = false, categoryId = 5 }).AsList();

// With DbSession
await using var session = await db.OpenSessionAsync();
var todos = session.Query<Todo>("SELECT * FROM todos").AsList();

QueryAsync<T> - Returns all rows as List<T>

// Basic usage
var todos = await db.QueryAsync<Todo>("SELECT * FROM todos");

// With parameters
var activeTodos = await db.QueryAsync<Todo>(
    "SELECT * FROM todos WHERE IsDone = @isDone",
    new { isDone = false });

// With cancellation
var todos = await db.QueryAsync<Todo>(
    "SELECT * FROM todos",
    param: null,
    cancellationToken: cts.Token);

QueryStreamAsync<T> - Streams rows as IAsyncEnumerable<T>

// Stream large result sets without loading all into memory
await foreach (var todo in db.QueryStreamAsync<Todo>("SELECT * FROM todos"))
{
    Console.WriteLine(todo.Title);
}

// With parameters
await foreach (var todo in db.QueryStreamAsync<Todo>(
    "SELECT * FROM todos WHERE CategoryId = @categoryId",
    new { categoryId = 5 }))
{
    await ProcessTodoAsync(todo);
}

---

Single-Row Methods

QueryFirst<T> - Returns first row, throws if empty

// Throws InvalidOperationException if no rows
var todo = db.QueryFirst<Todo>(
    "SELECT * FROM todos WHERE Id = @id",
    new { id = 1 });

QueryFirstAsync<T> - Async version of QueryFirst

var todo = await db.QueryFirstAsync<Todo>(
    "SELECT * FROM todos WHERE Id = @id",
    new { id = 1 });

QueryFirstOrDefault<T> - Returns first row or default

// Returns null/default if no rows
var todo = db.QueryFirstOrDefault<Todo>(
    "SELECT * FROM todos WHERE Id = @id",
    new { id = 999 });

if (todo is null)
    Console.WriteLine("Todo not found");

QueryFirstOrDefaultAsync<T> - Async version of QueryFirstOrDefault

var todo = await db.QueryFirstOrDefaultAsync<Todo>(
    "SELECT * FROM todos WHERE Email = @email",
    new { email = "user@example.com" });

QuerySingle<T> - Returns exactly one row, throws otherwise

// Throws if 0 or 2+ rows
var user = db.QuerySingle<User>(
    "SELECT * FROM users WHERE Email = @email",
    new { email = "admin@example.com" });

QuerySingleAsync<T> - Async version of QuerySingle

var user = await db.QuerySingleAsync<User>(
    "SELECT * FROM users WHERE Id = @id",
    new { id = 1 });

QuerySingleOrDefault<T> - Returns one row or default, throws if 2+

// Returns null/default if no rows, throws if 2+ rows
var user = db.QuerySingleOrDefault<User>(
    "SELECT * FROM users WHERE Email = @email",
    new { email = "unique@example.com" });

QuerySingleOrDefaultAsync<T> - Async version of QuerySingleOrDefault

var user = await db.QuerySingleOrDefaultAsync<User>(
    "SELECT * FROM users WHERE Email = @email",
    new { email = "unique@example.com" });

---

Execute Methods

Execute - Executes non-query, returns affected rows

// INSERT
var inserted = db.Execute(
    "INSERT INTO todos (Title, IsDone) VALUES (@title, @isDone)",
    new { title = "New Todo", isDone = false });

// UPDATE
var updated = db.Execute(
    "UPDATE todos SET IsDone = @isDone WHERE Id = @id",
    new { isDone = true, id = 5 });

// DELETE
var deleted = db.Execute(
    "DELETE FROM todos WHERE IsDone = @isDone",
    new { isDone = true });

ExecuteAsync - Async version of Execute

var affected = await db.ExecuteAsync(
    "UPDATE todos SET Title = @title WHERE Id = @id",
    new { title = "Updated Title", id = 1 });

// With cancellation
var affected = await db.ExecuteAsync(
    "DELETE FROM todos WHERE CreatedAt < @date",
    new { date = DateTime.UtcNow.AddDays(-30) },
    cancellationToken: cts.Token);

---

Scalar Methods

ExecuteScalar<T> - Returns single scalar value

// COUNT
var count = db.ExecuteScalar<long>("SELECT COUNT(*) FROM todos");

// SUM
var total = db.ExecuteScalar<decimal>(
    "SELECT SUM(Amount) FROM orders WHERE UserId = @userId",
    new { userId = 1 });

// Single value
var title = db.ExecuteScalar<string>(
    "SELECT Title FROM todos WHERE Id = @id",
    new { id = 1 });

// Check existence
var exists = db.ExecuteScalar<long>(
    "SELECT COUNT(*) FROM users WHERE Email = @email",
    new { email = "test@example.com" }) > 0;

ExecuteScalarAsync<T> - Async version of ExecuteScalar

var count = await db.ExecuteScalarAsync<long>("SELECT COUNT(*) FROM todos");

var maxId = await db.ExecuteScalarAsync<long?>("SELECT MAX(Id) FROM todos");

---

Multi-Mapping Methods

Supports mapping 2 to 5 objects per row. The splitOn parameter specifies where each object starts:

• For 2 types: splitOn: "col" (single column)
• For 3+ types: splitOn: "col1,col2,..." (comma-separated)

Query<T1, T2, TResult> - Maps two objects per row

public record Order(long Id, decimal Total, long CustomerId);
public record Customer(long Id, string Name);

var orders = db.Query<Order, Customer, (Order, Customer)>(
    """
    SELECT o.Id, o.Total, o.CustomerId,
           c.Id AS CustId, c.Name
    FROM orders o
    JOIN customers c ON c.Id = o.CustomerId
    """,
    map: (order, customer) => (order, customer),
    splitOn: "CustId").AsList();

Query<T1, T2, T3, TResult> - Maps three objects per row

public record Order(long Id, decimal Total);
public record Customer(long Id, string Name);
public record Address(long Id, string City);

var rows = db.Query<Order, Customer, Address, (Order, Customer, Address)>(
    """
    SELECT o.Id, o.Total,
           c.Id AS CustId, c.Name,
           a.Id AS AddrId, a.City
    FROM orders o
    JOIN customers c ON c.Id = o.CustomerId
    JOIN addresses a ON a.Id = c.AddressId
    """,
    map: (o, c, a) => (o, c, a),
    splitOn: "CustId,AddrId").AsList();

Query<T1, T2, T3, T4, T5, TResult> - Maps up to 5 objects

var rows = db.Query<T1, T2, T3, T4, T5, TResult>(
    sql,
    map: (t1, t2, t3, t4, t5) => new Result(t1, t2, t3, t4, t5),
    splitOn: "Col2,Col3,Col4,Col5");

Important Multi-Mapping Notes:

• splitOn values must be explicit column aliases (e.g., CustId, not Id)
• Columns must be ordered in the SELECT to match the type parameters (T1, T2, T3...)
• Each splitOn column marks where the next type begins

---

Transaction Methods

BeginTransaction - Starts a new transaction

using var tx = db.BeginTransaction();
try
{
    db.Execute("INSERT INTO logs (Message) VALUES (@msg)", new { msg = "Start" });
    db.Execute("UPDATE counters SET Value = Value + 1");
    tx.Commit();
}
catch
{
    tx.Rollback();
    throw;
}

BeginTransactionAsync - Async version of BeginTransaction

await using var tx = await db.BeginTransactionAsync();
// ...

InTransaction - Runs action in transaction with auto commit/rollback

// With return value
var result = db.InTransaction(tx =>
{
    db.Execute("INSERT INTO todos (Title) VALUES (@title)", new { title = "New" });
    return db.ExecuteScalar<long>("SELECT last_insert_rowid()");
});

// Without return value
db.InTransaction(tx =>
{
    db.Execute("UPDATE todos SET IsDone = 1 WHERE Id = @id", new { id = 1 });
    db.Execute("INSERT INTO logs (Message) VALUES (@msg)", new { msg = "Completed" });
});

InTransactionAsync - Async version of InTransaction

var newId = await db.InTransactionAsync(async (tx, ct) =>
{
    await db.ExecuteAsync("INSERT INTO todos (Title) VALUES (@title)", new { title = "New" });
    return await db.ExecuteScalarAsync<long>("SELECT last_insert_rowid()");
});

// Without return value
await db.InTransactionAsync(async (tx, ct) =>
{
    await db.ExecuteAsync("DELETE FROM todos WHERE IsDone = 1");
    await db.ExecuteAsync("VACUUM");
});

---

DbSession Methods

All query/execute methods are also available on DbSession for Parallel mode:

// Open session
await using var session = await db.OpenSessionAsync();

// Query methods
var todos = await session.QueryAsync<Todo>("SELECT * FROM todos");
var todo = await session.QueryFirstOrDefaultAsync<Todo>("SELECT * FROM todos WHERE Id = @id", new { id = 1 });

// Execute methods
await session.ExecuteAsync("UPDATE todos SET Title = @title WHERE Id = @id", new { title = "New", id = 1 });

// Scalar methods
var count = await session.ExecuteScalarAsync<long>("SELECT COUNT(*) FROM todos");

// Multi-mapping
var rows = session.Query<Order, Customer, (Order, Customer)>(sql, (o, c) => (o, c), "CustomerId").AsList();

// Transactions
await session.InTransactionAsync(async (tx, ct) =>
{
    await session.ExecuteAsync("INSERT INTO todos (Title) VALUES (@title)", new { title = "A" });
    await session.ExecuteAsync("INSERT INTO todos (Title) VALUES (@title)", new { title = "B" });
});

---

Helper Methods

AsList - Materializes IEnumerable to List

var list = db.Query<Todo>("SELECT * FROM todos").AsList();

ToListAsync - Async materialization

var list = await db.QueryStreamAsync<Todo>("SELECT * FROM todos").ToListAsync();

---

Low-Level Row Access (RowReader)

For zero-allocation row processing, use ReadRaw / ReadRawAsync with RowReader:

using RoomSharp.QueryExtensions.Extensions;
using RoomSharp.QueryExtensions.LowLevel;

// Async version
await db.ReadRawAsync(
    "SELECT Id, Title, IsDone FROM todos",
    param: null,
    (in RowReader r) =>
    {
        var id = r.Get<long>(0);
        var title = r.Get<string>(1);
        var isDone = r.Get<bool>(2);
        Console.WriteLine($"{id}: {title} ({(isDone ? "done" : "pending")})");
    });

// Sync version
db.ReadRaw("SELECT Id, Title FROM todos", null, (in RowReader r) =>
{
    Console.WriteLine($"{r.Get<long>(0)}: {r.Get<string>(1)}");
});

When to use RowReader:

• Aggregation without creating objects (counting, summing)
• Streaming to files (CSV/JSON export)
• High-frequency queries where GC pressure matters
• Data transformation/migration between databases

RowReader API:

Method	Description
Get<T>(int ordinal)	Returns value at column index
IsDbNull(int ordinal)	Checks if column is NULL
FieldCount	Number of columns in row

---

Mapping Rules (POCO/DTO)

• Column-to-property matching is case-insensitive by default.
• [ColumnInfo(Name="...")] overrides the expected column name.
• [Ignore] excludes a property from mapping.
• [TypeConverter(ConverterType=...)] converts the provider value into the property type.
• Extra columns are ignored; missing columns keep the property at its default value.
• For non-scalar mapping, T must have a public parameterless constructor, and properties must be public settable.
• For scalar mapping (string or value types), the first column is read (null/DBNull becomes default).

public sealed class TodoDto
{
    public long Id { get; set; }
    
    [ColumnInfo(Name = "Title")]
    public required string Name { get; set; }
    
    [Ignore]
    public string? ComputedField { get; set; }
    
    [TypeConverter(ConverterType = typeof(JsonConverter<List<string>>))]
    public List<string> Tags { get; set; } = [];
}

---

Parameter Binding

• Parameters are provided as an anonymous object / POCO, and its public readable properties become parameters (cached per parameter type).
• Property names are used as the parameter name (most providers match @id in SQL to parameter name id).
• null becomes DBNull.Value.

// Anonymous object
var todos = await db.QueryAsync<Todo>(
    "SELECT * FROM todos WHERE CategoryId = @categoryId AND IsDone = @isDone",
    new { categoryId = 5, isDone = false });

// POCO parameter object
public record TodoFilter(int CategoryId, bool IsDone);

var filter = new TodoFilter(5, false);
var todos = await db.QueryAsync<Todo>(
    "SELECT * FROM todos WHERE CategoryId = @CategoryId AND IsDone = @IsDone",
    filter);

---

Concurrency & Thread-Safety

QueryExtensions is fully integrated with RoomSharp's concurrency model:

• Serialized mode (default): All extension methods automatically acquire the database gate before executing. This ensures thread-safe access to the shared connection.
• Parallel mode: Use DbSession overloads for concurrent operations. Each session has its own connection.

// Serialized mode - automatically thread-safe
var todos = await db.QueryAsync<Todo>("SELECT * FROM todos");

// Parallel mode - use sessions for concurrency
await using var session1 = await db.OpenSessionAsync();
await using var session2 = await db.OpenSessionAsync();

// These can run concurrently
var task1 = session1.QueryAsync<Todo>("SELECT * FROM todos WHERE CategoryId = 1");
var task2 = session2.QueryAsync<Todo>("SELECT * FROM todos WHERE CategoryId = 2");

await Task.WhenAll(task1.AsTask(), task2.AsTask());

---

Performance Notes

Aspect	Details
Cold path	First call per (T, SQL, column layout) builds an IL materializer; first call per parameter type builds an IL binder
Hot path	Reuses cached delegates; no reflection in the steady state
Gate overhead	~100-200ns per call in Serialized mode (negligible compared to SQL execution time)
Streaming	QueryStreamAsync holds the command/reader open for the duration of enumeration

---

Supported Type Patterns

The materializer supports:

• Classes with parameterless constructor (traditional POCOs)
• Record types with primary constructors: public record User(long Id, string Name);
• Classes with primary constructors (C# 12+): public class User(long id, string name)
• Constructor parameters are matched to column names (case-insensitive)
• Default parameter values are used when columns are missing

---

Fluent Update & Delete

// Update matching rows
var affected = await db.From<Order>()
    .Where(o => o.Status == "pending")
    .UpdateAsync(new { Status = "archived" });

// Delete matching rows
var deleted = await db.From<Log>()
    .Where(l => l.CreatedAt < DateTime.UtcNow.AddDays(-90))
    .DeleteAsync();

---

Query Caching (.CachedAsync())

var users = await db.From<User>()
    .Where(u => u.IsActive)
    .CachedAsync(TimeSpan.FromMinutes(5));

// Invalidate
db.From<User>().InvalidateTableCache();

---

Soft Delete Auto-Filter

Entities with [SoftDelete] auto-filter soft-deleted rows in From<T>() and Table<T>():

// Auto: WHERE deleted_at IS NULL
var users = await db.From<User>().GetAsync();

The same rule now applies to DbSession queries opened from RoomDatabase.OpenSession() / OpenSessionAsync():

await using var session = await db.OpenSessionAsync();

// Auto: WHERE deleted_at IS NULL
var users = await session.From<User>().GetAsync();

Global Filters With DbSession

When a DbSession is opened from a RoomDatabase, session-bound fluent queries inherit the database's registered global filters and bind their dynamic parameters correctly at execution time:

db.GlobalFilters.Add<Order>(
    "tenant_id = @tenantId",
    new Dictionary<string, Func<object?>>
    {
        ["tenantId"] = () => tenantContext.CurrentTenantId
    });

await using var session = await db.OpenSessionAsync();

var orders = await session.From<Order>()
    .Where(o => o.Status == "pending")
    .GetAsync();

This keeps FluentQuerySession<T> aligned with FluentQuery<T> for multi-tenant and soft-delete scenarios.

---

Changelog

v0.6.0 (Unreleased)

✨ New Features

• Filtered Includes — Include(nav, filter) now supports in-memory filtering of related entities:

  .Include(o => o.Items, q => q.Where("IsActive", true))
  

  Supports all Where operators (=, !=, >, <, IN, BETWEEN, LIKE, IS NULL, etc.). Works with both FluentQuery (RoomDatabase) and FluentQuerySession (DbSession). Previously threw NotSupportedException.
• DbSession nested includes — ThenInclude(...) now follows the requested relation path recursively on FluentQuerySession<T> as well, so session-based eager loading matches the database-bound path more closely.

🏗️ Architecture

• FluentQueryCore<T> — Extracted all shared SQL-building logic (state, clause builders, SQL generation, parameter binding, validation) into a new internal struct FluentQueryCore<T>.
  • FluentQuery<T> and FluentQuerySession<T> now use composition (_core field) instead of duplicating ~300 lines of SQL generation code.
  • Adding new query features now requires changes to one file (FluentQueryCore.cs) instead of two.
  • Zero impact on public API — all method signatures remain identical.
  • struct chosen over class for zero-allocation query building on the hot path.

🔧 Internal

• Removed ~300 lines of duplicated SQL-building code from FluentQuerySession.cs
• Unified Build(), BuildCount(), BuildExists(), BuildAggregate(), BuildUpdate(), BuildDelete(), BuildInsert() into FluentQueryCore
• Unified BuildWhere(), BuildJoins(), BuildOrderBy(), BuildGroupBy(), BuildHaving() helpers
• Unified BindParameters(), ValidateLockSupported(), ThrowIfLocked(), AppendLockTableHint(), AppendLockClause()
• Added IncludeClause.Filter property (Delegate?) for storing filter predicates