FlexQuery.NET.EFCore 2.1.0

FlexQuery.NET

Dynamic filtering, sorting, pagination and projection for IQueryable in .NET

---

FlexQuery.NET is a lightweight and powerful .NET library that enables dynamic filtering, sorting, paging, and projection over IQueryable (EF Core or any LINQ provider).

It converts query parameters into EF Core-translatable expression trees, making it ideal for building flexible APIs without hardcoding queries.

Installation

dotnet add package FlexQuery.NET

Optional (async helpers for EF Core):

dotnet add package FlexQuery.NET.EFCore

Quick Start

FlexQuery.NET supports two distinct usage patterns. The Simple Usage is perfect for rapid prototyping, while the Advanced Usage using the QueryRequest DTO is highly recommended for production APIs requiring clean OpenAPI/Swagger bindings and strong separation between client input and server-side security.

1. Simple Usage (Direct Parsing)

Directly parse the raw Request.Query.

using FlexQuery.NET;
using FlexQuery.NET.Models;
using Microsoft.AspNetCore.Mvc;

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

    public CustomersController(AppDbContext context) => _context = context;

    [HttpGet]
    public async Task<IActionResult> Get([FromQuery] FlexQueryParameters parameters)
    {
        // One-stop shop: Parsing + Validation + Execution
        var result = await _context.Users.FlexQueryAsync(parameters, options => 
        {
            options.AllowedFields = ["Id", "Name", "Email"];
        });

        return Ok(result);
    }
}

2. Fluent Filter Builder

Build filters with a chainable API instead of string-based syntax. This feature is tightly coupled to the v2 API and integrates directly with the FlexQuery pipeline.

When to Use

• Building dynamic filters based on user input or runtime conditions
• When you need compile-time safety with strongly-typed builders (FilterBuilder<T>)
• Creating nested filter groups with AND/OR logic
• When you prefer a code-first approach over writing raw DSL strings

When NOT to Use

• For simple static filters that can be hardcoded as DSL strings
• When performance is critical and you want to avoid the slight overhead of building the filter object tree (though negligible)
• If you are consuming filters from external sources (e.g., stored as JSON) and only need to apply them

Recommendation (v2 approach)

Use the Fluent Filter Builder to construct your filter, serialize it to JSON, and assign it to FlexQueryParameters.Filter. Then execute the query using the unified FlexQuery pipeline.

Example: Basic filtering

using FlexQuery.NET.Builders;
using FlexQuery.NET.Models;
using System.Text.Json;

// Build filter using fluent API
var filterBuilder = new FilterBuilder()
    .Field("Age").GreaterThanOrEqual(18)
    .And.Field("Country").Eq("USA");

// Serialize to JSON
var filterJson = JsonSerializer.Serialize(filterBuilder.Build());

// Create query parameters
var parameters = new FlexQueryParameters
{
    Filter = filterJson,
    Sort = "Name:asc",
    Page = 1,
    PageSize = 20
};

// Execute query
var result = myQueryable.FlexQuery(parameters);

Example: Strongly-typed builder

using FlexQuery.NET.Builders;
using FlexQuery.NET.Models;
using System.Text.Json;

var filterBuilder = new FilterBuilder<Product>()
    .Field(p => p.Price).Between(10, 100)
    .Or.Field(p => p.Name).Contains("Widget");

var filterJson = JsonSerializer.Serialize(filterBuilder.Build());

var parameters = new FlexQueryParameters { Filter = filterJson };
var result = products.FlexQuery(parameters);

Example: Complex nested groups

using FlexQuery.NET.Builders;
using FlexQuery.NET.Models;
using System.Text.Json;

var filterBuilder = new FilterBuilder()
    .AndGroup(gb =>
    {
        gb.Field("OrderDate").GreaterThanOrEqual(DateTime.UtcNow.AddMonths(-1));
        gb.Field("Status").Eq("Shipped");
    })
    .OrGroup(gb =>
    {
        gb.Field("TotalAmount").GreaterThan(1000);
        gb.Field("IsPriority").Eq(true);
    });

var filterJson = JsonSerializer.Serialize(filterBuilder.Build());

var parameters = new FlexQueryParameters { Filter = filterJson };
var result = orders.FlexQuery(parameters);

Note: The Fluent Filter Builder is fully supported in v2 and integrates seamlessly with the FlexQuery pipeline. There are no plans to deprecate this feature.

3. Advanced Usage (Recommended)

💡 Tip: Use the FlexQuery extension for a "one-stop-shop" that handles parsing, validation, and execution with built-in security.

using FlexQuery.NET;
using FlexQuery.NET.Models;
using Microsoft.AspNetCore.Mvc;

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

    public CustomersController(AppDbContext context) => _context = context;

    /// <summary>
    /// Search customers with dynamic filters and security.
    /// </summary>
    /// <param name="parameters">Descriptive DTO for Swagger/OpenAPI documentation.</param>
    [HttpGet]
    public async Task<IActionResult> Get([FromQuery] FlexQueryParameters parameters)
    {
        // One-line secure execution with a configuration delegate
        var result = await _context.Users.FlexQueryAsync(parameters, options => 
        {
            options.MaxFieldDepth = 3;
            options.StrictFieldValidation = true;
            options.AllowedFields = new[] { "Id", "Name", "Email", "Orders.*" };
        });

        return Ok(result);
    }
}

High-Level API: FlexQuery

FlexQuery (and FlexQueryAsync for EF Core) is the recommended entry point for production APIs.

It encapsulates the full query pipeline into three internal steps:

• Parsing: Converts FlexQueryParameters into a QueryOptions model.
• Validation: Validates the query against QueryExecutionOptions (e.g., allowed fields, depth limits).
• Execution: Applies filtering, sorting, paging, and projection to the IQueryable.

---

Why use FlexQuery?

While lower-level APIs like QueryOptionsParser.Parse and ApplyQueryOptions are available, FlexQuery is recommended for most use cases.

✔ Built-in Validation

Enforces a validate-before-execute pattern. Validation is always applied automatically, reducing the risk of unsafe queries reaching the database.

---

✔ Separation of Concerns

• FlexQueryParameters → untrusted client input
• QueryExecutionOptions → trusted server-side rules

This ensures that clients cannot override security constraints.

---

✔ Cleaner Controllers

Replaces multiple steps (parsing, validation, execution) with a single, expressive call:

// Manual Approach (Advanced)
var options = QueryOptionsParser.Parse(Request.Query);
var exec = new QueryExecutionOptions { AllowedFields = ["Id", "Name", "Status"] };

try
{
    // 1. Validate
    options.ValidateOrThrow<User>(exec);
    
    // 2. Apply and Execute
    var users = await query.ApplyQueryOptions(options).ToListAsync();
}
catch (QueryValidationException ex)
{
    return BadRequest(ex.Result.Errors);
}

// After (recommended)
var result = await _context.Users
    .FlexQueryAsync(parameters, options => 
    {
        options.MaxFieldDepth = 3;
        options.StrictFieldValidation = true;
        options.AllowedFields = ["Id", "Name", "Email", "Orders.*"];
    });

---

✔ Optimized Performance

Automatically handles "Filtered Includes" and dynamic projections in a single optimized Select() statement, reducing database round-trips.

---

✔ Canonical Query Normalization

Equivalent filter expressions are normalized into a deterministic AST and stable cache key before expression caching, so queries like name = John AND age > 18 and age > 18 AND name = John hit the same cached expression.

---

✔ Consistent Results

Consistency: Ensures a consistent response shape, including Data, Page, PageSize, and an optional TotalCount based on the IncludeCount setting.

---

✔ Supports Modern DTOs

Primarily designed for FlexQueryParameters, but remains backward compatible with legacy QueryRequest and FlexQueryRequest models.

---

Configuration Options

Use the configuration delegate to set server-side rules that clients cannot override:

query.FlexQuery(parameters, options => 
{
    options.MaxPageSize = 100;
    options.DefaultPageSize = 25;
    options.StrictFieldValidation = true;
    options.AllowedFields = new[] { "Id", "Name", "Status" };
    options.BlockedFields = new[] { "PasswordHash" };
});

Features

• Filtering: nested AND/OR groups, nested property paths, scoped collection filtering (.any(), .all(), [...])
• Sorting: multi-field ordering
• Paging: page / pageSize or skip / take (format-dependent)
• Projection: select with nested properties, plus include-style expansion
• Query formats: DSL (primary), JSON (advanced), Indexed (compatibility), JQL fallback
• EF Core friendly: expression-tree based, provider-translatable; generates clean LIKE/= SQL without function wrappers
• Collation-aware string matching: relies on database collation (SQL Server CI_AS) instead of .ToLower(), preserving index usage
• Pluggable operators: core ships framework-agnostic handlers, optional packages can override by operator
• Dual-Pipeline: Decouples data filtering (WHERE) from data shaping (Filtered Includes - EF Core only)
• Validation Engine: Pre-execution query validation (field existence, operator validity, type safety)
• Field-Level Security: Built-in whitelisting and blacklisting of fields (supports nested paths)
• Expression Caching: Optional thread-safe caching of generated expression trees for high-frequency queries

📦 Core vs. EF Core Package

Feature	FlexQuery.NET (Core)	FlexQuery.NET.EFCore
Dynamic Filtering (Where)	✅	✅
Dynamic Sorting (OrderBy)	✅	✅
Dynamic Paging (Skip/Take)	✅	✅
Dynamic Projection (Select)	✅	✅
Async Helpers (ToListAsync, etc)	❌	✅
Filtered Includes (Include)	❌	✅

🚀 Filtered Includes (EF Core Only)

The Include Pipeline is a specialized feature for EF Core that allows you to eager-load related entities with inline filtering. It translates include= parameters into optimized .Include() and .ThenInclude() calls.

using FlexQuery.NET.EFCore;

// Apply the Include pipeline before materialization
var users = await _context.Users
    .ApplyQueryOptions(options)      // WHERE pipeline
    .ApplyFilteredIncludes(options)  // INCLUDE pipeline (EF Core Only)
    .ToListAsync();

Example Query: ?include=Orders[status:eq:Cancelled] SQL Result:

SELECT ... FROM Users
LEFT JOIN (
    SELECT ... FROM Orders WHERE Status = 'Cancelled'
) AS o ON Users.Id = o.UserId

🔄 Migration from DynamicQueryable.Extensions

FlexQuery.NET is the successor of DynamicQueryable.Extensions.

Key Improvements

• Rewritten architecture for better performance and extensibility
• Cleaner and more consistent API surface
• Enhanced EF Core integration
• Support for multiple query formats (DSL, JSON, Indexed, JQL)
• Public filter model: QueryOptions.Filter now uses a dedicated FilterGroup model while internal execution still uses FilterGroupNode
• Backward compatibility helpers: SortOption remains supported as a compatibility alias for SortNode

⚠️ Old versions and changelog history are not carried over to maintain a clean versioning strategy.

🔽 Sorting

• Basic: ?sort=createdAt:desc
• Multi-field: ?sort=createdAt:desc,total:asc
• Nested: ?sort=customer.name:asc
• Aggregate: ?sort=orders.sum(total):desc,orders.count():asc

Filtering & Query Formats

FlexQuery.NET parses incoming query parameters into a unified public model (QueryOptions, FilterGroup, FilterCondition). Parser output now produces FilterGroup trees for JSON, DSL, and JQL formats, while the runtime execution engine continues to traverse internal FilterGroupNode structures for efficiency.

Note: QueryOptions is intended for user-provided query payloads. Server-side security and execution policy should be configured through the separate QueryExecutionOptions model.

Generic (indexed)

Simple filter

?filter[0].field=Name
&filter[0].operator=contains
&filter[0].value=john

Top-level logic (AND/OR)

?logic=or
&filter[0].field=City&filter[0].operator=eq&filter[0].value=Berlin
&filter[1].field=City&filter[1].operator=eq&filter[1].value=Paris

JSON (nested groups)

Advanced nested logic

?filter={
  "logic":"and",
  "filters":[
    {
      "logic":"or",
      "filters":[
        {"field":"City","operator":"eq","value":"London"},
        {"field":"City","operator":"eq","value":"Berlin"}
      ]
    },
    {"field":"Age","operator":"between","value":"25,40"}
  ]
}

DSL (compact filter string)

Use & for AND (URL-encode as %26), | for OR, parentheses for grouping:

?filter=((city:eq:London|city:eq:Berlin)%26(age:between:25,40|status:eq:Pending))

DSL advanced operators:

?filter=!name:eq:john
?filter=not(name:eq:john)
?filter=name:like:%john%
?filter=orders:any:total:gt:100
?filter=orders:count:gt:5

Grouping, aggregates, and having

Use group, aggregate functions in select, and having for post-aggregation filtering:

?group=category,status
&select=category,sum(total),count(id)
&having=sum(total):gt:10000

Supported aggregate functions in select/having:

• sum(field)
• count(field)
• avg(field)

JQL-lite fallback (query)

Use SQL-like operators with AND / OR and parentheses for grouping:

?query=(name = "john" OR name = "doe") AND age >= 20

Supports nested property paths and quoted values:

?query=email = "ops@acmeretail.com" AND orders.number = "ORD-2026-0002" AND orders.items.quantity > 2

Scoped collection filtering (conditions apply to the same element):

?query=orders.any(status = Cancelled AND total > 500)
?query=orders[status = Cancelled AND orderItems.any(id = 101)]

Supported JQL operators:

• = != > >= < <=
• CONTAINS
• IN (...) and NOT IN (...)
• IS NULL and IS NOT NULL
• BETWEEN ... AND ...
• LIKE, STARTSWITH, ENDSWITH
• Collection predicates: ANY, ALL, COUNT

Unlike DSL/JSON malformed-input handling, invalid JQL syntax is surfaced as a parse exception to callers.

Operators

Operator	Description	Example
eq	Equal	Name eq 'John'
neq	Not equal	Age neq 30
gt	Greater than	Age gt 18
gte	Greater than or equal	Age gte 18
lt	Less than	Age lt 60
lte	Less than or equal	Age lte 60
contains	String contains	Name contains 'jo'
startswith	String starts with	Name startswith 'Jo'
endswith	String ends with	Name endswith 'hn'
in	Value exists in a list	Status in ['Active','Pending']
notin	Value does not exist in list	Status notin ['Inactive']
between	Inclusive range	Age between 18,60
isnull	Is null	DeletedAt isnull
notnull	Is not null	DeletedAt notnull
like	SQL LIKE pattern	Name like %john%
any	Collection element predicate	Orders any Total gt 100
count	Collection count compare	Orders count gt 5
! / not()	Negates condition/group	!Name eq John
:	DSL operator separator	Status:eq:Cancelled (inside include)

Nested & Collections

Nested property paths

Dot-notation works across filtering and projection:

?filter[0].field=Profile.Bio&filter[0].operator=contains&filter[0].value=dev
&select=Id as customerId,Profile.Bio as biography

Aliases (as)

You can rename properties in the output dynamic object using the as keyword. This works at any level of nesting:

?select=id as customerId, name, orders.status as orderStatus, orders.orderItems.productName as product

In the resulting object:

• Id becomes customerId
• Orders.Status becomes orderStatus inside each order
• Orders.OrderItems.ProductName becomes product inside each order item

Collection paths (parent filtering)

Filtering on a collection navigation (e.g. Orders.Number) uses Any(...) / EXISTS semantics for the parent:

?filter[0].field=Orders.Number
&filter[0].operator=eq
&filter[0].value=SO-001

Conceptually:

x => x.Orders.Any(o => o.Number == "SO-001")

Scoped Collection Filtering (JQL)

By default, independent conditions on a collection are interpreted as separate Any() checks. Scoped filtering ensures multiple conditions apply to the same element.

Syntax	Description
orders.any(...)	Conditions apply to the same order
orders.all(...)	All orders must satisfy the inner filter
orders[...]	Shorthand for orders.any(...)

Conceptually:

?query=orders.any(status = Cancelled AND total > 500)

Translates to:

x => x.Orders.Any(o => o.Status == "Cancelled" && o.Total > 500)

Nested Scoped Filtering:

?query=orders.any(status = Cancelled AND orderItems.any(id = 101))

This ensures the orderItems condition is checked against items belonging to a Cancelled order. Scoped filters can be nested recursively to any depth.

Filtered child collections (when selected)

When a request filters on a collection path and that collection is also projected, the returned child collection is filtered to match the same criteria (projection-based, EF Core translatable):

?filter[0].field=Orders.Number&filter[0].operator=eq&filter[0].value=SO-001
&select=Id,Orders.Number

Conceptually:

x => new {
  x.Id,
  Orders = x.Orders
    .Where(o => o.Number == "SO-001")
    .Select(o => new { o.Number })
    .ToList()
}

Dual-Pipeline Query System (EF Core)

FlexQuery.NET implements a dual-pipeline architecture to solve the "over-filtering" problem. It allows you to filter which root entities are returned (WHERE) independently from how their related collections are shaped (Filtered Includes).

Pipeline 1: Root Filtering (WHERE)

Filters which root entities appear in the results.

?query=orders.any(status = 'Cancelled' AND total > 500)

Pipeline 2: Data Shaping (Filtered Includes)

Filters the content of the included child collections without affecting the root entity count.

?include=orders(total > 100).items(sku = 'SKU-BBB')

Mixed Formats & Chains: Includes support chained segments and both DSL/JQL formats:

?include=orders(status:eq:cancelled).items(sku = 'SKU-AAA')

Exclusive Selection: If you provide a specific select path for a navigation, the library will only project those fields, overriding the default "include all scalars" behavior:

?include=orders(total > 100)&select=id,orders.number

(Result: Orders will have only ID and Number, filtered by total > 100)

Applying Both Pipelines

To use both, chain ApplyQueryOptions (for the WHERE/Sort/Paging pipeline) and ApplyFilteredIncludes (for the Include pipeline).

using FlexQuery.NET;
using FlexQuery.NET.EFCore;

var options = QueryOptionsParser.Parse(Request.Query);

var results = await _context.Customers
    .AsNoTracking()
    .ApplyQueryOptions(options)      // Pipeline 1: Root WHERE
    .ApplyFilteredIncludes(options)  // Pipeline 2: Filtered Includes
    .ToListAsync();

API Methods

Apply filter/sort/paging

using FlexQuery.NET;

var options = QueryOptionsParser.Parse(Request.Query);

var query = _context.Users.AsQueryable()
    .ApplyQueryOptions(options); // filter + sort + paging

var data = await query.ToListAsync();

Apply projection (select / include / JSON select tree)

var projected = await _context.Users
    .ApplyQueryOptions(options)
    .ApplySelect(options)
    .ToListAsync(); // IQueryable<object>

Flattened Projections

By default, the library preserves the original object hierarchy during projection. However, you can optionally flatten deep nested collections into a table-like rowset using SelectMany expression chains.

Default Nested Output (Before)

Without a flattening mode, the output reflects the entity structure:

[
  {
    "id": 1,
    "name": "Alice",
    "orders": [
      {
        "status": "Shipped",
        "orderItems": [
          { "product": "Laptop", "qty": 1 },
          { "product": "Mouse", "qty": 2 }
        ]
      }
    ]
  }
]

1. Flat Mode (mode=flat)

Linearizes a single navigation path into a flat list of leaf objects. This is ideal for reporting on deeply nested items where parent context is not required.

Query: ?select=orders.orderItems.productName as product,orders.orderItems.quantity as qty&mode=flat

Output:

[
  { "product": "Laptop", "qty": 1 },
  { "product": "Mouse", "qty": 2 }
]

Generated LINQ: The library builds a sequential SelectMany chain:

query.SelectMany(c => c.Orders)
     .SelectMany(o => o.OrderItems)
     .Select(oi => new { product = oi.ProductName, qty = oi.Quantity })

2. Flat-Mixed Mode (mode=flat-mixed)

Flattens root entity fields alongside deeply nested collection fields into a single rowset. This mode preserves parent context by using correlated SelectMany projections.

Query: ?select=id as customerId,name,orders.status as orderStatus,orders.orderItems.productName as product&mode=flat-mixed

Output:

[
  { "customerId": 1, "name": "Alice", "orderStatus": "Shipped", "product": "Laptop" },
  { "customerId": 1, "name": "Alice", "orderStatus": "Shipped", "product": "Mouse" }
]

Generated LINQ: The library carries context through the chain using a progressive anonymous type:

query.SelectMany(c => c.Orders, (c, o) => new { c, o })
     .SelectMany(x => x.o.OrderItems, (x, oi) => new {
         customerId = x.c.Id,
         name = x.c.Name,
         orderStatus = x.o.Status,
         product = oi.ProductName
     })

This mode allows you to "join" all levels of a hierarchy into a flat result set while maintaining full EF Core server-side translation.

Example Request and Response

Here is how a real-world request looks when combining JQL filtering, selective projection, and filtered includes.

Request

GET /api/customers?query=(name contains "Connelly")&pageSize=2&select=id,email,name,orders.id,orders.orderDate,orders.status,orders.orderItems&include=orders(status = "cancelled").orderItems(productName = "Tasty Metal Pants")

Response

{
  "totalCount": 1,
  "page": 1,
  "pageSize": 2,
  "totalPages": 1,
  "data": [
    {
      "id": 42,
      "name": "John Connelly",
      "email": "j.connelly@example.com",
      "orders": [
        {
          "id": 1001,
          "orderDate": "2026-04-15T10:30:00Z",
          "status": "cancelled",
          "orderItems": [
            {
              "id": 5001,
              "productId": 101,
              "productName": "Tasty Metal Pants",
              "unitPrice": 49.99
            }
          ]
        }
      ]
    }
  ]
}

In this example:

• Root Filtering: Limits results to customers whose name contains "Connelly".
• Selective Projection: Only fetches specified fields for customers and orders.
• Filtered Include: Returns only "cancelled" orders and specific items within them.
• Total Count: Returned as 1. If includeCount=false was passed, totalCount would be null to save on a database round-trip.

Return results with metadata

var result = _context.Users.ToQueryResult(options);
// result.Data, result.TotalCount, result.Page, result.PageSize

Return projected results with metadata

var result = _context.Users.ToProjectedQueryResult(options);
// result.Data is List<object> shaped by Select/Includes/JSON select tree

EF Core async helpers (package: FlexQuery.NET.EFCore)

var result = await _context.Users.ToQueryResultAsync(options, cancellationToken);
var projected = await _context.Users.ToProjectedQueryResultAsync(options, cancellationToken);

EF Core operator overrides (optional)

Core does not depend on EF Core. By default, like is handled with a framework-agnostic fallback:

• %value% → Contains
• %value → EndsWith
• value% → StartsWith

When using the EF Core package, opt in to EF-specific operator handlers:

using FlexQuery.NET.EFCore;

var options = QueryOptionsParser.Parse(Request.Query)
    .UseEfCoreOperators(); // registers EF.Functions.Like handler

var data = await _context.Users
    .ApplyQueryOptions(options)
    .ToListAsync();

ASP.NET Integration (optional)

You can parse directly in controllers/minimal APIs:

public abstract class BaseController : ControllerBase
{
    protected QueryOptions Options => QueryOptionsParser.Parse(Request.Query);
}

Production Readiness: Security & Performance

FlexQuery.NET is designed with enterprise-grade security and performance in mind, ensuring it can be safely exposed to public APIs.

Security Hardening & SQL Injection Protection

The library implements multiple layers of security to prevent malicious queries:

1. Strict Parameterization: All user-provided inputs (strings, numbers, dates) are automatically handled as safe parameterized constants. EF Core translates these securely into parameterized SQL (e.g., @p0). No raw SQL interpolation is used.
2. Fail-Fast Validation (JQL / DSL): The query parsers strictly enforce syntax validation. Any dangerous tokens (like ;, --, DROP, or UNION) result in an immediate JqlParseException, stopping execution before it ever reaches EF Core.
3. Whitelist-Based Execution:
   • Operators: Only whitelisted operators (e.g., eq, contains, any) are supported via the internal OperatorRegistry.
   • Fields: Properties and paths are strictly validated against your actual EF Core models. Non-existent or protected fields are rejected.
4. Alias Validation: Dynamic projections map user-provided aliases via strict regex (^[a-zA-Z0-9_]+$), neutralizing projection-based injection mapping attempts.

Performance & Query Optimization

When exposing queries over large datasets, performance is critical:

• Optimized Paging: When paging (skip/take) is requested without an explicit sort, the library automatically injects a default OrderBy (using Id or the primary key) to prevent EF Core errors on relational databases and ensure deterministic results.
• Efficient EXISTS Translation: Deeply nested filters like orders.any(orderItems.any(quantity > 5)) are efficiently translated by EF Core into nested SQL EXISTS clauses without fetching intermediate records into memory.
• Memory Optimization: Filtering is strictly applied before any dynamic projection, reducing the memory footprint of materialized objects.

Collation-Aware String Matching (Index-Friendly)

FlexQuery.NET generates string predicates without .ToLower() or .ToUpper() function calls. This is critical for query performance on large datasets.

Before (v1.5 and earlier):

-- LOWER() prevents index seeks — full table scans on every string comparison
WHERE LOWER([c].[Name]) LIKE '%john%'
  AND LOWER([o].[Status]) = 'cancelled'

After (v1.6+):

-- Clean predicates — SQL Server CI_AS collation handles case-insensitivity at the index level
WHERE [c].[Name] LIKE '%John%'
  AND [o].[Status] = 'Cancelled'

Case-insensitivity is handled by the database collation (SQL Server default: SQL_Latin1_General_CP1_CI_AS), which applies at the index level. This means:

• No function overhead on each row evaluation
• Index seeks are preserved on indexed string columns
• Behavior is unchanged for SQL Server users (CI collation = case-insensitive by default)

CaseInsensitive Option

You can control this behavior explicitly via QueryOptions.CaseInsensitive:

var options = QueryOptionsParser.Parse(Request.Query);

// Default: true — relies on database collation (recommended for SQL Server)
options.CaseInsensitive = true;

// Set to false for strict ordinal string matching (e.g., case-sensitive PostgreSQL columns)
options.CaseInsensitive = false;

Monitoring & Logging

We recommend leveraging EF Core's built-in Interceptors alongside ASP.NET Core Middleware to trace dynamic queries.

1. SQL Query Logging (EF Core Interceptor)

You can create an EF Core Interceptor to track slow dynamic queries and log execution metrics:

using Microsoft.EntityFrameworkCore.Diagnostics;
using System.Data.Common;

public class QueryPerformanceInterceptor : DbCommandInterceptor
{
    private readonly ILogger _logger;
    public QueryPerformanceInterceptor(ILogger logger) => _logger = logger;

    public override ValueTask<DbDataReader> ReaderExecutedAsync(
        DbCommand command, CommandExecutedEventData eventData, DbDataReader result, CancellationToken cancellationToken = default)
    {
        if (eventData.Duration.TotalMilliseconds > 500)
        {
            _logger.LogWarning("Slow Dynamic Query Executed ({Duration}ms):\n{CommandText}", eventData.Duration.TotalMilliseconds, command.CommandText);
        }
        return base.ReaderExecutedAsync(command, eventData, result, cancellationToken);
    }
}

// Program.cs
services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString);
    options.AddInterceptors(new QueryPerformanceInterceptor(logger));
});

2. Raw Request Logging (ASP.NET Core Middleware)

To track incoming dynamic requests, you can add a simple request logging middleware:

app.Use(async (context, next) =>
{
    if (context.Request.Query.ContainsKey("query"))
    {
        var logger = context.RequestServices.GetRequiredService<ILogger<Program>>();
        logger.LogInformation("Dynamic Query Request: {QueryString}", context.Request.QueryString.Value);
    }
    await next(context);
});

🛡️ Query Validation Engine

FlexQuery.NET provides a pluggable validation engine that inspects QueryOptions before they are applied to an IQueryable. This prevents invalid queries (e.g., non-existent fields, incompatible types) from causing runtime exceptions or database errors.

Using ApplyValidatedQueryOptions

The ApplyValidatedQueryOptions extension method validates the query options against a specific execution policy and applies them in a single step. It throws a QueryValidationException if any security or structural rules are violated.

This is useful when you have already manually parsed QueryOptions and need to apply a custom security policy.

using FlexQuery.NET;

var options = QueryOptionsParser.Parse(Request.Query);
var exec = new QueryExecutionOptions 
{ 
    AllowedFields = new[] { "Id", "Name", "Status" } 
};

try
{
    var users = await _context.Users
        .ApplyFlexQuery(request, options => { /* configure security rules */ })
        .ToListAsync();
}
catch (QueryValidationException ex)
{
    // Handle validation errors (ex.Result.Errors)
    return BadRequest(new { errors = ex.Result.Errors });
}

Manual Validation

You can also run the validation manually if you need to inspect the results before taking action:

using FlexQuery.NET.Validation;

var validator = new QueryValidator();
var result = validator.Validate<User>(options);

if (!result.IsValid)
{
    var errors = result.Errors;
    // ...
}

🛡️ Field-Level Security (Whitelisting / Blacklisting)

FlexQuery.NET includes a built-in security rule to restrict access to specific fields. This is integrated into the validation pipeline.

Whitelisting (AllowedFields)

Only fields in this list can be queried. Any attempt to filter, sort, or select a field not in this list will throw a QueryValidationException.

var options = QueryOptionsParser.Parse(Request.Query);
var exec = new QueryExecutionOptions 
{ 
    AllowedFields = new[] { "Id", "Name", "Email", "Orders.Status", "Orders.Total" }
};

// Throws if disallowed fields are used
query.ApplyValidatedQueryOptions(options, exec);

Blacklisting (BlockedFields)

Fields in this list are explicitly forbidden. This is useful for hiding sensitive data like SSNs or internal flags.

options.BlockedFields = new HashSet<string>(StringComparer.OrdinalIgnoreCase) 
{ 
    "SSN", "PasswordHash", "InternalMetadata" 
};

Whitelisting & Default Projection

FlexQuery provides powerful control over which fields can be projected back to the client. This is useful for both security (preventing sensitive data exposure) and performance (returning only what's needed).

Defining Selectable Fields

You can define allowed fields globally via execution options:

var exec = new QueryExecutionOptions 
{ 
    SelectableFields = new[] { "Id", "Name", "Orders.Number" } 
};

Wildcard Support (.*)

You can use wildcards to include all scalar properties of a navigation property:

// Includes Id of root, and all scalar properties of the Orders collection
options.Select = new List<string> { "Id", "Orders.*" };

Default Projection

If SelectableFields is configured and the client provides no select parameter, FlexQuery will automatically use the SelectableFields list as the default projection.

Strict Validation

By default, FlexQuery silently ignores unauthorized fields. Enable StrictFieldValidation to throw a QueryValidationException instead:

options.StrictFieldValidation = true;

Whitelisting Filterable & Sortable Fields

Similar to selection, you can restrict which fields can be used for filtering and sorting. This is critical for preventing denial-of-service (DoS) attacks via unindexed columns.

var exec = new QueryExecutionOptions();

// Only allow filtering on Name and Category (and everything under Category)
exec.FilterableFields = new[] { "Name", "Category.*" };

// Only allow sorting on Id and CreatedAt
exec.SortableFields = new[] { "Id", "CreatedAt" };

Wildcards (*) and deep paths (dot-notation) are fully supported across all security lists.

🌐 ASP.NET Core Integration (FlexQuery.NET.AspNetCore)

For ASP.NET Core applications, you can use the FlexQuery.NET.AspNetCore package to apply security declaratively via attributes.

1. Registration

Register the security filters in your Program.cs:

using FlexQuery.NET.AspNetCore.Extensions;

// For Controllers
builder.Services.AddControllers()
    .AddFlexQuerySecurity();

// OR for Minimal APIs / Web API
builder.Services.AddControllers(options =>
{
    options.Filters.Add<FieldAccessFilter>();
});

2. Declarative Security

Use the [FieldAccess] attribute on controllers or actions:

[ApiController]
[Route("api/[controller]")]
[FieldAccess(Allowed = new[] { "Id", "Name", "Email" })] // Controller-level whitelist
public class UsersController : ControllerBase
{
    [HttpGet]
    [FieldAccess(Allowed = new[] { "Orders.*" })] // Action-level additional fields
    public async Task<IActionResult> Get([FromQuery] QueryOptions options)
    {
        // Settings from attributes are automatically merged into 'options'
        return Ok(await _context.Users.ApplyValidatedQueryOptions(options).ToListAsync());
    }
}

🔍 Query Debug Mode

FlexQuery.NET provides a powerful debug mode to inspect the transformation from string-based queries to LINQ Expression Trees. This is essential for troubleshooting complex nested queries or verifying EF Core translation.

Usage

using FlexQuery.NET;

var options = QueryOptionsParser.Parse(Request.Query);
var debug = _context.Customers.ToFlexQueryDebug(options);

// Inspect the results
Console.WriteLine(debug.LinqLambda);      // The C#-like LINQ syntax
Console.WriteLine(debug.ExpressionTree);  // The structural node tree
Console.WriteLine(debug.Ast);             // The raw parsed AST (JQL/DSL)

Example Output

For a query like ?query=orders.any(status = Cancelled AND orderItems.id = 101):

LINQ Lambda:

query.Where(x => x.Orders.Any(sc => (sc.Status == "Cancelled") && sc.OrderItems.Any(i => i.Id == 101)))

AST (ToString):

orders.any(AND(status eq [Cancelled], orderItems.any(id eq [101])))

Security (Field-Level Access Control)

Never expose your entire database schema. FlexQuery.NET provides a robust, pluggable security pipeline that runs before database execution.

When using ApplyFlexQuery, configure your rules in the configuration delegate:

query.ApplyFlexQuery(request, options => 
{
    // 1. Whitelisting (Only allow these fields to be touched)
    options.AllowedFields = new[] { "Id", "Name", "Orders.*" };

    // 2. Blacklisting (Deny access to sensitive fields)
    options.BlockedFields = new[] { "PasswordHash", "SSN" };

    // 3. Operation-Specific Rules
    options.FilterableFields = new[] { "Status", "CreatedAt" };
    options.SortableFields = new[] { "CreatedAt" };
    options.SelectableFields = new[] { "Id", "Name", "Status" };

    // 4. Depth Protection
    options.MaxFieldDepth = 3; // Prevent 'Orders.Items.Product.Category.Name'

    // 5. Per-Field Operator Governance (New in v2.1)
    options.AllowOperators("Email", FilterOperators.Equal, FilterOperators.StartsWith);
});

If a client attempts to filter, sort, or select a restricted field, a QueryValidationException is thrown with detailed ValidationResult errors, preventing the query from ever hitting the database.

⚡ Performance: Expression Caching

FlexQuery.NET can cache the expensive process of parsing query objects into LINQ Expression trees. This is highly recommended for high-traffic APIs where the same query patterns (even with different values) are reused.

1. Global Enable

Enable caching globally in your Program.cs:

using FlexQuery.NET.Caching;

FlexQueryCacheSettings.EnableCache = true;
FlexQueryCacheSettings.MaxCacheSize = 5000; // Prevent memory leaks

2. Per-Query Control

You can override the global setting on individual requests:

var options = QueryOptionsParser.Parse(request);
options.EnableCache = true; // Force cache for this heavy query

3. Compiled Lambda Caching (Optional)

If you are using LINQ to Objects (not EF Core), you can also cache the compiled delegates:

FlexQueryCacheSettings.CacheCompiledLambdas = true;

🚀 EF Core: Split Query Optimization

When including multiple collections, you can prevent "cartesian explosion" by enabling split queries server-side:

var result = await _context.Users.FlexQueryAsync(parameters, exec =>
{
    // Executes collection includes as separate SQL queries
    exec.UseSplitQuery = true;
    
    // Automatically applies .AsNoTracking() (default true)
    exec.UseNoTracking = true; 
});

⚖️ License

This project is licensed under the MIT License - see the LICENSE file for details.