--- title: "Query Extensions" description: "Dapper-like API for ad-hoc SQL queries with fluent query builder and eager loading." canonical: "https://roomsharp.dev/docs/v0.5.4/query-extensions" source: "src/content/v0.5.4/query-extensions.mdx" --- # RoomSharp.QueryExtensions *Designed for developers who want control, not magic.* RoomSharp.QueryExtensions is a companion package to RoomSharp that adds a Dapper-like API on top of `RoomDatabase` for ad-hoc SQL: - Query POCOs/DTOs/records without declaring a DAO method - **Fluent Query Builder** - Laravel-style `db.From().Where().OrderBy().Get()` - **Eager Loading** - Include related entities with batched queries - Stream results with `IAsyncEnumerable` - Run raw commands and scalars - Multi-mapping up to 5 types with `splitOn` - Drop down to low-level, allocation-free row access (`RowReader`) ## Installation ```bash dotnet add package RoomSharp.QueryExtensions ``` ## When to 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 ## Production Updates in v0.5.4 The `v0.5.4` release improves QueryExtensions behavior in areas that matter for production correctness: - `DbSession.InTransaction*` now commits, rolls back, and clears only transactions it started. - Expression predicates preserve grouping, so `(A || B) && C` is not flattened into SQL with different precedence. - String-based table/column identifiers are validated and dialect-quoted in fluent SQL construction paths. - Filtered includes pass a `RelationLoadFilter` into relation loading where possible. - Async raw multi-mapping validates provider command/reader support before using async reader APIs. For lower-level details, see [v0.5.4 Technical Details](/docs/v0.5.4/release-notes-0-5-4-details). --- ## 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 ```csharp using RoomSharp.QueryExtensions.FluentQuery; // String-based var todos = await db.Table("todos") .Where("IsDone", false) .Where("CategoryId", ">", 5) .OrderBy("CreatedAt") .Take(20) .GetAsync(); // Expression-based (Type-safe) var todos = await db.From() .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 ```csharp // 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 (evaluated at runtime) .Where(t => t.CreatedAt > DateTime.Now.AddDays(-7)) ``` ### Advanced Expression Features Expression predicates cover common C# patterns and translate them into parameterized SQL: ```csharp // Compound expressions keep grouping .Where(x => (x.Status == Status.Active || x.Status == Status.Pending) && x.Price > 50) // Enum comparisons .Where(x => x.Status == Status.Active) // Null checks .Where(x => x.DeletedAt == null) .Where(x => x.Email != null) // Negation .Where(x => !x.Title.Contains("draft")) .Where(x => !x.Name.Equals("test")) // IN / NOT IN from arrays or lists var ids = new[] { 1, 2, 3 }; .Where(x => ids.Contains(x.Id)) // String transforms .Where(x => x.Name.Equals("john", StringComparison.OrdinalIgnoreCase)) .Where(x => x.Title.ToLower().Contains("test")) .Where(x => x.Code.Trim() == "ABC") .Where(x => x.Title.Length > 10) ``` String length uses provider-aware SQL (`LENGTH`, `LEN`, or `CHAR_LENGTH`) depending on the active dialect. ### Expression-based Helper Methods | 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")` | | `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")` | **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. ### Ordering & Pagination ```csharp .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` | | `GetAsync()` | Returns `List` async | | `GetStreamAsync()` | Returns `IAsyncEnumerable` (streaming) | | `First()` / `FirstAsync()` | First row or throws | | `FirstOrDefault()` / `FirstOrDefaultAsync()` | First row or null | | `Count()` / `CountAsync()` | Row count | | `Exists()` / `ExistsAsync()` | Any rows exist? | ### Aggregations ```csharp // String-based var total = db.Table("orders").Sum("Amount"); var avg = db.Table("products").Avg("Price"); var max = db.Table("orders").Max("CreatedAt"); var min = db.Table("products").Min("Price"); // Expression-based (type-safe) var total = db.From().Sum(o => o.Amount); var avg = db.From().Avg(p => p.Price); var max = db.From().Max(o => o.CreatedAt); var min = db.From().Min(p => p.Price); // Async versions var totalAsync = await db.From().SumAsync(o => o.Amount); ``` ### Select Projection ```csharp // Select specific columns using expression (type-safe) var orders = db.From() .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: ```csharp using RoomSharp.QueryExtensions.FluentQuery.Dialects; db.Table("todos", SqliteDialect.Instance) db.Table("todos", MySqlDialect.Instance) db.Table("todos", PostgreSqlDialect.Instance) db.Table("todos", SqlServerDialect.Instance) ``` ### Debugging ```csharp // Get generated SQL without executing var sql = db.From() .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() .WithTimeout(TimeSpan.FromSeconds(60)) .GetAsync(); ``` --- ## Joins ```csharp // INNER JOIN var results = db.Table("orders") .Join("customers", "orders.CustomerId", "customers.Id") .Select("orders.*", "customers.Name as CustomerName") .GetAsync(); // LEFT JOIN db.Table("users") .LeftJoin("addresses", "users.AddressId", "addresses.Id") .Select("users.*", "addresses.City") .Get(); // Multiple JOINs db.Table("orders") .Join("customers", "orders.CustomerId", "customers.Id") .LeftJoin("products", "orders.ProductId", "products.Id") .Select("orders.Id", "customers.Name", "products.Title") .Get(); // CROSS JOIN db.Table("a").CrossJoin("b"); ``` | Method | SQL Output | |--------|------------| | `.Join(table, col1, col2)` | `INNER JOIN table ON col1 = 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()`. --- ## Include (Eager Loading) Load related entities efficiently with batched queries (solves N+1 problem): ```csharp // Expression-based (type-safe, recommended) var orders = await db.From() .Include(o => o.Customer) .Include(o => o.Items) .Where(o => o.Status == "active") .GetAsync(); // String-based (fallback) var orders = await db.From() .Include("customer") .GetAsync(); // Nested includes (ThenInclude - type-safe) var orders = await db.From() .Include(o => o.Customer) .ThenInclude(c => c.Address) .GetAsync(); // Filtered include var activeOrders = await db.From() .Include(o => o.Items, q => q.Where("IsActive", true)) .GetAsync(); ``` Related properties must have `[Relation]` attribute defined. ### Filtered Includes Filtered includes let you keep the fluent eager-loading shape while limiting the related objects assigned to each parent: ```csharp // Load only active items for each order var orders = await db.From() .Include(o => o.Items, q => q.Where("IsActive", true)) .GetAsync(); // Comparison operators var customers = await db.From() .Include(c => c.Orders, q => q.Where("Total", ">", 100)) .GetAsync(); // Multiple conditions are combined with AND var ordersWithAvailableItems = await db.From() .Include(o => o.Items, q => q .Where("IsActive", true) .Where("Quantity", ">", 0)) .GetAsync(); // DbSession supports the same include filter API await using var session = await db.OpenSessionAsync(); var sessionOrders = await session.From() .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%")` | Filtered includes are applied while loading relations and can still require loading more related rows than a hand-written SQL query. For very large relations, prefer an explicit query or join that pushes the whole filter into SQL. ```csharp 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? 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? Courses { get; set; } } ``` --- ## Raw Query API ### Query Methods ```csharp // Basic query var todos = db.Query("SELECT * FROM todos").AsList(); // With parameters var todos = await db.QueryAsync( "SELECT * FROM todos WHERE IsDone = @isDone", new { isDone = false }); // Streaming await foreach (var todo in db.QueryStreamAsync("SELECT * FROM todos")) { Console.WriteLine(todo.Title); } ``` ### Single-Row Methods | Method | Behavior | |--------|----------| | `QueryFirst` | Returns first row, throws if empty | | `QueryFirstOrDefault` | Returns first row or `default` | | `QuerySingle` | Returns exactly one row, throws otherwise | | `QuerySingleOrDefault` | Returns one row or `default`, throws if 2+ | ### Execute Methods ```csharp // INSERT var inserted = db.Execute( "INSERT INTO todos (Title, IsDone) VALUES (@title, @isDone)", new { title = "New Todo", isDone = false }); // UPDATE var updated = await db.ExecuteAsync( "UPDATE todos SET IsDone = @isDone WHERE Id = @id", new { isDone = true, id = 5 }); // Scalar var count = await db.ExecuteScalarAsync("SELECT COUNT(*) FROM todos"); ``` ### Helper Methods #### `AsList` - Materializes IEnumerable to List ```csharp var list = db.Query("SELECT * FROM todos").AsList(); ``` #### `ToListAsync` - Async materialization ```csharp var list = await db.QueryStreamAsync("SELECT * FROM todos").ToListAsync(); ``` --- ## Multi-Mapping Supports mapping **2 to 5 objects** per row: ```csharp public record Order(long Id, decimal Total, long CustomerId); public record Customer(long Id, string Name); var orders = db.Query( """ 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(); ``` - `splitOn` values must be explicit column aliases (e.g., `CustId`, not `Id`) - Columns must be ordered in SELECT to match type parameters - Each `splitOn` column marks where the next type begins --- ## Transactions ```csharp // Using InTransactionAsync var newId = await db.InTransactionAsync(async (tx, ct) => { await db.ExecuteAsync("INSERT INTO todos (Title) VALUES (@title)", new { title = "New" }); return await db.ExecuteScalarAsync("SELECT last_insert_rowid()"); }); // Manual transaction await using var tx = await db.BeginTransactionAsync(); try { await db.ExecuteAsync("..."); tx.Commit(); } catch { tx.Rollback(); throw; } ``` ### Pessimistic Locking Use `LockForUpdate()` when a row must be read and then updated safely inside the same transaction: ```csharp await db.RunInTransactionAsync(async () => { var order = await db.From() .Where(o => o.Id == orderId) .LockForUpdate() .FirstOrDefaultAsync(); if (order is null) return; await db.From() .Where(o => o.Id == order.Id) .UpdateAsync(new { Status = "processing" }); }); ``` `LockForUpdate()` requires an active transaction. It is provider-aware: | Provider | SQL behavior | |----------|--------------| | PostgreSQL | Appends `FOR UPDATE` | | MySQL | Appends `FOR UPDATE` | | SQL Server | Adds `WITH (UPDLOCK, ROWLOCK, HOLDLOCK)` after the table name | | SQLite | Throws by default because SQLite has no row-level `FOR UPDATE` equivalent | For portable code that intentionally ignores the lock on unsupported providers: ```csharp var order = await db.From() .Where(o => o.Id == orderId) .LockForUpdate(ignoreIfUnsupported: true) .FirstOrDefaultAsync(); ``` `LockForUpdate()` is valid only for fluent SELECT materialization methods such as `Get`, `First`, and `Single`. It is rejected for count, aggregate, cached, update, and delete operations. --- ## Low-Level Row Access (RowReader) For **zero-allocation** row processing: ```csharp using RoomSharp.QueryExtensions.LowLevel; await db.ReadRawAsync( "SELECT Id, Title, IsDone FROM todos", param: null, (in RowReader r) => { var id = r.Get(0); var title = r.Get(1); var isDone = r.Get(2); Console.WriteLine($"{id}: {title}"); }); ``` | Method | Description | |--------|-------------| | `Get(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`). ```csharp 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>))] public List 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`. ```csharp // Anonymous object var todos = await db.QueryAsync( "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( "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. ```csharp // Serialized mode - automatically thread-safe var todos = await db.QueryAsync("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("SELECT * FROM todos WHERE CategoryId = 1"); var task2 = session2.QueryAsync("SELECT * FROM todos WHERE CategoryId = 2"); await Task.WhenAll(task1.AsTask(), task2.AsTask()); ``` --- ## 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 ```csharp // Update matching rows var affected = await db.From() .Where(o => o.Status == "pending") .UpdateAsync(new { Status = "archived" }); // Delete matching rows var deleted = await db.From() .Where(l => l.CreatedAt < DateTime.UtcNow.AddDays(-90)) .DeleteAsync(); ``` --- ## Query Caching Use `.CachedAsync(...)` for read-heavy fluent queries with a short time-to-live: ```csharp var users = await db.From() .Where(u => u.IsActive) .CachedAsync(TimeSpan.FromMinutes(5)); db.From().InvalidateTableCache(); ``` `LockForUpdate()` is rejected for cached queries because row locks must be tied to live transaction execution. --- ## Soft Delete and Global Filters Entities marked with `[SoftDelete]` are automatically filtered in fluent queries: ```csharp // Adds deleted_at IS NULL or is_deleted = 0 based on the entity attribute var users = await db.From().GetAsync(); ``` `DbSession` fluent queries opened from a `RoomDatabase` also inherit soft-delete and global-filter behavior: ```csharp db.GlobalFilters.Add( "tenant_id = @tenantId", new Dictionary> { ["tenantId"] = () => tenantContext.CurrentTenantId }); await using var session = await db.OpenSessionAsync(); var orders = await session.From() .Where(o => o.Status == "pending") .GetAsync(); ``` --- ## DbSession Methods All methods are also available on `DbSession` for Parallel mode: ```csharp // Open session await using var session = await db.OpenSessionAsync(); // Query methods var todos = await session.QueryAsync("SELECT * FROM todos"); var todo = await session.QueryFirstOrDefaultAsync("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("SELECT COUNT(*) FROM todos"); // Multi-mapping var rows = session.Query(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" }); }); ``` --- ## Performance Notes | Aspect | Details | |--------|---------| | **Cold path** | First call builds IL materializer; first param type builds IL binder | | **Hot path** | Reuses cached delegates; no reflection in steady state | | **Gate overhead** | ~100-200ns per call in Serialized mode | | **Streaming** | `QueryStreamAsync` holds command/reader open for enumeration |