LowCodeHub.QueryableExtensions 0.0.10

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, AdvancedHybridCache-backed query caching, EF Core auditing, 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);
}

// Cached repository (AdvancedHybridCache) + typed database exceptions
builder.Services.AddCachedQueryableRepositories<AppDbContext>();
builder.Services.AddDbContext<AppDbContext>(o => o.UseSqlServer(connectionString).UseDatabaseExceptions());

// Auditing — audited repository + user-context provider; audit rows written in the background
builder.Services.AddAuditedQueryableRepositories<AppDbContext, HttpUserContextProvider>(
    audit => audit.UseSqlServer(connectionString)); // same DB here; pass another connection to isolate

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
• Caching
• Telemetry
• Auditing
• 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);
}

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"));
}

// one provider feeds the injected UserContext (and auditing); 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.

Caching

AdvancedHybridCache-backed query caching: tag entities, read through the cached terminators, and writes invalidate the matching tags. Requires LowCodeHub.AdvancedHybridCache.

// 1. Register the cache (optionally enable cycle-safe JSON for graphs with back-references)
builder.Services.AddQueryableExtensionsCache().WithCycleSafeSerialization();

// 2. Tag cacheable entities
[CacheableEntity]                       // cache region + tag = entity name
public class Product { /* ... */ }

[CacheableEntity("catalog", "pricing")] // plus extra tags
public class Price { /* ... */ }

// 3. Read through the cache
var products = await dbContext.Products.Where(p => p.IsActive).ToCachedListAsync(ct);
var product  = await dbContext.Products.Where(p => p.Id == id).FirstOrDefaultCachedAsync(ct);
var page     = await dbContext.Products.ToCachedPagedListAsync(1, 20, ct);

// 4. Writes via ICacheRepository<T> invalidate the entity's tags automatically;
//    InvalidateTagsAsync is available for manual invalidation when you mutate elsewhere.

Cached terminators: ToCachedListAsync, FirstOrDefaultCachedAsync, CountCachedAsync, ToCachedPagedListAsync. The cache key is derived from the translated SQL plus its parameter values, so different values cache independently and stale plans are never reused. Entries are tagged by entity, which is what makes targeted invalidation possible.

Cached repository

AddCachedQueryableRepositories<TContext>() registers the plain repositories plus an ICacheRepository<T> whose reads are cached and whose writes invalidate the entity's tags automatically:

builder.Services.AddCachedQueryableRepositories<AppDbContext>();

public sealed class CatalogService(ICacheRepository<Product> products)
{
    public Task<IReadOnlyList<Product>> ActiveAsync(CancellationToken ct)
        => products.ListAsync(new ActiveProductsSpec(), ct);   // cached read
}

Inject ICacheRepository<T> for cached access or IRepository<T> for a guaranteed-fresh read. In multi-instance deployments, tag invalidation propagates through HybridCache's backend (e.g. Redis) so other instances drop stale entries.

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))));

Auditing

An IAuditedRepository<T> whose mutations capture audit entries out of the box (including audited set-based ExecuteUpdate/ExecuteDelete). Capture and enrichment run on the request thread; persistence is deferred to a background writer (best-effort delivery), so auditing adds almost no latency. The audit store is just a DbContext you configure — point it at the same database as your entities, or a separate audit database.

// 1. Register audited repositories + the user-context provider, and configure the audit store.
//    Same database:
builder.Services.AddAuditedQueryableRepositories<AppDbContext, HttpUserContextProvider>(
    audit => audit.UseSqlServer(connectionString));
//    …or a separate audit database (its own connection):
builder.Services.AddAuditedQueryableRepositories<AppDbContext, HttpUserContextProvider>(
    audit => audit.UseNpgsql(auditConnectionString),
    options => { options.QueueCapacity = 50_000; options.BatchSize = 200; });

// 2. Mark entities auditable. Map the audit table into your model when the store IS your AppDbContext.
protected override void OnModelCreating(ModelBuilder model)
{
    model.Entity<Order>().IsAuditable();
    model.Entity<Order>().Property(o => o.InternalNotes).IgnoreFromAudit();  // excluded from Old/NewValues
    model.ConfigureAuditTrail();                                            // dbo.AuditTrail (same-DB case)
}

// 3. Implement the provider (shared with row-level security)
public sealed class HttpUserContextProvider(IHttpContextAccessor http) : IUserContextProvider
{
    public ValueTask<UserContext> GetAsync(CancellationToken ct = default)
        => ValueTask.FromResult(new UserContext { UserId = http.HttpContext?.User.FindFirst("sub")?.Value });
}

// 4. Inject it — mutations are audited
public sealed class OrdersService(IAuditedRepository<Order> orders)
{
    public async Task PlaceAsync(Order order, CancellationToken ct)
    {
        orders.Add(new NewOrderSpec(order));
        await orders.SaveChangesAsync(ct);                 // queues an Insert audit entry
    }

    public Task CancelStaleAsync(CancellationToken ct)
        => orders.UpdateAsync(new StaleOrdersSpec(), new CancelOrderSpec(), ct);  // audited set-based update
}

For a dedicated audit database, provision its schema at startup with await app.Services.EnsureAuditTrailDatabaseCreatedAsync(); (when the audit table lives alongside other tables, create it with a migration instead).

Enrich the audit. Attach context without threading it through every call:

// Ambient scope — reason / correlation id / tags flow to every entry captured inside it
using (AuditScope.Begin(reason: "Quarter-end correction", correlationId: traceId))
{
    AuditScope.Tag("ticket", "OPS-1234");
    await orders.UpdateAsync(filter, update, ct);
}

// Cross-cutting enricher — stamps every entry (register one or more)
public sealed class TenantEnricher(ITenant tenant) : IAuditEnricher
{
    public ValueTask EnrichAsync(AuditEntry entry, CancellationToken ct = default)
    {
        entry.Metadata["tenant"] = tenant.Id;
        return ValueTask.CompletedTask;
    }
}
builder.Services.AddScoped<IAuditEnricher, TenantEnricher>();

// Record a non-EF event through the same enrich + sink pipeline
public sealed class LoginAuditor(IAuditDispatcher audit)
{
    public ValueTask RecordLoginAsync(string userId, CancellationToken ct) =>
        audit.DispatchAsync(new AuditEntry { EntityType = "Login", EntityId = userId, Action = AuditAction.Insert }, ct);
}

The pipeline is fully pluggable: IAuditSink (transport — default is an in-process queue), IAuditStore (persistence — default writes AuditTrailEntity rows via a dedicated context), IAuditEnricher (metadata), and IAuditDispatcher (the request-thread entry point). AuditOptions tunes the queue (QueueCapacity, FullBehavior, BatchSize). For custom audited repositories, inherit AuditedRepositoryBase<T>. Use AddAuditedQueryableRepositories<TContext>(configureAuditStore) (no provider type) when you register IUserContextProvider separately via AddUserContextProvider.

Delivery: entries are queued and persisted by a background writer — under a hard crash a small unwritten tail can be lost. Choose AuditQueueFullBehavior.Wait to back-pressure instead of dropping under sustained load, or replace IAuditSink to write inline/transactionally.

Scope: only mutations made through IAuditedRepository<T> are audited. Plain IRepository<T>/SaveChanges, raw SQL, and writes from other applications are not audited — by design.

Requirements

• .NET 10 or later
• Microsoft.EntityFrameworkCore.Relational 10.0+
• LowCodeHub.AdvancedHybridCache for query caching support

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