LowCodeHub.QueryableExtensions 0.0.3

LowCodeHub.QueryableExtensions

A SaaS-ready EF Core toolkit for ASP.NET Core: filtering, dynamic ordering, offset and keyset pagination, specification pattern with composition, multi-tenancy, soft-delete, full-text search, second-level caching, dynamic filter DSL, OpenTelemetry, generic repository, optimistic concurrency, and transactional execution with retry.

Why This Library?

Installation

dotnet add package LowCodeHub.QueryableExtensions

Quick Start

// Pagination — one round-trip, single snapshot
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 ByTenantSpec(tenantId));
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");

// Cached query
var lookups = await dbContext.LookupItems
    .Where(i => i.IsActive)
    .ToCachedListAsync(queryCache, new CachePolicy(TimeSpan.FromMinutes(10), "lookups"));

Table of Contents

• Pagination
• Filtering
• Dynamic Ordering
• Sparse Fieldsets
• Dynamic Filter DSL
• Full-Text Search
• Specification Pattern
• Generic Repository
• Multi-Tenancy
• Soft-Delete
• Row-Level Security
• Lookup Entities
• Localized Values
• Base Entities
• Transactions & Concurrency
• Caching
• Compiled Queries
• Telemetry
• Auditing
• JSON Functions
• Requirements
• License

Pagination

// Offset — single round-trip via COUNT(*) OVER()
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);

PagedQueryBase exposes PageNumber, PageSize (default 20, max 200), 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

OrderBySafeColumn honors [Sortable]-marked properties; OrderByMappedKey takes an explicit alias map; OrderByColumn is reflective and only safe for trusted callers.

public class User
{
    [Sortable("createdDate")] public DateTime CreatedAt { get; set; }
    [Sortable] public string Email { get; set; }
}
var ordered = dbContext.Users.OrderBySafeColumn(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.

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.

Full-Text Search

Provider-agnostic via IFullTextProvider:

IFullTextProvider provider = new SqlServerFullTextProvider();           // CONTAINS
// or new PostgreSqlFullTextProvider("english");                         // tsvector @@ to_tsquery
// or new MySqlFullTextProvider();                                       // MATCH ... AGAINST

var hits = await dbContext.Articles
    .FullTextSearch(provider, request.Query, a => a.Title, a => a.Body)
    .ToListAsync(ct);

Each provider resolves the underlying EF.Functions.* overload by reflection so this package does not take a hard dependency on every provider.

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 ByTenantSpec(tenantId)).Or(new IsAdminSpec());

// Rich spec with includes / flags
public sealed class OrdersWithItemsSpec(Guid customerId) : 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");
        IsSplitQuery = true;
        AsNoTracking = noTracking;
    }
}
var orders = await dbContext.Orders.WithSpecification(new OrdersWithItemsSpec(id)).ToListAsync(ct);

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

Multi-Tenancy

public class HttpTenantContext(IHttpContextAccessor accessor) : ITenantContext
{
    public Guid? TenantId => Guid.TryParse(accessor.HttpContext?.User.FindFirst("tid")?.Value, out var t) ? t : null;
}

public class Order : TrackedBaseEntity<Guid>, ITenantOwned
{
    public Guid TenantId { get; set; }
    /* ... */
}

protected override void OnModelCreating(ModelBuilder model)
{
    model.AddTenantFilters(_tenantContext);
}

// Stamping on insert
dbContext.Orders.AddForTenant(newOrder, _tenantContext);

// Admin escape hatch (current async flow only)
using (MultiTenancyExtensions.IgnoreTenantScope())
{
    var allTenantsView = await dbContext.Orders.ToListAsync(ct);
}

Soft-Delete

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

protected override void OnModelCreating(ModelBuilder model)
{
    model.ApplySoftDeleteFilter();
}

dbContext.Orders.SoftDelete<Order, Guid>(order, deletedBy: currentUser);

var withDeleted = dbContext.Orders.IncludeDeleted();
var deletedOnly = dbContext.Orders.OnlyDeleted<Order, Guid>();

dbContext.Orders.Restore<Order, Guid>(order);

Row-Level Security

Per-call authorization predicate — different from a global query filter, easy to bypass for admin paths:

public sealed class OrderRowSecurity : IRowSecurityPolicy<ClaimsPrincipal, Order>
{
    public Expression<Func<Order, bool>> Filter(ClaimsPrincipal principal)
    {
        var userId = Guid.Parse(principal.FindFirst("sub")!.Value);
        return o => o.AssigneeId == userId || o.SharedWith.Contains(userId);
    }
}

var visible = dbContext.Orders.WhereAuthorized(_policy, _httpContext.User);

Lookup Entities

The package ships only the entity types — LookupSetEntity, LookupItemEntity, and the [LookupCode] enum-mapping attribute. You own the repository, DI registration, and model configuration.

public enum OrderStatus
{
    [LookupCode("PENDING")]  Pending,
    [LookupCode("APPROVED")] Approved,
    [LookupCode("SHIPPED")]  Shipped
}
string code = OrderStatus.Pending.ToCode();             // "PENDING"
OrderStatus s = "APPROVED".ToEnum<OrderStatus>();

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 & Concurrency

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

// Reload-and-retry on DbUpdateConcurrencyException
await dbContext.RetryOnConcurrencyConflictAsync(async () =>
{
    order.Status = "Approved";
    await dbContext.SaveChangesAsync(ct);
}, maxAttempts: 3, cancellationToken: ct);

Caching

Tag-based second-level cache backed by IMemoryCache or IDistributedCache:

builder.Services.AddMemoryQueryCache();        // or AddDistributedQueryCache() for Redis/SQL
builder.Services.AddSingleton<IDistributedCache, ...>(); // when using distributed

[CacheableEntity("lookups")]
public class LookupItemEntity { /* ... */ }

var policy = new CachePolicy(TimeSpan.FromMinutes(10), "lookups");
var items = await dbContext.LookupItems.Where(i => i.IsActive).ToCachedListAsync(cache, policy, ct);
var first = await dbContext.LookupItems.Where(i => i.Code == "X").FirstOrDefaultCachedAsync(cache, policy, ct);
var n     = await dbContext.LookupItems.CountCachedAsync(cache, policy, ct);

// On write — invalidate by tag (wire from your SaveChanges interceptor or service)
await cache.InvalidateTagsAsync(["lookups"], ct);

Cache keys are SHA256 hashes of the LINQ expression tree, so any change to the query produces a fresh entry.

Compiled Queries

private static readonly Func<AppDbContext, Guid, CancellationToken, Task<Order?>> GetOrder =
    CompiledQueryCache.Single<AppDbContext, Guid, Order?>(
        "Orders.ById",
        () => EF.CompileAsyncQuery((AppDbContext db, Guid id, CancellationToken ct) =>
            db.Orders.FirstOrDefault(o => o.Id == id)));

Telemetry

// OpenTelemetry
builder.Services.AddOpenTelemetry()
    .WithTracing(b => b.AddSource(QueryActivitySource.Name))
    .WithMetrics(b => b.AddMeter(QueryActivitySource.MeterName));

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

Auditing

Auditing is fully opt-in at the call site. There is no interceptor running silently — every operation that should produce AuditTrail rows uses an explicit *AuditedAsync extension. Three operations, three wrappers, one ambient scope to attribute them:

Each wrapper writes its audit rows via parameterized raw SQL — they don't go through the change tracker, don't recurse through any other interceptor, and don't flush other pending change-tracker work.

1. Mark entities as auditable — attribute or Fluent API:

[Auditable]
public class Order : TrackedBaseEntity<Guid>
{
    public string Status { get; set; }
    [AuditIgnore] public string? InternalNotes { get; set; }   // excluded from OldValues/NewValues
}

// Or via Fluent API in OnModelCreating:
modelBuilder.Entity<Customer>().IsAuditable();
modelBuilder.Entity<Customer>().Property(c => c.PasswordHash).IgnoreFromAudit();

2. Map the AuditTrail table in OnModelCreating:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.ConfigureAuditTrail();                          // dbo.AuditTrail (defaults)
    // modelBuilder.ConfigureAuditTrail("AuditLog", schema: "audit");
}

3. Implement IAuditContextProvider — your hook for user id, timestamp, and any extra context. The library never assumes IHttpContextAccessor:

public sealed class UserAuditContextProvider(IHttpContextAccessor http) : IAuditContextProvider
{
    public ValueTask<AuditContext> GetAsync(CancellationToken ct = default)
    {
        var user = http.HttpContext?.User;
        return ValueTask.FromResult(new AuditContext
        {
            UserId = user?.FindFirst("sub")?.Value,
            Metadata = new Dictionary<string, string?>
            {
                ["ip"]            = http.HttpContext?.Connection.RemoteIpAddress?.ToString(),
                ["correlationId"] = http.HttpContext?.TraceIdentifier,
            }
        });
    }
}

// Background-job example — no HttpContext involved:
public sealed class JobAuditContextProvider(string jobName) : IAuditContextProvider
{
    public ValueTask<AuditContext> GetAsync(CancellationToken ct = default)
        => ValueTask.FromResult(new AuditContext { UserId = $"job:{jobName}" });
}

4. Pick how the provider is supplied. Two patterns, freely mixable:

a. Per-unit-of-work via AuditScope — recommended. Wrap each logical operation in a using block:

using (AuditScope.Begin(new UserAuditContextProvider(httpAccessor)))
{
    dbContext.Orders.Add(new Order { ... });
    await dbContext.SaveChangesAuditedAsync(ct);                                // attributed to user

    await dbContext.Invoices
        .Where(i => i.Status == "Pending" && i.DueDate < DateTime.UtcNow)
        .ExecuteUpdateAuditedAsync(dbContext,
            s => s.SetProperty(i => i.Status, "Overdue"), ct);                  // attributed to user
}

using (AuditScope.Begin(new JobAuditContextProvider("nightly-cleanup")))
{
    await dbContext.Sessions
        .Where(s => s.LastSeenAt < DateTime.UtcNow.AddDays(-30))
        .ExecuteDeleteAuditedAsync(dbContext, ct);                              // attributed to the job
}

Scopes are async-flow-scoped and nest cleanly.

b. Default fallback via DI — for apps with a single dominant context (e.g., always HTTP):

builder.Services.AddEfCoreAuditing<UserAuditContextProvider>();
// or with a factory delegate:
// builder.Services.AddEfCoreAuditing(sp => new UserAuditContextProvider(sp.GetRequiredService<IHttpContextAccessor>()));

Resolution order on every audited call: active AuditScope → DI-registered IAuditContextProvider → empty default (no UserId, current UTC). If you only ever use AuditScope, DI registration is unnecessary.

Bulk operations

EF Core's ExecuteUpdateAsync / ExecuteDeleteAsync bypass the change tracker, so use these explicit wrappers when you want them logged:

await dbContext.Invoices
    .Where(i => i.Status == "Pending" && i.DueDate < DateTime.UtcNow)
    .ExecuteUpdateAuditedAsync(dbContext,
        s => s.SetProperty(i => i.Status, "Overdue"), ct);

await dbContext.Sessions
    .Where(s => s.LastSeenAt < DateTime.UtcNow.AddDays(-30))
    .ExecuteDeleteAuditedAsync(dbContext, ct);

Both fall through to the underlying EF Core call with no extra work when the entity isn't auditable. For auditable entities, expect:

• One extra SELECT to snapshot rows before the modification.
• For updates, an additional WHERE … IN (…) re-fetch by primary key to capture new values. Composite-key entities fall back to FindAsync per row (N+1) — fine for small batches, avoid for large ones.

For atomicity, wrap a sequence of audited operations in BeginTransactionAsync so the snapshot, modification, and audit insert commit together.

Limitations: these wrappers see only what passes through them. Plain SaveChangesAsync, raw SQL, replication, and writes from other applications sharing the database are not audited — by design.

JSON Functions

protected override void OnModelCreating(ModelBuilder model) => model.AddJsonFunctions();

var europe = await dbContext.Orders
    .Where(o => JsonExtensions.JsonValue(o.MetadataJson, "$.region") == "eu")
    .ToListAsync(ct);

Requirements

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

Provider-specific features (full-text on SQL Server / Postgres / MySQL) require the matching EF Core provider package on the host application.

License

MIT © Ahmed Abuelnour