BlockSoftware.QueryKit 0.10.0

QueryKit

Lightweight, dependency-light SQL builder + CRUD extensions for Dapper. Dialect-aware. Attribute-friendly. Zero ceremony.

• NuGet: Rowan.QueryKit
• Targets: net8.0 (recommended) + netstandard2.0
• Databases: SQL Server, PostgreSQL, MySQL/MariaDB, SQLite, Oracle, Db2

Table of contents

• Why QueryKit?
• Install
• Quick start
• Mapping entities with attributes
• Reading data
  • Get a single row by key
  • GetList with anonymous filters
  • GetList with a raw WHERE fragment
  • GetList for all rows
  • GetListPaged
  • RecordCount
• Writing data
  • Insert
  • Update
  • Optimistic concurrency with UpdateWithVersion
  • Delete
  • DeleteList
• Stored procedures
• Async API
• Choosing a dialect
• Composite keys
• Transactions
• Logging generated SQL
• Safety notes

Why QueryKit?

• Tiny surface — extension methods on IDbConnection (sync + async).
• Predictable SQL — no LINQ provider, no expression trees pretending to be queries; you can log every statement.
• Dialect-aware — identifier quoting, identity retrieval, and paging are picked per database.
• Attribute-friendly — supports [Key], [Column], [Table], [NotMapped], [IgnoreInsert], [IgnoreUpdate], [IgnoreSelect], [IgnoreCrud], [ReadOnly], [Required], [Version].
• Batteries included — paging, composite keys, anonymous filters, raw WHERE fragments, optimistic concurrency, and stored-procedure execution.

Install

dotnet add package Rowan.QueryKit

QueryKit depends on Dapper. Bring your own IDbConnection (e.g. SqlConnection, NpgsqlConnection, MySqlConnection, SqliteConnection, OracleConnection).

Quick start

using System.Data;
using Microsoft.Data.SqlClient;
using QueryKit.Attributes;
using QueryKit.Dialects;
using QueryKit.Extensions;

// Once at startup — defaults to SQL Server if you skip this.
ConnectionExtensions.UseDialect(Dialect.SQLServer);

[Table("Users", "dbo")]
public class User
{
    [Key]
    public int Id { get; set; }

    [Column("first_name")]
    public string FirstName { get; set; } = "";

    [Column("last_name")]
    public string LastName { get; set; } = "";

    public DateTime CreatedUtc { get; set; }

    [NotMapped]
    public string FullName => $"{FirstName} {LastName}";
}

using IDbConnection db = new SqlConnection(connectionString);
db.Open();

var newId = db.Insert<int, User>(new User
{
    FirstName = "Ada",
    LastName  = "Lovelace",
    CreatedUtc = DateTime.UtcNow
});

var ada    = db.Get<User>(newId);
var lovela = db.GetList<User>(new { LastName = "Lovelace" });
var page1  = db.GetListPaged<User>(pageNumber: 1, rowsPerPage: 25,
                                   conditions: "where CreatedUtc > @since",
                                   orderBy:   "CreatedUtc DESC",
                                   parameters: new { since = DateTime.UtcNow.AddDays(-7) });

ada!.LastName = "King-Lovelace";
db.Update(ada);

db.Delete<User>(ada.Id);

Mapping entities with attributes

Example

[Table("Orders")]
public class Order
{
    [Key]
    public Guid Id { get; set; }                 // auto-filled with a sequential GUID on insert

    [Column("order_no")]
    public string OrderNumber { get; set; } = "";

    [ReadOnly]
    public DateTime CreatedUtc { get; set; }     // selected and inserted, never updated

    [IgnoreInsert]
    public DateTime? UpdatedUtc { get; set; }    // populated by a trigger or default

    [IgnoreSelect]
    public string? InternalNotes { get; set; }   // written but never read back

    [NotMapped]
    public bool IsRecent => CreatedUtc > DateTime.UtcNow.AddDays(-1);

    [Version]
    public long Revision { get; set; }
}

Reading data

Get a single row by key

var user = db.Get<User>(42);
// SELECT [Id], [first_name], [last_name], [CreatedUtc] FROM [dbo].[Users] WHERE [Id] = @Id

For composite keys, pass an object that exposes each key property:

var line = db.Get<OrderLine>(new { OrderId = orderId, LineNo = 3 });

GetList with anonymous filters

Equality filters built from an anonymous object are the simplest way to query:

var active = db.GetList<User>(new { IsActive = true });

// With strongly-typed ORDER BY
var sorted = db.GetList<User>(
    new { IsActive = true },
    orderBy: new[]
    {
        ConnectionExtensions.OrderByDescending<User>(u => u.CreatedUtc),
        ConnectionExtensions.OrderByAscending<User>(u => u.LastName)
    });

GetList with a raw WHERE fragment

When you need anything beyond equality, pass a SQL fragment plus parameters:

var recent = db.GetList<User>(
    conditions: "where CreatedUtc >= @since and LastName like @name",
    parameters: new { since = DateTime.UtcNow.AddMonths(-1), name = "Lov%" },
    orderBy: "LastName ASC, FirstName ASC");

The orderBy string is validated against an allowlist of mapped columns/property names per entity, so you can pass user-supplied sort tokens without opening up SQL injection. Quoted forms ([col], "col", `col`) and Schema.Column are accepted.

GetList for all rows

var everyone = db.GetList<User>();

GetListPaged

Dialect-aware pagination. Pages are 1-indexed.

var page = db.GetListPaged<User>(
    pageNumber: 2,
    rowsPerPage: 50,
    conditions: "where IsActive = @active",
    orderBy: "CreatedUtc DESC, Id ASC",
    parameters: new { active = true });

If you omit orderBy, QueryKit uses the first [Key] property. Throws NotSupportedException for dialects that have no paging template configured.

RecordCount

var total       = db.RecordCount<User>();
var activeCount = db.RecordCount<User>("where IsActive = @active", new { active = true });

Writing data

Insert

// Returns the generated key as object (for callers that don't care about its type)
db.Insert(new User { FirstName = "Ada", LastName = "Lovelace" });

// Strongly-typed key
int id = db.Insert<int, User>(new User { FirstName = "Ada", LastName = "Lovelace" })!;

// Guid key — QueryKit fills in a monotonically increasing sequential GUID if Id is empty
var orderId = db.Insert<Guid, Order>(new Order { OrderNumber = "ORD-1001" })!;

// String key — must be supplied by you
db.Insert<string, ApiKey>(new ApiKey { Id = "ak_live_abc", OwnerId = userId });

After insert with an integer/identity key, the entity's key property is updated with the generated identity value. If a [Version] property exists and is 0, it is initialised to 1.

Update

Updates every mapped column except keys, [ReadOnly], and [IgnoreUpdate]:

ada.LastName = "King-Lovelace";
int rows = db.Update(ada);

Optimistic concurrency with UpdateWithVersion

Add a long Version column (or any long property tagged [Version]):

public class Document
{
    [Key] public int Id { get; set; }
    public string Title { get; set; } = "";
    [Version] public long Revision { get; set; }
}

Then update with the version you originally read:

var doc = db.Get<Document>(id)!;
doc.Title = "New title";

int rows = db.UpdateWithVersion(doc, expectedVersion: doc.Revision);
if (rows == 0)
    throw new DBConcurrencyException("Document was modified by someone else.");

QueryKit appends Revision = Revision + 1 to the SET clause and AND Revision = @ExpectedVersion to the WHERE clause, so the update only succeeds if the row still has the version you expected.

Delete

// By entity (uses the key property values)
db.Delete(ada);

// By primary-key value
db.Delete<User>(42);

// Composite key
db.Delete<OrderLine>(new { OrderId = orderId, LineNo = 3 });

DeleteList

// Anonymous filter — at least one property is required to prevent accidental full-table deletes
db.DeleteList<User>(new { IsActive = false });

// Raw WHERE fragment with parameters
db.DeleteList<User>(
    "where CreatedUtc < @cutoff",
    new { cutoff = DateTime.UtcNow.AddYears(-2) });

// Truly delete every row — opt in explicitly
db.DeleteList<User>("1=1");

Calling DeleteList<T>(null) or with an empty filter object throws — full-table deletes have to be intentional.

Stored procedures

var orders = db.ExecuteStoredProcedure<Order>(
    "dbo.GetOrdersForUser",
    new { UserId = userId });

The async version (ExecuteStoredProcedureAsync<T>) follows the same shape.

Async API

Every CRUD method has an async counterpart that accepts a CancellationToken:

var user  = await db.GetAsync<User>(id, cancellationToken: ct);
var users = await db.GetListAsync<User>(new { IsActive = true }, cancellationToken: ct);
var page  = await db.GetListPagedAsync<User>(1, 25, "", "Id ASC", cancellationToken: ct);
var newId = await db.InsertAsync<int, User>(user, cancellationToken: ct);
await db.UpdateAsync(user, cancellationToken: ct);
await db.UpdateWithVersionAsync(doc, expectedVersion: doc.Revision, cancellationToken: ct);
await db.DeleteAsync<User>(id, cancellationToken: ct);
var n = await db.RecordCountAsync<User>(cancellationToken: ct);

The async overloads emit identical SQL to their sync counterparts.

Choosing a dialect

Set the dialect once at startup:

ConnectionExtensions.UseDialect(Dialect.PostgreSQL);

UseDialect clears QueryKit's column allowlist cache, so it's safe to call again — but the expectation is to call it once during process startup.

Composite keys

Mark every key property with [Key]. Pass keys as anonymous objects to Get, Delete, etc.:

public class OrderLine
{
    [Key] public Guid OrderId { get; set; }
    [Key] public int  LineNo  { get; set; }
    public int  ProductId { get; set; }
    public int  Quantity  { get; set; }
}

var line = db.Get<OrderLine>(new { OrderId = orderId, LineNo = 3 });
db.Delete<OrderLine>(new { OrderId = orderId, LineNo = 3 });

Transactions

Every CRUD method takes an optional IDbTransaction:

using var tx = db.BeginTransaction();

var orderId = db.Insert<int, Order>(new Order { OrderNumber = "ORD-1" }, transaction: tx);
db.Insert(new OrderLine { OrderId = orderId, LineNo = 1, ProductId = 7, Quantity = 2 }, transaction: tx);
db.Insert(new OrderLine { OrderId = orderId, LineNo = 2, ProductId = 9, Quantity = 1 }, transaction: tx);

tx.Commit();

Logging generated SQL

By default QueryKit writes generated SQL to System.Diagnostics.Trace while a debugger is attached. You can hook in your own logger:

// Microsoft.Extensions.Logging
ConnectionExtensions.Logger = msg => logger.LogDebug(msg);

// Or silence it entirely
ConnectionExtensions.Logger = null;

The logger is invoked for every CRUD method with the SQL that QueryKit built (parameters are bound by Dapper, not interpolated into the string).

Safety notes

• ORDER BY is allowlisted. Raw orderBy strings to GetList and GetListPaged are validated against the entity's mapped columns and property names — invalid tokens throw ArgumentException. Direction is restricted to ASC / DESC.
• DeleteList refuses empty filters. Calling it without a filter throws. To delete every row, pass "1=1" explicitly so the intent is in your code.
• Identifiers are quoted per-dialect, with the dialect's escape character doubled inside identifiers (e.g. ] → ]] in SQL Server).
• Insert will populate empty Guid keys with a sequential GUID, but throws on a missing string key — string keys must be supplied by you.

License

See LICENSE.