RawQueryKit 1.0.0

RawQueryKit

RawQueryKit is a lightweight, high-performance, and secure C# library that brings Sieve-style filtering, sorting, and pagination to raw SQL, ADO.NET, and Dapper queries — without requiring Entity Framework.

It parses URL query strings, validates field-level access permissions, prevents SQL injection via parameterized queries, and outputs SQL clauses tailored to your specific database dialect.

Table of Contents

• Features
• Supported Databases
• Installation
• Quick Start
  • 1. Decorate Your Model
  • 2. Use in a Controller or Service
  • 3. Usage with Dapper
  • 4. Fluent Configuration (No Attributes)
• Querying the API
• Filter Operators Reference
• API Reference
  • RawQueryFieldAttribute
  • RawQueryOptions
  • RawQueryResult
  • RawQuery Static Factory
• Error Handling
• Code Contributors
• License

Features

• Attribute & Fluent Configuration — Mark properties as filterable/sortable using [RawQueryField] attributes, or configure them programmatically with a fluent builder API.
• Zero DI Setup Required — Use the high-performance RawQuery.For<T>() static factory anywhere in your codebase; processors are cached automatically.
• SQL Injection Prevention — All filter values are emitted as named parameters (@rq_f_...) and never interpolated into the SQL string.
• AND/OR Precedence Parsing — Comma-separated terms within a group are ANDed; pipe-separated groups are ORed. Grouping is emitted as bracketed SQL.
• Rich Filter Operator Set — 24 operators covering equality, comparison, IN/NOT IN, LIKE-based pattern matching, case-insensitive variants, and IS NULL / IS NOT NULL.
• Smart Comma Parsing — Intelligently distinguishes between commas as AND-separators and commas that are part of a string value (e.g. name==Hello, World).
• Dialect-Aware Pagination — Emits OFFSET … FETCH NEXT for SQL Server and LIMIT … OFFSET for PostgreSQL, MySQL, and SQLite.
• Default Sort Fallbacks — Configure fallback sort columns for when the client provides no sorts parameter.
• Custom SQL Expressions — Map a field to any SQL expression, not just a column name (e.g. COALESCE(p.Description, '')).

Supported Databases

RawQueryKit is database-agnostic at the query-building level. Pass the appropriate SqlDialect and it handles the rest:

Note: SQL Server requires an ORDER BY clause when paginating. If no sort is provided (and no default sort is configured), RawQueryKit automatically injects ORDER BY (SELECT NULL) to satisfy this constraint.

Installation

Core Package

dotnet add package RawQueryKit

Dapper Integration (optional)

dotnet add package RawQueryKit.Dapper

Quick Start

1. Decorate Your Model

Apply [RawQueryField] to any properties you want to expose for filtering or sorting. The Name maps to the URL parameter name, and Column maps to the actual SQL column or expression.

using RawQueryKit;

public class Product
{
    [RawQueryField(Name = "id", Column = "p.Id", CanFilter = true, CanSort = true)]
    public Guid Id { get; set; }

    [RawQueryField(Name = "name", Column = "p.Name", CanFilter = true, CanSort = true)]
    public string Name { get; set; } = null!;

    [RawQueryField(Name = "price", Column = "p.Price", CanFilter = true, CanSort = true)]
    public decimal Price { get; set; }

    [RawQueryField(Name = "category_id", Column = "p.CategoryId", CanFilter = true, CanSort = false)]
    public int? CategoryId { get; set; }

    // IsDefaultSort = true: used when no ?sorts= is provided by the client
    [RawQueryField(Name = "created_at", Column = "p.CreatedAt", CanFilter = false, CanSort = true,
                   IsDefaultSort = true, DefaultSortDescending = true)]
    public DateTime CreatedAt { get; set; }
}

2. Use in a Controller or Service

RawQuery.For<T>() returns a cached processor — no DI registration required.

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Data.Sqlite;
using RawQueryKit;

[ApiController]
[Route("api/[controller]")]
public class ProductsController : ControllerBase
{
    private readonly AppDbContext _db;

    public ProductsController(AppDbContext db) => _db = db;

    [HttpGet]
    public async Task<IActionResult> GetProducts([FromQuery] RawQueryOptions options)
    {
        // 1. Build SQL clauses and parameters
        var result = RawQuery.For<Product>(SqlDialect.Sqlite).Build(options);

        // 2. Map parameters to the driver-specific parameter type
        var sqlParams = result.Parameters
            .Select(p => new SqliteParameter(p.Key, p.Value ?? DBNull.Value))
            .ToArray();

        // 3. Assemble the final SQL
        string sql = $@"
            SELECT *
            FROM Products p
            {result.GetWhereClause(includeWhereKeyword: true)}
            {result.GetOrderByClause(includeOrderByKeyword: true)}
            {result.GetPaginationClause()}
        ";

        // 4. Execute
        var products = await _db.Products.FromSqlRaw(sql, sqlParams).ToListAsync();
        return Ok(products);
    }
}

3. Usage with Dapper (RawQueryKit.Dapper)

The companion package provides a single extension method that handles the entire pipeline — building clauses, mapping parameters to DynamicParameters, and appending the SQL — in one call.

using RawQueryKit.Dapper;

// In your service or repository:
var products = await dbConnection.QueryWithRawQueryKitAsync<Product>(
    baseSql:  "SELECT * FROM Products p",
    options:  options,
    dialect:  SqlDialect.PostgreSql
);

The generated SQL is equivalent to:

SELECT * FROM Products p
 WHERE (p.Price > @rq_f_Price_0)
 ORDER BY p.CreatedAt DESC
 LIMIT @rq_limit OFFSET @rq_offset

4. Fluent Configuration (No Attributes)

If you can't or don't want to annotate your model, configure fields via the fluent builder passed to the RawQueryProcessor<T> constructor:

var processor = new RawQueryProcessor<Product>(SqlDialect.SqlServer, cfg =>
{
    cfg.Property(p => p.Id)
       .HasName("id")
       .HasColumn("p.Id")
       .CanFilter()
       .CanSort();

    cfg.Property(p => p.Name)
       .HasName("name")
       .HasColumn("p.Name")
       .CanFilter()
       .CanSort();

    cfg.Property(p => p.Price)
       .HasName("price")
       .HasColumn("p.Price")
       .CanFilter()
       .CanSort(false); // Sortable is disabled
});

var result = processor.Build(options);

Tip: Fluent configuration and [RawQueryField] attributes can be combined. Attributes are scanned first, then the fluent builder can override or add fields.

Querying the API

Clients use standard URL query parameters. RawQueryKit reads them from the RawQueryOptions object (which ASP.NET Core binds automatically via [FromQuery]).

AND/OR Grouping Logic

Filters follow standard precedence rules: AND binds tighter than OR.

• a,b|c,d is parsed as (a AND b) OR (c AND d)
• Each group becomes a bracketed SQL expression joined by OR

Filter Operators Reference

Note: String-only operators (@=, _=, *-=, ==*, !=*, and their variants) throw an ArgumentException if applied to a non-string property.

API Reference

RawQueryFieldAttribute

Applied to model properties to declare filtering and sorting permissions.

RawQueryOptions

Bind this from [FromQuery] in your controller, or construct it manually in services.

RawQueryResult

Returned by RawQueryProcessor<T>.Build(options).

RawQuery Static Factory

// Returns a cached RawQueryProcessor<T> for the given dialect.
// One processor is cached per type; if your app uses multiple dialects
// for the same model, use DI with RawQueryProcessor<T> directly.
RawQueryProcessor<T> processor = RawQuery.For<T>(SqlDialect dialect = SqlDialect.SqlServer);

Error Handling

Code Contributors

License

This project is licensed under the MIT License.