--- 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.4.7/query-extensions" source: "src/content/v0.4.7/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 --- ## 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)) ``` ### 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(); ``` Related properties must have `[Relation]` attribute defined. ```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; } ``` --- ## 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 | Method | Description | |--------|-------------| | `Get(int ordinal)` | Returns value at column index | | `IsDbNull(int ordinal)` | Checks if column is NULL | | `FieldCount` | Number of columns in row | --- --- ## 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 |