LowCodeHub.QueryableExtensions 0.0.12

LowCodeHub.QueryableExtensions

A comprehensive EF Core toolkit for ASP.NET Core: filtering, dynamic ordering, offset and keyset pagination, specification pattern with composition, soft-delete, dynamic filter DSL, row-level security, a generic repository, typed database-exception translation, localized value objects, and transactional execution.

Why This Library?

Installation

dotnet add package LowCodeHub.QueryableExtensions

Namespaces

Types are grouped by kind (folder == namespace) under the LowCodeHub.QueryableExtensions.* root. Import the namespace(s) for what you use:

Quick Start

// Pagination — items + total count in one round-trip
var paged = await dbContext.Users
    .Where(u => u.IsActive)
    .OrderBy(u => u.Name)
    .ToPagedListAsync(page: 1, pageSize: 20, ct);

// Keyset pagination from an HTTP cursor
var page = await dbContext.Orders
    .ToKeysetPageAsync(o => o.Id, request.Cursor, pageSize: 50, ct: ct);
return new { page.Items, page.NextCursor };

// Composable predicate spec
var spec = new ActiveUsersSpec().And(new RecentlyActiveSpec(since));
var users = await dbContext.Users.WithSpecification(spec).ToListAsync(ct);

// Dynamic filter from a query string
var filtered = dbContext.Orders.ApplyDynamicFilter("status eq 'Open' and total gt 100");

// Generic repository (opt-in)
builder.Services.AddQueryableRepositories<AppDbContext>();

public sealed class ProductService(IRepository<Product> repository)
{
    public Task<IReadOnlyList<Product>> GetActiveAsync(CancellationToken ct)
        => repository.ListAsync(new ActiveProductsSpec(), ct);

    // …or read a projection / a scalar through the same repository
    public Task<IReadOnlyList<ProductSummary>> GetSummariesAsync(CancellationToken ct)
        => repository.ListAsync(new ProductSummarySpec(), ct);

    public Task<decimal> GetTotalStockValueAsync(CancellationToken ct)
        => repository.ScalarAsync(new StockValueSpec(), ct);
}

// Typed database exceptions
builder.Services.AddDbContext<AppDbContext>(o => o.UseSqlServer(connectionString).UseDatabaseExceptions());

Table of Contents

• Pagination
• Filtering
• Dynamic Ordering
• Sparse Fieldsets
• Dynamic Filter DSL
• Specification Pattern
• Generic Repository
• Soft-Delete
• Row-Level Security
• Localized Values
• Base Entities
• Transactions
• Database Exception Translation
• Telemetry
• Requirements
• License

Pagination

// Offset — items + total count in a single round-trip
var paged = await dbContext.Users.OrderBy(u => u.Id).ToPagedListAsync(1, 20, ct);

// Keyset — by key
var page = await dbContext.Orders.ToKeysetPageAsync(o => o.Id, lastSeenId, 50, ct: ct);

// Keyset — by HTTP cursor (base64)
var page = await dbContext.Orders.ToKeysetPageAsync(o => o.Id, request.Cursor, 50, ct: ct);
return new { page.Items, page.NextCursor };

// Stream all rows (exports / migrations)
await foreach (var order in dbContext.Orders.ToKeysetStreamAsync(o => o.Id, 1000, ct: ct))
    await WriteAsync(order);

// First page with a struct key (int/Guid/DateTime) — use the overload without afterKey
var first = await dbContext.Orders.ToKeysetPageAsync(o => o.Id, pageSize: 50, ct: ct);

Rules and behavior:

• Always order offset-paginated queries — SQL OFFSET/FETCH without ORDER BY returns rows nondeterministically, so pages can repeat or skip rows.
• The keyset key must be a unique single column (e.g. Id). A non-unique key such as CreatedAt silently skips rows whose key equals the page boundary. Composite keys are not supported. string keys are supported (translated via CompareTo).
• All paging/filter values (including the keyset boundary) are sent as SQL parameters, so query plans are reused across values.
• PagedQueryBase exposes PageNumber (clamped to ≥ 1), PageSize (default 20, max DefaultMaxPageSize = 200 — override the protected virtual MaxPageSize property to change the cap), Filter, SortColumn, SortOrder, Language.

Filtering

// LIKE — escape-aware, nested members supported
var users = await dbContext.Users
    .MultiColumnSearchLike(term, u => u.Name, u => u.Email, u => u.Address.City)
    .ToListAsync(ct);

// Group by + count in one round-trip
var byStatus = await dbContext.Orders.GroupByWithCountAsync(o => o.Status, ct);

Dynamic Ordering

OrderByColumn honors [Sortable]-marked properties (aliased properties answer only to their alias); OrderByMappedKey takes an explicit alias map. Both return the source unchanged for anything not whitelisted, so they are safe to call with an untrusted, client-supplied column name. Only properties are resolvable, not fields.

public class User
{
    [Sortable("createdDate")] public DateTime CreatedAt { get; set; }
    [Sortable] public string Email { get; set; }
}
var ordered = dbContext.Users.OrderByColumn(request.SortColumn, descending: true);

Sparse Fieldsets

public class User
{
    [Projectable] public Guid Id { get; set; }
    [Projectable] public string Name { get; set; }
    [Projectable("emailAddress")] public string Email { get; set; }
    public string PasswordHash { get; set; } // not projectable, never returned
}

var rows = await dbContext.Users
    .SelectFields("name,emailAddress")
    .ToListAsync(ct);
// rows: List<Dictionary<string, object?>>

Unknown fields are silently dropped; non-[Projectable] properties are unreachable from this API. Passing null/empty selects every [Projectable] property. Result keys always use the canonical alias/property casing (not the caller's casing), and aliased properties answer only to their alias.

Dynamic Filter DSL

OData-lite, whitelisted via [Filterable]. Operators: eq, ne, gt, lt, ge, le, contains, startswith, endswith, in. Logical: and, or, parentheses. Literals: numbers, ISO dates, 'strings', true/false, null, (a, b, c) for in.

public class Order
{
    [Filterable] public string Status { get; set; }
    [Filterable("created")] public DateTime CreatedAt { get; set; }
    [Filterable] public decimal Total { get; set; }
}

var q = dbContext.Orders.ApplyDynamicFilter(
    "status in ('Open','Hold') and created gt 2026-01-01 and total ge 100");

Unknown aliases throw InvalidFilterException — there is no silent fallback. All malformed input (bad literals, type mismatches, null against non-nullable columns, unterminated strings) also surfaces as InvalidFilterException, so map that one exception type to 400.

Safety and semantics:

• Aliased properties answer only to their alias — [Filterable("created")] CreatedAt is addressable as created, not CreatedAt. (Same rule for [Sortable]/[Projectable].)
• Hard input limits (exceeding any throws InvalidFilterException): 2,048 chars, 256 tokens, 32 nesting levels, 100 items per in list.
• All literal values are sent as SQL parameters — stable query plans across values.
• ne follows SQL three-valued logic: rows where the column is NULL are excluded.
• Relational operators (gt/lt/ge/le) require a comparable column type (numbers, dates, DateTimeOffset, enums). Applying them to a string, bool, or Guid throws InvalidFilterException — use eq/ne or contains/startswith/endswith for those.

Specification Pattern

// Plain interface
public sealed class ActiveUsersSpec : ISpecification<User>
{
    public IQueryable<User> Where(IQueryable<User> q) => q.Where(u => u.IsActive);
}

// Composable predicate spec
public sealed class ActiveUsersSpec : Criterion<User>
{
    public override Expression<Func<User, bool>> Criteria => u => u.IsActive;
}
var spec = new ActiveUsersSpec().And(new InRegionSpec(region)).Or(new IsAdminSpec());

// Rich spec with includes / ordering / flags
public sealed class OrdersWithItemsSpec : Specification<Order>
{
    public OrdersWithItemsSpec(Guid customerId) : this(customerId, true) { }
    public OrdersWithItemsSpec(Guid customerId, bool noTracking)
    {
        Criteria = o => o.CustomerId == customerId;
        AddInclude(o => o.Items);
        AddInclude("Items.Product");
        ApplyOrderBy(o => o.CreatedAt);   // applied by WithSpecification — order specs for paging!
        IsSplitQuery = true;
        AsNoTracking = noTracking;
    }
}
var orders = await dbContext.Orders.WithSpecification(new OrdersWithItemsSpec(id)).ToListAsync(ct);

ApplyOrderBy/ApplyOrderByDescending are applied by WithSpecification (ascending first; when both are set the descending key becomes a ThenByDescending). Set-based operations (UpdateAsync/RemoveAsync/DeleteWithSpecification*) apply only the spec's predicate and ignore-query-filters flag — EF Core rejects includes/ordering/tracking in ExecuteUpdate/ExecuteDelete pipelines.

Projection, update, add, and scalar specs all work as before:

public sealed class UserSummarySpec : ISpecification<User, UserSummaryDto> { /* Select */ }
public sealed class DeactivateUserSpec : IUpdateSpecification<User> { /* Update */ }
public sealed class UserCountSpec : IScalarSpecificationAsync<User, int> { /* ExecuteAsync */ }

Generic Repository

Opt-in. Some teams prefer hand-rolled repos — this is shipped as a convenience, not the default abstraction.

builder.Services.AddQueryableRepositories<AppDbContext>();

public class OrdersService(IRepository<Order> orders)
{
    public Task<PagedList<Order>> SearchAsync(int page, int pageSize, CancellationToken ct)
        => orders.PagedAsync(new ActiveOrdersSpec(), page, pageSize, ct);

    public Task<int> CancelInactiveAsync(CancellationToken ct)
        => orders.UpdateAsync(new InactiveOrdersSpec(), new CancelOrderSpec(), ct);

    public Task<int> PurgeDraftsAsync(CancellationToken ct)
        => orders.RemoveAsync(new DraftOrdersSpec(), ct);
}

Reading projections and scalars

GetAsync / ListAsync / PagedAsync each take a projection specification (ISpecification<TEntity, TResult>) alongside the entity-returning overload, and ScalarAsync reduces the set through an IScalarSpecificationAsync<TEntity, TResult>. Projections never materialize entities, so they skip change tracking and only pull the columns you select:

public sealed record OrderSummary(Guid Id, string Customer, decimal Total);

// The projection spec owns the whole shape — filter, order, and Select.
public sealed class OpenOrderSummariesSpec : ISpecification<Order, OrderSummary>
{
    public IQueryable<OrderSummary> Select(IQueryable<Order> query)
        => query.Where(o => o.Status == OrderStatus.Open)
                .OrderByDescending(o => o.CreatedAt)
                .Select(o => new OrderSummary(o.Id, o.Customer.Name, o.Total));
}

public sealed class OpenOrderRevenueSpec : IScalarSpecificationAsync<Order, decimal>
{
    public Task<decimal> ExecuteAsync(IQueryable<Order> query, CancellationToken ct = default)
        => query.Where(o => o.Status == OrderStatus.Open).SumAsync(o => o.Total, ct);
}

public class OrdersReadService(IRepository<Order> orders)
{
    public Task<OrderSummary?> LatestAsync(CancellationToken ct)
        => orders.GetAsync(new OpenOrderSummariesSpec(), ct);

    public Task<IReadOnlyList<OrderSummary>> ListAsync(CancellationToken ct)
        => orders.ListAsync(new OpenOrderSummariesSpec(), ct);

    public Task<PagedList<OrderSummary>> PageAsync(int page, int size, CancellationToken ct)
        => orders.PagedAsync(new OpenOrderSummariesSpec(), page, size, ct);

    public Task<decimal> RevenueAsync(CancellationToken ct)
        => orders.ScalarAsync(new OpenOrderRevenueSpec(), ct);
}

Because a projection spec receives the raw set, it applies its own Where/OrderBy — rich Specification<T> features (includes, tracking flags) don't apply and aren't needed. Order the projection inside its Select pipeline before paging it, for the same reason entity specs must declare an ordering.

The concrete default repository is internal. Consumers should depend on IRepository<T>.

The generic IRepository<T> services bind to one DbContext per service provider — calling AddQueryableRepositories again with a different context type throws (previously the second registration silently rebound every repository to the wrong context). Use dedicated RepositoryBase<T>-derived repositories for additional contexts.

RemoveAsync is a physical ExecuteDelete — see the soft-delete section before using it on soft-deletable entities.

Custom repositories can inherit the public base class when they need the default behavior plus domain-specific methods:

public interface IOrdersRepository : IRepository<Order>
{
    Task<IReadOnlyList<Order>> ListReadyToShipAsync(CancellationToken ct);
}

public sealed class OrdersRepository(AppDbContext dbContext)
    : RepositoryBase<Order>(dbContext), IOrdersRepository
{
    public Task<IReadOnlyList<Order>> ListReadyToShipAsync(CancellationToken ct)
        => ListAsync(new ReadyToShipOrdersSpec(), ct);
}

builder.Services.AddScoped<IOrdersRepository, OrdersRepository>();

Soft-Delete

SoftDeletableEntity<TKey> adds IsDeleted / DeletedAt / DeletedBy to your entity:

public class Order : SoftDeletableEntity<Guid> { /* ... */ }

// Hide soft-deleted rows with a global query filter (use IgnoreQueryFilters() to include them):
protected override void OnModelCreating(ModelBuilder model) =>
    model.Entity<Order>().HasQueryFilter(o => !o.IsDeleted);

Soft-delete a row by setting IsDeleted through an update specification (so only that column is written and concurrent changes to other columns aren't clobbered):

await repository.UpdateAsync(new OrderByIdSpec(id), new MarkDeletedSpec(currentUser), ct);

Heads-up: IRepository<T>.RemoveAsync and DeleteWithSpecification* are physical ExecuteDelete operations — they permanently remove rows even for soft-deletable entities. Use an update specification setting IsDeleted for soft-delete semantics.

Row-Level Security

RowSecurityPolicySpecification<TEntity> is an ISpecification<TEntity>: it applies a Where over the query with its UserContext injected by DI. Compose it with WithSpecification — different from a global query filter and easy to skip on admin/system paths.

public sealed class OrderRowSecurity(UserContext context) : RowSecurityPolicySpecification<Order>(context)
{
    public override IQueryable<Order> Where(IQueryable<Order> query) =>
        query.Where(o => o.OwnerId == UserContext.UserId || UserContext.Roles.Contains("admin"));
}

// the provider feeds the injected UserContext; register it + your policy:
builder.Services.AddUserContextProvider<HttpUserContextProvider>();
builder.Services.AddScoped<OrderRowSecurity>();

var visible = dbContext.Orders.WithSpecification(policy);

Localized Values

Immutable, structurally-equatable, language-keyed text:

var name = new Localized(new Dictionary<string, string> { ["en"] = "Active", ["ar"] = "نشط" });
name.Get("ar");       // "نشط"
name.Get("en-US");    // falls back to "en"
name.Get("fr");       // falls back to first non-empty value

Base Entities

public class Order : BaseEntity<Guid> { }
public class Order : TrackedBaseEntity<Guid> { }
public class Order : SoftDeletableEntity<Guid> { }
public class Order : ConcurrentEntity<Guid> { }   // adds [Timestamp] RowVersion

Tracking fields are plain properties — populate them via your own SaveChanges interceptor or service code.

Transactions

// Transaction with retry strategy
await transactionManager.ExecuteInTransactionWithRetryAsync(async scope =>
{ /* changes */ }, IsolationLevel.ReadCommitted, ct);

Notes:

• ExecuteInTransactionWithRetryAsync clears the change tracker at the start of every attempt so a transient-failure retry can't re-save the previous attempt's entities. Do all loads/adds inside the delegate; entities tracked before the call are evicted.
• The non-retry ExecuteInTransactionAsync throws an actionable error when the provider is configured with a retrying execution strategy (e.g. EnableRetryOnFailure) — use the WithRetry variant there.

Database Exception Translation

Opt in per DbContext; provider DbExceptions surfaced by SaveChanges/queries are translated into typed, provider-agnostic exceptions you can map to HTTP responses:

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
        .UseDatabaseExceptions());          // also works on UseNpgsql / UseSqlite / UseMySql / UseOracle

try
{
    await dbContext.SaveChangesAsync(ct);
}
catch (UniqueConstraintViolationException)
{
    return Results.Conflict("That value already exists.");
}

Every typed exception derives from DatabaseErrorException (itself a DbUpdateException), exposes the normalized ErrorKind, and keeps the original provider exception as InnerException. UniqueConstraintViolationException / ReferenceConstraintViolationException further derive from ConstraintViolationException, so you can catch broadly or precisely. Classification is provider-specific (SQL Server, PostgreSQL, SQLite, MySQL, Oracle); anything unrecognized passes through unchanged. Translation adds no provider package references — it inspects the DbException your provider already throws.

Telemetry

SlowQueryInterceptor logs every command whose execution exceeds a threshold:

builder.Services.AddDbContext<AppDbContext>((sp, opt) =>
    opt.UseSqlServer(connectionString)
       .AddInterceptors(new SlowQueryInterceptor(
           sp.GetRequiredService<ILogger<SlowQueryInterceptor>>(),
           TimeSpan.FromMilliseconds(500))));

Requirements

• .NET 10 or later
• Microsoft.EntityFrameworkCore.Relational 10.0+

Database exception translation does not add provider package references — it inspects the DbException your provider already throws. You still need the matching EF Core provider package (SQL Server, PostgreSQL, SQLite, MySQL, Oracle) on the host application.

License

MIT © Ahmed Abuelnour