UniversalQueryBuilder.EntityFramework 10.0.13-beta

QueryBuilder.EntityFramework

Entity Framework Core execution engine for the Universal Query Builder system

Overview

QueryBuilder.EntityFramework is the Entity Framework Core execution strategy for Universal Query Builder, providing LINQ-to-Entities query execution against DbContext instances. It serves as the bridge between the universal QueryDefinition API and EF Core, offering automatic navigation property management, DTO projections, and multi-database support.

Key Features

• ✅ Automatic Navigation Includes - Detects navigation properties in filters/projections and applies .Include() automatically
• ✅ DTO Projection Support - Register entity→DTO projections once, auto-detects required navigation paths
• ✅ Multi-DbContext Support - Query across multiple DbContext instances via Schema Registry metadata
• ✅ Database Agnostic Core - Core EF query execution works with any EF Core provider; JSON scalar fields are a SQL Server-only extension
• ✅ Performance Optimized - Uses .AsNoTracking() by default, prevents N+1 queries, expression caching
• ✅ Rich Metadata - Detailed execution metrics including build time, DB time, generated SQL
• ✅ No Manual Includes - Strategy analyzes queries and applies necessary eager loading automatically
• ✅ Aggregation Support - GROUP BY queries with aggregate functions (COUNT, SUM, AVG, MIN, MAX) and HAVING clauses, including navigational GROUP BY on scalar and collection navigation properties
• ✅ Dynamic Hierarchical Projections - Runtime field selection with support for nested objects and collections
• ✅ Nested Relation Options - Apply filters, ordering, and limits directly to nested collections
• ✅ Configurable Query Splitting - Per-data-source or runtime control over EF Core split queries vs single queries
• ✅ SQL Server JSON Scalar Fields - Filter, order, project, group, and expose searchable fields backed by SQL Server JSON_VALUE
• ⚠️ Limitations - Regex, fuzzy matching, and explicit JOINs not supported. Nested relation options (where/limit on collections) may cause split queries.

Architecture Role

┌─────────────────────────────────────────────────────────────┐
│                    Universal Query API                       │
│                    (QueryDefinition)                         │
└────────────────────────┬────────────────────────────────────┘
                         │
                ┌────────┴────────┐
                │                 │
         ┌──────▼──────┐   ┌──────▼──────┐
         │EntityFramew │   │  InMemory   │
         │ork Strategy │   │  Strategy   │
         │ Priority: 10│   │ Priority:100│
         └──────┬──────┘   └─────────────┘
                                         │
                ┌────────────────────────┴─────────────────────┐
                │   EntityFrameworkExecutionStrategy           │
                │   • DbContext resolution                     │
                │   • Auto navigation includes                 │
                │   • DTO projection execution                 │
                │   • LINQ-to-Entities translation             │
                └────┬─────────────────────────────────────────┘
                     │
        ┌────────────┴────────────────┐
        │                             │
   ┌────▼──────────────┐      ┌───────▼──────────────┐
   │ ILinqQueryBuilder │      │ IProjectionRegistry  │
   │ • Build IQueryable│      │ • DTO projections    │
   │ • Apply filters   │      │ • Auto-detect navs   │
   │ • Apply ordering  │      │ • Expression storage │
   │ • Apply pagination│      └──────────────────────┘
   └────┬──────────────┘
        │
   ┌────▼────────────────────────────────┐
   │ Entity Framework Core               │
   │ • DbSet<T> queries                  │
   │ • .Include() navigation loading     │
   │ • .AsNoTracking() optimization      │
   │ • LINQ-to-SQL translation           │
   │ • Database provider abstraction     │
   └─────────────────────────────────────┘

---

Table of Contents

1. Core Concepts
2. Installation
3. Quick Start
4. Navigation Properties
5. DTO Projections
6. Multi-DbContext Scenarios
7. Configuration
   • Query Splitting Configuration
8. Performance
9. Supported Features
10. Testing
11. Advanced Usage
12. IQueryableBuilder
13. Integration
14. Best Practices
15. Comparison with Other Strategies
16. Limitations

---

Core Concepts

Two Execution Strategies

Universal Query Builder provides two complementary execution strategies:

Strategy	Data Source	Query Building	Best For
EntityFramework	EF Core DbContext	LINQ-to-Entities expressions	Applications using EF Core, multi-database support, navigation properties
InMemory	Custom providers	LINQ-to-Objects expressions	Caches, APIs, files, regex/fuzzy matching

Why Use EntityFramework Strategy?

Choose EntityFramework when:

• ✅ Your application already uses Entity Framework Core
• ✅ You need database-agnostic queries (PostgreSQL, MySQL, SQLite)
• ✅ You have complex navigation property hierarchies
• ✅ You want automatic DTO projections
• ✅ You need to query across multiple DbContext instances

Choose InMemory when:

• ✅ You need regex or fuzzy matching operators
• ✅ Data comes from non-database sources (caches, APIs, files)
• ✅ You want custom data transformations
• ✅ Rapid prototyping with static data

EF Model Membership and Inheritance

Entity Framework execution resolves registered sources through DbContext.Set<T>(). A registered source entity must be part of the resolved DbContext model, but it does not need its own explicit DbSet<TEntity> property.

• Register a base entity source to query the full TPH/TPT hierarchy using base fields.
• Register a derived entity source to query only that derived type and expose inherited plus derived fields.
• Querying a registered type that is not part of the resolved model fails with a CoreException that points back to EF model registration.

SQL Server JSON Scalar Fields

When a source registers JSON scalar fields through Schema Registry, the EF strategy treats them like normal schema fields in where, orderBy, select, grouping, and searchable metadata expansion.

builder
    .ExposeJsonValue<string>(x => x.ProfileJson, "profileCity", "$.city")
    .ExposeJsonValue<int?>(x => x.ProfileJson, "profileAge", "$.age")
    .ExposeJsonValue<string>(
        x => x.ProfileJson,
        "profileEmail",
        "$.email",
        json => json.HasDisplayName("Email").AddAlias("email").Searchable());

Important constraints:

• SQL Server only. A source with registered JSON scalar fields throws a targeted configuration error when executed against a non-SQL Server EF provider.
• Explicit registration only. Queries target registered field names such as profileCity; they do not pass raw JSON paths.
• Flat field names only. Use aliases when you want shorter query terms, for example city.
• Nullable value types are required because SQL Server JSON_VALUE and TRY_CONVERT return NULL for missing paths or invalid scalar conversions.
• TRY_CONVERT returns NULL silently when a JSON value cannot be converted to the target type (e.g., a non-numeric string in an int? field). Callers cannot distinguish "missing path" from "conversion failure" at the SQL level.
• JSON_VALUE returns NULL if the extracted value exceeds 4000 characters.
• v1 is scalar-only. Arrays, object projection, JSON_QUERY, OPENJSON, field transformations over JSON scalar fields, and HAVING references to JSON scalar fields are not supported.

Coalesced Columns

The EF execution strategy registers FieldAccessExpressionBuilder (in QueryBuilder.EntityFramework.Services), which replaces the default IFieldAccessExpressionBuilder and handles all three ColumnAccessor variants: Property, JsonScalar, and Coalesce.

A Coalesce(Sources, Fallback?) accessor is emitted as Expression.Coalesce folded left-associatively over its source accessors. When a LiteralFallback is present, it is appended as the terminal Expression.Constant(value, clrType). EF Core translates the resulting expression to a flat SQL COALESCE(...) call, so a column registered through WithCoalesce is queryable in where, orderBy, select, grouping, and search expansion exactly like a direct CLR property.

DISTINCT is supported on coalesced columns: a single-field DISTINCT query targeting a coalesced alias projects through the same scalar path used for property columns and emits SELECT DISTINCT COALESCE(...) in SQL. Multi-field DISTINCT remains unsupported, matching the property-column limitation.

A SQL COALESCE(...) expression is not indexable by a simple single-column index. For frequent filter targets, consider a persisted computed column at the database layer. See Coalesced Columns in the SchemaRegistry README for the configuration API and full rules.

Execution Strategy Pattern

QueryBuilder.EntityFramework implements IExecutionStrategy:

public interface IExecutionStrategy
{
    string Name { get; }                           // "EntityFramework"
    int Priority { get; }                          // 10 (primary database strategy)
    DataSourceType SupportedDataSourceType { get; } // DataSourceType.EntityFramework

    bool CanExecute(QueryDefinition query, IExecutionContext? context = null);
    Task<IQueryResult<Dictionary<string, object>>> ExecuteAsync(QueryDefinition query, IExecutionContext? context = null);
    Task<IEstimatedMetrics> EstimateAsync(QueryDefinition query, IExecutionContext? context = null);
    // Note: EstimateAsync is not yet implemented — throws NotImplementedException
    ValidationResult Validate(QueryDefinition query);
}

Priority Order:

• InMemory: 100 (highest - for in-memory sources)
• EntityFramework: 10 (primary database strategy)

Seven-Phase Execution Model

Phase 1: DbContext Resolution
   ├─ Try Schema Registry for specific DbContext type
   └─ Fallback to default registered DbContext

Phase 2: Projection Detection
   ├─ Check if query targets a DTO projection
   └─ Route to ExecuteWithProjection if applicable

Phase 3: Model Membership Validation
   ├─ Verify entity type is in DbContext EF model
   └─ Throw CoreException(InvalidConfiguration) if not mapped

Phase 4: Queryable Setup
   ├─ Query DbContext.Set<T>()
   └─ Apply .AsNoTracking() for read-only performance

Phase 5: Auto Navigation Includes
   ├─ Analyze filter expressions for navigation paths
   ├─ Extract paths (e.g., "Customer", "Customer.Address")
   └─ Apply .Include() calls to prevent N+1 queries

Phase 6: Query Building
   ├─ Apply filters via ILinqQueryBuilder
   ├─ Apply ordering (OrderBy, ThenBy)
   ├─ Apply pagination (Skip, Take)
   └─ Optionally apply DTO projection (.Select())

Phase 7: Execution & Results
   ├─ Execute via .ToListAsync()
   ├─ Capture generated SQL for debugging
   └─ Return IQueryResult<T> with rich metadata

---

Installation

NuGet Package (Future)

dotnet add package QueryBuilder.EntityFramework

Project Reference

<ItemGroup>
  <ProjectReference Include="../QueryBuilder.EntityFramework/QueryBuilder.EntityFramework.csproj" />
</ItemGroup>

Dependencies

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="10.0.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="10.0.0" />

Implicit Dependencies:

• QueryBuilder.Core - Core models and abstractions
• QueryBuilder.Expressions - Expression compilation with caching
• QueryBuilder.SchemaRegistry - Multi-DbContext support and metadata

---

Quick Start

1. Configure DbContext and Services

using QueryBuilder.Core.Extensions;
using QueryBuilder.EntityFramework.Extensions;
using QueryBuilder.SchemaRegistry.Extensions;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// Register DbContext (example with PostgreSQL)
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection")));

// Register Universal Query Builder and its components
builder.Services.AddUniversalQueryBuilder(options =>
{
    // Registers EF strategy and automatically handles DbContext aliasing
    options.UseEntityFramework<AppDbContext>();

    // Registers the code-first schema registry
    options.AddCodeFirstSchemaRegistry(schemaOptions =>
    {
        schemaOptions.DefaultSourceNameCase = SourceNameCase.KebabCase;
    });

    // Configure projections (e.g., enable strict mode for hierarchical projections)
    options.ConfigureProjections(p => p.StrictMode = true);
});

// Register aggregation support (for GROUP BY queries)
builder.Services.AddSingleton<QueryBuilder.Expressions.Services.AggregationExpressionBuilder>();

var app = builder.Build();

2. Define Entity Models

public class Order
{
    public int Id { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal TotalAmount { get; set; }
    public string Status { get; set; } = string.Empty;

    // Navigation properties
    public int CustomerId { get; set; }
    public Customer Customer { get; set; } = null!;
    public List<OrderItem> Items { get; set; } = new();
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public string City { get; set; } = string.Empty;

    // Navigation property
    public int? AddressId { get; set; }
    public Address? Address { get; set; }
    public List<Order> Orders { get; set; } = new();
}

public class OrderItem
{
    public int Id { get; set; }
    public int OrderId { get; set; }
    public int ProductId { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }

    public Order Order { get; set; } = null!;
    public Product Product { get; set; } = null!;
}

3. Register Data Source

// Define a data source using fluent configuration
public class UserConfiguration : IDataSourceConfiguration<User>
{
    public void Configure(DataSourceBuilder<User, AppDbContext> builder)
    {
        builder
            .HasSourceName("users")
            .HasDisplayName("Users")
            .ExposeProperties(u => new { u.Id, u.FirstName, u.LastName, u.Email });
    }
}

// Unified registration in Program.cs
builder.Services.AddUniversalQueryBuilder(options =>
{
    options.AddCodeFirstSchemaRegistry();
});

4. Execute Query

using QueryBuilder.Core.Models;
using QueryBuilder.Core.Extensions;

// Build query
var query = new QueryDefinition
{
    From = "orders",
    Where = FilterDefinitionExtensions.And(
        FilterDefinitionExtensions.Equal("Status", "Active"),
        FilterDefinitionExtensions.Equal("Customer.City", "Seattle")
        // ↑ Strategy auto-detects "Customer" navigation and applies .Include()
    ),
    OrderBy = new[] {
        new OrderingDefinition { Field = "OrderDate", Direction = SortDirection.Descending }
    },
    Limit = 10,
    Offset = 0
};

// Execute via strategy
var strategy = serviceProvider.GetRequiredService<EntityFrameworkExecutionStrategy>();
var result = await strategy.ExecuteAsync(query);

// Use results
if (result.Success)
{
    Console.WriteLine($"Found {result.TotalCount} orders");

    foreach (var order in result.Data)
    {
        Console.WriteLine($"Order #{order["Id"]}");
    }

    // Access detailed metadata
    Console.WriteLine($"Auto-included navigations: {string.Join(", ", (string[])result.AdditionalMetadata["NavigationIncludes"])}");
    Console.WriteLine($"Generated SQL: {result.AdditionalMetadata["GeneratedSql"]}");
}

5. Example Output

Found 127 orders
Auto-included navigations: Customer
Generated SQL: SELECT [o].[Id], [o].[OrderDate], ... FROM [Orders] AS [o] INNER JOIN [Customers] AS [c] ON [o].[CustomerId] = [c].[Id] WHERE [o].[Status] = @p0 AND [c].[City] = @p1

---

Navigation Properties

Automatic Navigation Include Detection

The key differentiator of EntityFramework strategy is automatic navigation property handling for entity queries. The strategy analyzes filter and ordering expressions to detect navigation references, then applies .Include() calls automatically to prevent N+1 queries.

Important: This auto-include behavior applies to entity queries only. Projection queries skip auto-includes because EF Core handles navigation loading automatically via the .Select() expression.

How It Works (Entity Queries)

The strategy uses reflection-based analysis to detect navigation properties in filter and ordering expressions:

Reflection-Based Detection:

// User query with navigation filter
var query = new QueryDefinition
{
    Where = FilterDefinitionExtensions.Equal("Customer.Address.City", "Seattle")
};

// Strategy internally:
// 1. Analyzes filter via reflection: "Customer.Address.City"
// 2. Extracts navigation paths: ["Customer", "Customer.Address"]
// 3. Applies includes:
//    queryable = queryable.Include("Customer");
//    queryable = queryable.Include("Customer.Address");
// 4. Builds filter: .Where(x => x.Customer.Address.City == "Seattle")

2. Collection Navigation:

// Query referencing collection navigation
var query = new QueryDefinition
{
    Where = FilterDefinitionExtensions.GreaterThan("Items.Count", 0)
    //                                               ^^^^^
    //                                               Collection navigation detected
};

// Strategy applies:
// queryable = queryable.Include("Items");
// queryable = queryable.Where(x => x.Items.Count > 0);

3. Multi-Level Navigation:

// Deep navigation path
var query = new QueryDefinition
{
    Where = FilterDefinitionExtensions.And(
        FilterDefinitionExtensions.Equal("Customer.Company.Name", "Acme Corp"),
        FilterDefinitionExtensions.Equal("Items.Product.Category", "Electronics")
    )
};

// Strategy applies:
// queryable.Include("Customer")
// queryable.Include("Customer.Company")
// queryable.Include("Items")
// queryable.Include("Items.Product")

Supported Relationship Types

Relationship	Example	Handling
One-to-One	Order.Customer	Auto-included if referenced
One-to-Many	Order.Items (List<OrderItem>)	Auto-included, prevents N+1
Many-to-One	OrderItem.Product	Auto-included
Multi-Level	Order.Customer.Address.City	Entire path auto-included
Optional	Customer.Address? (nullable FK)	Auto-included, gracefully handles nulls

N+1 Query Prevention

Without EntityFramework Strategy (Manual EF Core):

// ❌ BAD - N+1 query problem
var orders = dbContext.Orders
    .Where(o => o.Status == "Active")
    .ToList();

foreach (var order in orders)  // 1 query to get orders
{
    var customer = order.Customer;  // N additional queries (one per order)
    Console.WriteLine(customer.Name);
}
// Total: 1 + N queries (if 100 orders, 101 queries!)

With EntityFramework Strategy:

// ✅ GOOD - Single query with auto-includes
var query = new QueryDefinition
{
    From = "orders",
    Where = FilterDefinitionExtensions.Equal("Status", "Active")
        .And(FilterDefinitionExtensions.Contains("Customer.Name", "Smith"))
        // ↑ Strategy detects "Customer" navigation
};

var result = await strategy.ExecuteAsync(query);

foreach (var order in result.Data)
{
    Console.WriteLine($"Order: {order["Id"]}");  // Already loaded via JOIN!
}
// Total: 1 query with INNER JOIN

Manual Navigation Paths (Not Needed for Projections)

Note: Explicit includeNavigations parameter is not needed for projection queries. EF Core automatically handles navigation loading based on the projection expression itself.

// ✅ Correct - EF Core handles it automatically
projectionRegistry.Register<Order, OrderSummaryDto>(
    o => new OrderSummaryDto
    {
        OrderId = o.Id,
        CustomerName = o.Customer.Name,  // EF Core auto-loads Customer
        ItemCount = o.Items.Count        // EF Core auto-loads Items
    }
);

// ❌ Unnecessary (but harmless if specified)
projectionRegistry.Register<Order, OrderSummaryDto>(
    o => new OrderSummaryDto { /* ... */ },
    includeNavigations: new[] { "Customer", "Items" }  // Redundant
);

The includeNavigations parameter exists for backward compatibility but is ignored during projection query execution.

---

DTO Projections

Why DTO Projections?

Benefits:

• Reduce data transfer (only selected fields)
• Flatten complex object graphs
• Hide sensitive properties (e.g., PasswordHash)
• Improve query performance (fewer columns)
• API-friendly response shapes

Projection Registry

The IProjectionRegistry service manages entity→DTO transformations:

public interface IProjectionRegistry
{
    void Register<TSource, TProjection>(
        Expression<Func<TSource, TProjection>> selector,
        string[]? includeNavigations = null);  // Optional explicit includes

    Expression<Func<TSource, TProjection>>? Get<TSource, TProjection>();
    string[]? GetIncludePaths<TSource, TProjection>();
    bool IsRegistered<TSource, TProjection>();
    Type? GetSourceType(Type projectionType);
}

Basic Projection Example

1. Define DTO:

public class OrderSummaryDto
{
    public int OrderId { get; set; }
    public DateTime OrderDate { get; set; }
    public string CustomerName { get; set; } = string.Empty;
    public string CustomerCity { get; set; } = string.Empty;
    public int ItemCount { get; set; }
    public decimal TotalAmount { get; set; }
}

2. Register Projection (at startup):

var projectionRegistry = serviceProvider.GetRequiredService<IProjectionRegistry>();

projectionRegistry.Register<Order, OrderSummaryDto>(
    o => new OrderSummaryDto
    {
        OrderId = o.Id,
        OrderDate = o.OrderDate,
        CustomerName = o.Customer.Name,      // EF Core auto-loads "Customer" navigation
        CustomerCity = o.Customer.City,
        ItemCount = o.Items.Count,           // EF Core auto-loads "Items" navigation
        TotalAmount = o.TotalAmount
    }
    // EF Core handles navigation loading automatically via the projection expression
);

3. Register in Schema Registry:

await registrationService.RegisterProjectionSourceAsync(
    sourceName: "order-summaries",
    dbContextType: typeof(AppDbContext),
    sourceEntityType: typeof(Order),         // Source entity
    projectionType: typeof(OrderSummaryDto), // Target DTO
    displayName: "Order Summaries",
    createdBy: "admin"
);

4. Query with Projection:

var query = new QueryDefinition
{
    From = "order-summaries",
    Where = FilterDefinitionExtensions.GreaterThan("TotalAmount", 1000)
};

// Strategy detects this is a projection query and:
// 1. Queries DbContext.Set<Order>() (source entity)
// 2. Applies filters on Order entity
// 3. Projects to DTO: .Select(o => new OrderSummaryDto { ... })
// 4. EF Core automatically includes navigations referenced in projection
// 5. Returns OrderSummaryDto results

var result = await strategy.ExecuteAsync(query);

foreach (var summary in result.Data)
{
    Console.WriteLine($"{summary["CustomerName"]}: {summary["ItemCount"]} items, ${summary["TotalAmount"]}");
}

Generated SQL (conceptual):

SELECT
    [o].[Id] AS [OrderId],
    [o].[OrderDate],
    [c].[Name] AS [CustomerName],
    [c].[City] AS [CustomerCity],
    (SELECT COUNT(*) FROM [OrderItems] WHERE [OrderId] = [o].[Id]) AS [ItemCount],
    [o].[TotalAmount]
FROM [Orders] AS [o]
INNER JOIN [Customers] AS [c] ON [o].[CustomerId] = [c].[Id]
WHERE [o].[TotalAmount] > @p0

Nested Field Selection (Hierarchical Projections)

Nested Field Selection allows runtime field selection with support for nested objects and collections. This replaces the legacy fields property with a hierarchical dictionary structure.

Features:

• Recursive Selection: Select fields from nested objects.
• Nested Collections: Select fields from child collections with full control.
• Relation Options: Apply Where, OrderBy, Limit, and Offset to nested collections.
• Schema-Aware Relation Fields: Nested collection where/orderBy resolve the same schema field names as top-level queries, including aliases and SQL Server JSON scalar fields.

Implementation: Projections are handled by NestedProjectionBuilder which compiles hierarchical LINQ expressions.

Example: Basic Selection

{
  "from": "customers",
  "select": {
    "Id": true,
    "Name": true,
    "Email": true
  }
}

var query = new QueryDefinition
{
    From = "customers",
    Select = new SelectionDictionary
    {
        { "Id", true },
        { "Name", true },
        { "Email", true }
    }
};

var result = await strategy.ExecuteAsync(query);

Example: Nested Object Selection

{
  "from": "users",
  "select": {
    "Name": true,
    "Address": {
      "select": {
        "City": true,
        "Country": true
      }
    }
  }
}

Example: Nested Collection with Relation Options

This feature allows you to filter and sort child collections directly in the projection:

{
  "from": "users",
  "select": {
    "Name": true,
    "Orders": {
      "select": {
        "OrderDate": true,
        "TotalAmount": true
      },
      "where": { "field": "Status", "operator": "eq", "value": "Completed" },
      "orderBy": [{ "field": "OrderDate", "direction": "desc" }],
      "limit": 5
    }
  }
}

Key Difference with Unified Syntax: The select keyword is used for nested projection definitions, and relation options (where, limit, etc.) are siblings of select within the nested configuration object.

Execution Priority:

When a query has explicit Select:

1. Dynamic projection is used (results returned as Dictionary<string, object>).
2. EF Core translates nested options into optimized SQL (e.g., OUTER APPLY).

Automatic Navigation Includes:

The execution strategy automatically extracts navigation paths from the hierarchical selection and applies selective .Include() calls for entity queries. For hierarchical projections, NestedProjectionBuilder handles navigation loading automatically via projected LINQ expressions.

Projections and Navigation Loading

Important: Projection queries do not use explicit .Include() calls. Entity Framework Core automatically loads all navigation properties referenced within a .Select() projection expression.

Why Auto-Includes Are Skipped:

When you write a projection like:

o => new OrderDto
{
    CustomerName = o.Customer.Name,
    ItemCount = o.Items.Count
}

EF Core analyzes the projection expression and automatically includes the required navigations (Customer, Items) without explicit .Include() calls. Adding explicit includes can cause path conflicts when navigations are nested within collections (e.g., trying to .Include("Product") when it's actually at Orders.OrderItems.Product causes "Unable to find navigation" errors).

Example: Deep Navigation in Projection:

// Projection with nested collection navigation
user => new UserDetailsDto
{
    Orders = user.Orders.Select(order => new OrderDto
    {
        Items = order.OrderItems.Select(item => new ItemDto
        {
            ProductName = item.Product.Name  // Nested navigation
        })
    })
}

// EF Core automatically loads: Orders → OrderItems → Product
// No explicit .Include() needed or wanted

For Entity Queries (Non-Projection):

Entity queries (those that return entities like Order, not DTOs) still use auto-includes based on filter expressions to prevent N+1 queries.

Complex Projection Example

public class CustomerProfileDto
{
    public int CustomerId { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;

    // Flattened address
    public string Street { get; set; } = string.Empty;
    public string City { get; set; } = string.Empty;
    public string ZipCode { get; set; } = string.Empty;

    // Flattened company
    public string CompanyName { get; set; } = string.Empty;
    public string CompanyIndustry { get; set; } = string.Empty;

    // Aggregated order data
    public int TotalOrders { get; set; }
    public decimal TotalRevenue { get; set; }
}

// Registration
projectionRegistry.Register<Customer, CustomerProfileDto>(
    c => new CustomerProfileDto
    {
        CustomerId = c.Id,
        Name = c.Name,
        Email = c.Email,

        // Address navigation (nullable)
        Street = c.Address != null ? c.Address.Street : "",
        City = c.Address != null ? c.Address.City : "",
        ZipCode = c.Address != null ? c.Address.ZipCode : "",

        // Company navigation (nullable)
        CompanyName = c.Company != null ? c.Company.Name : "",
        CompanyIndustry = c.Company != null ? c.Company.Industry : "",

        // Order aggregations
        TotalOrders = c.Orders.Count,
        TotalRevenue = c.Orders.Sum(o => o.TotalAmount)
    }
);

// EF Core automatically includes all referenced navigations:
// Address, Company, Orders

---

Multi-DbContext Scenarios

Why Multiple DbContexts?

Common Scenarios:

• Microservices - Each service has its own database/context
• Multi-Tenancy - Separate contexts per tenant
• Read/Write Separation - ReadDbContext and WriteDbContext
• Legacy Integration - Old and new database contexts

Configuration

1. Register Multiple DbContexts:

builder.Services.AddDbContext<OrderDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("OrdersDb")));

builder.Services.AddDbContext<CustomerDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("CustomersDb")));

builder.Services.AddDbContext<ProductDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("ProductsDb")));

2. Register Default DbContext (optional):

// If most queries use one context, register as default
builder.Services.AddScoped<DbContext>(sp =>
    sp.GetRequiredService<OrderDbContext>());

3. Register Sources with Specific DbContext Types:

// Orders source uses OrderDbContext
await registrationService.RegisterEntityFrameworkSourceAsync(
    sourceName: "orders",
    dbContextType: typeof(OrderDbContext),  // Specific DbContext
    entityType: typeof(Order),
    displayName: "Sales Orders",
    createdBy: "admin"
);

// Customers source uses CustomerDbContext
await registrationService.RegisterEntityFrameworkSourceAsync(
    sourceName: "customers",
    dbContextType: typeof(CustomerDbContext),  // Different DbContext
    entityType: typeof(Customer),
    displayName: "Customers",
    createdBy: "admin"
);

// Products source uses ProductDbContext
await registrationService.RegisterEntityFrameworkSourceAsync(
    sourceName: "products",
    dbContextType: typeof(ProductDbContext),  // Third DbContext
    entityType: typeof(Product),
    displayName: "Products",
    createdBy: "admin"
);

4. Query Any Source:

// Query orders (uses OrderDbContext)
var orderQuery = new QueryDefinition
{
    From = "orders"
};
var orderResult = await strategy.ExecuteAsync(orderQuery);

// Query customers (uses CustomerDbContext)
var customerQuery = new QueryDefinition
{
    From = "customers"
};
var customerResult = await strategy.ExecuteAsync(customerQuery);

// Strategy automatically resolves correct DbContext via Schema Registry metadata

DbContext Resolution Flow

1. User submits query
   From: { SourceName: "orders" }

       ↓

2. Strategy looks up in Schema Registry
   DataSourceRegistry:
   {
     SourceName: "orders",
     Type: EntityFramework,
     DbContextTypeName: "MyApp.Data.OrderDbContext, MyApp"
   }

       ↓

3. Strategy resolves DbContext via reflection
   var dbContextType = Type.GetType("MyApp.Data.OrderDbContext, MyApp");
   var dbContext = serviceProvider.GetService(dbContextType) as DbContext;

       ↓

4. Execute query against resolved DbContext
   var dbSet = dbContext.Set<Order>();
   var queryable = dbSet.AsNoTracking();
   ...

---

Configuration

Basic Registration

using QueryBuilder.Core.Extensions;
using QueryBuilder.EntityFramework.Extensions;
using QueryBuilder.SchemaRegistry.Extensions;

services.AddUniversalQueryBuilder(options =>
{
    // Registers EF strategy and automatically handles DbContext aliasing
    options.UseEntityFramework<AppDbContext>();

    // Registers the code-first schema registry
    options.AddCodeFirstSchemaRegistry(schemaOptions =>
    {
        schemaOptions.DefaultSourceNameCase = SourceNameCase.KebabCase;
    });
});

What Gets Registered

public static IServiceCollection AddQueryBuilderEntityFramework(
    this IServiceCollection services)
{
    // Projection registry - Singleton (thread-safe cache)
    services.TryAddSingleton<IProjectionRegistry, ProjectionRegistry>();

    // Expression compilation services
    services.AddExpressionCompilation();

    // Property access builder - Scoped (per request)
    services.TryAddScoped<IPropertyAccessBuilder, PropertyAccessBuilder>();

    // Expression builder - Scoped
    services.TryAddScoped(typeof(IExpressionBuilder<>), typeof(JsonToExpressionBuilder<>));

    // LINQ query builder - Scoped
    services.TryAddScoped<ILinqQueryBuilder, LinqQueryBuilder>();

    // Strategy implementation - Scoped (aligned with DbContext lifetime)
    services.TryAddScoped<IExecutionStrategy, EntityFrameworkExecutionStrategy>();
    services.TryAddScoped<EntityFrameworkExecutionStrategy>();

    return services;
}

Service Lifetimes

Service	Lifetime	Reason
IProjectionRegistry	Singleton	Thread-safe cache, shared across requests
ILinqQueryBuilder	Scoped	Per-request, aligns with DbContext
IExpressionBuilder<T>	Scoped	Per-request compilation
EntityFrameworkExecutionStrategy	Scoped	Aligns with DbContext lifetime

Query Splitting Configuration

Problem: EF Core generates a single query with JOINs by default. When multiple collection navigations are included, this causes Cartesian explosion (duplicate rows multiplied across collections).

Solution: Split queries execute separate SQL queries per collection navigation, avoiding data duplication at the cost of additional round-trips.

Schema Registry Configuration (Per-Data-Source Default):

public class CustomerConfiguration : IDataSourceConfiguration<Customer>
{
    public void Configure(DataSourceBuilder<Customer, AppDbContext> builder)
    {
        builder
            .HasSourceName("customers")
            .ExposeProperties(c => new { c.Id, c.Name })
            .ConfigureNavigation(c => c.Orders, orders => ...)
            .ConfigureNavigation(c => c.Addresses, addresses => ...)
            .WithQuerySplitting();  // Enable split queries for this data source
    }
}

Runtime Override (Per-Query):

// Force split query mode for this specific query
var context = ExecutionContext.Create()
    .WithQuerySplitting(true)
    .Build();

var result = await strategy.ExecuteAsync(query, context);

// Force single query mode (override schema registry config)
var singleQueryContext = ExecutionContext.Create()
    .WithQuerySplitting(false)
    .Build();

Precedence:

1. ExecutionContext.QuerySplitting - Runtime override (highest)
2. DataSourceDefinition.QuerySplitting - Schema registry configuration
3. No change - EF Core default (single query mode)

When to Enable:

• EF Core warning: "Compiling a query which loads related collections..."
• Queries with multiple collection navigations
• Wide tables with large row sizes

Trade-offs: | Single Query (Default) | Split Query | |------------------------|-------------| | One database round-trip | Multiple round-trips | | Cartesian explosion risk | No data duplication | | Data consistency guaranteed | Potential consistency issues | | Better for small result sets | Better for large result sets |

Complete Setup Example

var builder = WebApplication.CreateBuilder(args);

// Step 1: Register DbContext
builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection"));

    // Optional: EF Core configuration
    options.EnableSensitiveDataLogging(builder.Environment.IsDevelopment());
    options.EnableDetailedErrors(builder.Environment.IsDevelopment());
});

// Step 2: Register Schema Registry
builder.Services.AddSchemaRegistry(options =>
{
    options.ConnectionString = builder.Configuration.GetConnectionString("DefaultConnection");
});

// Step 3: Register EntityFramework strategy
builder.Services.AddQueryBuilderEntityFramework();

// Step 4: Register other strategies (optional)
builder.Services.AddQueryBuilderInMemory();

var app = builder.Build();

// Step 5: Register data sources and projections at startup
using (var scope = app.Services.CreateScope())
{
    var services = scope.ServiceProvider;
    var registrationService = services.GetRequiredService<RegistrationService>();
    var projectionRegistry = services.GetRequiredService<IProjectionRegistry>();

    // Register entity sources
    await registrationService.RegisterEntityFrameworkSourceAsync(
        sourceName: "orders",
        dbContextType: typeof(AppDbContext),
        entityType: typeof(Order),
        displayName: "Sales Orders",
        createdBy: "system"
    );

    // Register projections
    projectionRegistry.Register<Order, OrderSummaryDto>(
        o => new OrderSummaryDto
        {
            OrderId = o.Id,
            CustomerName = o.Customer.Name,
            ItemCount = o.Items.Count,
            TotalAmount = o.TotalAmount
        }
    );

    await registrationService.RegisterProjectionSourceAsync(
        sourceName: "order-summaries",
        dbContextType: typeof(AppDbContext),
        sourceEntityType: typeof(Order),
        projectionType: typeof(OrderSummaryDto),
        displayName: "Order Summaries",
        createdBy: "system"
    );
}

app.Run();

---

Performance

Performance Characteristics

Query execution time is dominated by database round-trip latency. The strategy's LINQ composition overhead is minimal relative to network and database execution time.

Optimization: .AsNoTracking()

Disables EF Core change tracking for read-only queries, reducing memory usage and avoiding identity map and proxy creation overhead.

Applied by default in EntityFramework strategy:

var queryable = dbContext.Set<T>().AsNoTracking();
// Change tracking disabled for all queries

Trade-off: Cannot call .SaveChangesAsync() on returned entities (by design for read-only queries).

Optimization: Auto Navigation Includes

Without Strategy (manual EF Core):

// N+1 query problem
var orders = dbContext.Orders.ToList();  // 1 query
foreach (var order in orders)
{
    var customer = order.Customer;  // N queries (lazy loading)
}
// Total: 1 + N queries

With Strategy:

// Single query with auto-includes
var result = await strategy.ExecuteAsync(query);
// Strategy applies: .Include("Customer")
// Total: 1 query with JOIN

Auto-includes convert N+1 query patterns into single JOINed queries, reducing database round-trips proportional to result set size.

Optimization: Expression Caching

Leverages QueryBuilder.Expressions L1/L2 caching. Expression caching avoids recompilation overhead on repeated queries by caching compiled expression trees. Identical queries hit L1 cache; structurally similar queries hit L2 cache.

Optimization: Deferred Execution

LINQ queries use deferred execution - database access happens only at .ToListAsync():

// No DB access yet
var queryable = dbContext.Orders.AsNoTracking();
queryable = queryable.Include("Customer");
queryable = queryable.Where(x => x.Status == "Active");
queryable = queryable.OrderBy(x => x.OrderDate);

// Single DB access here
var results = await queryable.ToListAsync();

Benefit: All filters pushed to database, only matching rows returned.

---

Supported Features

Query Features

Feature	Status	Notes
Filters	✅ Fully supported	All comparison operators
Logical operators	✅ Fully supported	AND, OR, NOT
Ordering	✅ Fully supported	Single/multiple fields, asc/desc
Pagination	✅ Fully supported	Skip/Take (limit/offset)
Navigation properties	✅ Fully supported	Auto-detect and include
DTO projections	✅ Fully supported	Via IProjectionRegistry
Multi-DbContext	✅ Fully supported	Via Schema Registry metadata
Fuzzy matching	❌ Not supported	Use InMemory strategy
Explicit JOINs	❌ Not supported	Use navigation properties instead
GROUP BY	✅ Implemented (v1.0)	Single GROUP BY field (top-level or navigational path), 5 aggregates (COUNT, SUM, AVG, MIN, MAX)
HAVING	✅ Implemented (v1.0)	Post-aggregation filtering with explicit functions (no alias resolution)
Dynamic Projections	✅ Implemented (v1.0)	Runtime field selection with SelectionDictionary and SelectionConfig
Query Splitting	✅ Implemented (v1.0)	Per-data-source or runtime control via WithQuerySplitting() or ExecutionContext
DISTINCT	✅ Implemented	Single-field projected queries only — supports property, JSON scalar, and coalesced columns. Uses the scalar projection path for correct SQL translation. Multi-field DISTINCT throws QueryExecutionException. Offset is not supported with DISTINCT.

Supported Operators

Comparison Operators:

• eq (Equal)
• ne (NotEqual)
• gt (GreaterThan)
• gte (GreaterThanOrEqual)
• lt (LessThan)
• lte (LessThanOrEqual)
• in (In)
• nin (NotIn)
• between (Between)
• contains (String contains)
• startswith (String starts with)
• endswith (String ends with)
• isnull (Is null)
• isnotnull (Is not null)

Logical Operators:

• and
• or
• not

Flags Enum Support:

For [Flags] enum fields, eq, ne, in, and nin use bitwise membership semantics. The expression (field & mask) == mask translates to SQL Server's bitwise AND operator. Flags enum columns must be stored as integers (the EF Core default). String-stored flags enums (HasConversion<string>()) are not supported because bitwise AND cannot operate on string values in SQL.

Not Supported:

• regex - Use InMemory strategy
• fuzzy - Throws FeatureNotSupportedException
• Comparison/range/string operators on [Flags] enum fields - Throws QueryValidationException

---

Testing

Test Coverage

Test Files:

tests/QueryBuilder.EntityFramework.Tests/
├── Integration/
│   ├── EntityFrameworkExecutionStrategyTests.cs
│   ├── ProjectionTests.cs
│   ├── NavigationIncludeTests.cs
│   ├── CollectionNavigationIncludeTests.cs
│   └── CollectionNavigationE2ETests.cs
├── Unit/
│   └── ProjectionRegistryTests.cs
└── Fixtures/
    ├── TestDbContext.cs
    └── TestEntities.cs

Test Patterns

1. Strategy Execution Tests:

public class EntityFrameworkExecutionStrategyTests
{
    [Fact]
    public async Task ExecuteAsync_ShouldFilterOrders()
    {
        // Arrange
        var query = new QueryDefinition
        {
            From = "orders",
            Where = FilterDefinitionExtensions.Equal("Status", "Active")
        };

        // Act
        var result = await _strategy.ExecuteAsync(query);

        // Assert
        Assert.True(result.Success);
        Assert.All(result.Data, o => Assert.Equal("Active", (string)o["Status"]));
    }

    [Fact]
    public async Task ExecuteAsync_ShouldAutoIncludeNavigations()
    {
        var query = new QueryDefinition
        {
            Where = FilterDefinitionExtensions.Equal("Customer.City", "Seattle")
        };

        var result = await _strategy.ExecuteAsync(query);

        Assert.True(result.Success);
        Assert.All(result.Data, o => Assert.True(o.ContainsKey("Customer")));
        Assert.Contains("Customer", (string[])result.AdditionalMetadata["NavigationIncludes"]);
    }
}

2. Projection Tests:

public class ProjectionTests
{
    [Fact]
    public async Task ExecuteAsync_ShouldProjectToDto()
    {
        // Arrange
        _projectionRegistry.Register<Order, OrderSummaryDto>(
            o => new OrderSummaryDto
            {
                OrderId = o.Id,
                CustomerName = o.Customer.Name
            }
        );

        var query = new QueryDefinition
        {
            From = "order-summaries"
        };

        // Act
        var result = await _strategy.ExecuteAsync(query);

        // Assert
        Assert.True(result.Success);
        Assert.All(result.Data, dto => Assert.NotEmpty((string)dto["CustomerName"]));
    }

    [Fact]
    public void Register_ShouldAutoDetectNavigationPaths()
    {
        _projectionRegistry.Register<Order, OrderSummaryDto>(
            o => new OrderSummaryDto
            {
                CustomerName = o.Customer.Name,
                ItemCount = o.Items.Count
            }
        );

        var paths = _projectionRegistry.GetIncludePaths<Order, OrderSummaryDto>();

        Assert.Contains("Customer", paths);
        Assert.Contains("Items", paths);
    }
}

3. Navigation Include Tests:

public class NavigationIncludeTests
{
    [Fact]
    public async Task ShouldIncludeMultiLevelNavigation()
    {
        var query = new QueryDefinition
        {
            Where = FilterDefinitionExtensions.Equal("Customer.Address.City", "Seattle")
        };

        var result = await _strategy.ExecuteAsync(query);

        var includes = (string[])result.AdditionalMetadata["NavigationIncludes"];
        Assert.Contains("Customer", includes);
        Assert.Contains("Customer.Address", includes);
    }

    [Fact]
    public async Task ShouldPreventN1Queries()
    {
        var query = new QueryDefinition
        {
            Where = FilterDefinitionExtensions.Contains("Customer.Name", "Smith")
        };

        var result = await _strategy.ExecuteAsync(query);

        // Verify results returned
        Assert.All(result.Data, o =>
        {
            Assert.True(o.ContainsKey("Customer"));
        });

        // Verify single query (check DB query count if needed)
        Assert.Equal(1, (int)result.AdditionalMetadata["DatabaseRoundtrips"]);
    }
}

Running Tests

# All tests
dotnet test

# Specific test file
dotnet test --filter "FullyQualifiedName~EntityFrameworkExecutionStrategyTests"

# Verbose output
dotnet test --verbosity detailed

# With coverage
dotnet test --collect:"XPlat Code Coverage"

---

Advanced Usage

Complex Navigation Hierarchies

// Entity model with deep navigation
public class Order
{
    public Customer Customer { get; set; }
    public List<OrderItem> Items { get; set; }
}

public class Customer
{
    public Address Address { get; set; }
    public Company Company { get; set; }
}

public class Company
{
    public Address HeadquartersAddress { get; set; }
}

// Query with multi-level navigation
var query = new QueryDefinition
{
    From = "orders",
    Where = FilterDefinitionExtensions.And(
        FilterDefinitionExtensions.Equal("Customer.Address.City", "Seattle"),
        FilterDefinitionExtensions.Equal("Customer.Company.HeadquartersAddress.State", "WA"),
        FilterDefinitionExtensions.GreaterThan("Items.Count", 0)
    )
};

// Strategy auto-includes:
// .Include("Customer")
// .Include("Customer.Address")
// .Include("Customer.Company")
// .Include("Customer.Company.HeadquartersAddress")
// .Include("Items")

var result = await strategy.ExecuteAsync(query);

foreach (var order in result.Data)
{
    // All navigations loaded - zero N+1 queries
    Console.WriteLine($"Order data: {order["Id"]}");
}

Conditional Projections

// DTO with conditional logic
public class OrderDetailDto
{
    public int OrderId { get; set; }
    public string Status { get; set; } = string.Empty;
    public string StatusDisplay { get; set; } = string.Empty;
    public decimal TotalAmount { get; set; }
    public string TotalDisplay { get; set; } = string.Empty;
}

// Projection with conditionals
projectionRegistry.Register<Order, OrderDetailDto>(
    o => new OrderDetailDto
    {
        OrderId = o.Id,
        Status = o.Status,
        StatusDisplay = o.Status == "Active" ? "✓ Active" :
                       o.Status == "Pending" ? "⏳ Pending" :
                       o.Status == "Cancelled" ? "✗ Cancelled" : o.Status,
        TotalAmount = o.TotalAmount,
        TotalDisplay = o.TotalAmount > 1000 ? "High Value" :
                      o.TotalAmount > 100 ? "Medium Value" : "Low Value"
    }
);

Integration with ASP.NET Core

Controller Example:

[ApiController]
[Route("api/[controller]")]
public class OrdersController : ControllerBase
{
    private readonly EntityFrameworkExecutionStrategy _strategy;

    public OrdersController(EntityFrameworkExecutionStrategy strategy)
    {
        _strategy = strategy;
    }

    [HttpPost("query")]
    public async Task<ActionResult<IQueryResult<Dictionary<string, object>>>> Query(
        [FromBody] QueryDefinition query,
        CancellationToken ct)
    {
        // Validate query
        var validation = _strategy.Validate(query);
        if (!validation.IsValid)
        {
            return BadRequest(validation.Errors);
        }

        // Execute
        var result = await _strategy.ExecuteAsync(query);

        if (!result.Success)
        {
            return StatusCode(500, new { error = result.Error?.Message });
        }

        return Ok(result);
    }

    [HttpPost("summaries")]
    public async Task<ActionResult<IQueryResult<Dictionary<string, object>>>> QuerySummaries(
        [FromBody] QueryDefinition query,
        CancellationToken ct)
    {
        query.From = "order-summaries";

        var result = await _strategy.ExecuteAsync(query);

        if (!result.Success)
        {
            return StatusCode(500, new { error = result.Error?.Message });
        }

        return Ok(new
        {
            data = result.Data,
            totalCount = result.TotalCount,
            executionTime = result.ExecutionTime.TotalMilliseconds,
            metadata = result.AdditionalMetadata
        });
    }
}

---

IQueryableBuilder

Overview

The IQueryableBuilder service builds a deferred IQueryable<T> from a data source with filters applied, without executing the query. This enables custom downstream composition (aggregations, projections, joins) while leveraging the library's source resolution, filter parsing, and expression compilation.

When to Use

Use IQueryableBuilder when you need to:

• Apply the library's filter parsing but perform custom aggregations downstream
• Build dashboard queries where filtering happens before aggregation
• Compose additional LINQ operations after the filter is applied
• Work with the IQueryable<T> directly instead of getting materialized results

Interface

public interface IQueryableBuilder
{
    // Build with shorthand filter string
    Task<IQueryable<T>> BuildQueryableAsync<T>(
        string sourceName,
        string filter,
        IReadOnlyList<OrderingDefinition>? orderBy = null,
        CancellationToken cancellationToken = default) where T : class;

    // Build with structured FilterDefinition
    Task<IQueryable<T>> BuildQueryableAsync<T>(
        string sourceName,
        FilterDefinition? filter,
        IReadOnlyList<OrderingDefinition>? orderBy = null,
        CancellationToken cancellationToken = default) where T : class;

    // Build with combined filters (shorthand AND structured)
    Task<IQueryable<T>> BuildQueryableAsync<T>(
        string sourceName,
        string? shorthandFilter,
        FilterDefinition? structuredFilter,
        IReadOnlyList<OrderingDefinition>? orderBy = null,
        CancellationToken cancellationToken = default) where T : class;
}

Basic Usage

With Structured Filter:

// Inject IQueryableBuilder
private readonly IQueryableBuilder _queryableBuilder;

// Build a filtered queryable
var filter = FilterDefinitionExtensions.And(
    FilterDefinitionExtensions.Equal("Status", "Pending"),
    FilterDefinitionExtensions.GreaterThan("TotalAmount", 100m)
);

var queryable = await _queryableBuilder.BuildQueryableAsync<Order>(
    sourceName: "orders",
    filter: filter,
    cancellationToken: ct);

// The queryable is NOT executed yet - apply custom operations
var dashboard = await queryable
    .GroupBy(o => o.CustomerId)
    .Select(g => new
    {
        CustomerId = g.Key,
        OrderCount = g.Count(),
        TotalAmount = g.Sum(o => o.TotalAmount)
    })
    .ToListAsync(ct);

With Shorthand Filter (requires IQueryParser registration):

var queryable = await _queryableBuilder.BuildQueryableAsync<Order>(
    sourceName: "orders",
    filter: "status:pending AND total>100",
    cancellationToken: ct);

// Apply custom aggregation downstream
var results = await queryable
    .GroupBy(o => o.CustomerId)
    .Select(g => new { CustomerId = g.Key, Count = g.Count() })
    .ToListAsync(ct);

Dashboard Aggregation Example

A common use case is building filtered dashboard metrics:

public class DashboardService
{
    private readonly IQueryableBuilder _queryableBuilder;

    public async Task<DashboardMetrics> GetMetricsAsync(
        FilterDefinition? userFilter,
        CancellationToken ct)
    {
        // Build filtered queryable (not executed yet)
        var ordersQueryable = await _queryableBuilder.BuildQueryableAsync<Order>(
            "orders",
            filter: userFilter,
            cancellationToken: ct);

        // Apply multiple aggregations to the same filtered queryable
        var totalOrders = await ordersQueryable.CountAsync(ct);
        var totalRevenue = await ordersQueryable.SumAsync(o => o.TotalAmount, ct);
        var avgOrderValue = await ordersQueryable.AverageAsync(o => o.TotalAmount, ct);

        // Complex aggregation with grouping
        var byStatus = await ordersQueryable
            .GroupBy(o => o.Status)
            .Select(g => new StatusBreakdown
            {
                Status = g.Key,
                Count = g.Count(),
                Revenue = g.Sum(o => o.TotalAmount)
            })
            .ToListAsync(ct);

        return new DashboardMetrics
        {
            TotalOrders = totalOrders,
            TotalRevenue = totalRevenue,
            AverageOrderValue = avgOrderValue,
            ByStatus = byStatus
        };
    }
}

Combining Filters

When using the combined filter overload, shorthand and structured filters are combined with AND logic:

// Structured filter as baseline (e.g., tenant isolation)
var baseFilter = FilterDefinitionExtensions.Equal("TenantId", currentTenantId);

// Shorthand from user search (refinement)
var userSearch = "status:active AND price>50";

var queryable = await _queryableBuilder.BuildQueryableAsync<Product>(
    sourceName: "products",
    shorthandFilter: userSearch,       // User's search criteria
    structuredFilter: baseFilter,      // Always-applied baseline
    cancellationToken: ct);

// Effective filter: TenantId = currentTenantId AND status = "active" AND price > 50

Empty Shorthand Parse Results

When a shorthand filter parses to an empty result (i.e., QueryParseResult.IsEmpty is true, due to UnmatchedBareTermBehavior.EmptyResult mode), IQueryableBuilder returns an empty IQueryable<T> via dbSet.Where(_ => false) instead of throwing. This matches the API endpoint behavior where unmatched queries return zero results rather than errors.

// If the shorthand parser yields an empty parse result,
// the returned queryable produces zero rows when materialized.
var queryable = await _queryableBuilder.BuildQueryableAsync<Order>(
    "orders",
    filter: "unmatchedterm",
    cancellationToken: ct);

var count = await queryable.CountAsync(ct); // 0

@ Function Macro Resolution

IQueryableBuilder resolves @ function macros (e.g., @today, @this_week, @last_30_days) in structured FilterDefinition values before building LINQ expressions. This requires IFilterFunctionResolver and IFilterFunctionRegistry to be registered — both are automatically available when AddFilterShorthandParser() is called.

// Works with @ macros in structured filters
var filter = FilterDefinitionExtensions.Equal("CreatedDate", "@this_week");

var queryable = await _queryableBuilder.BuildQueryableAsync<Order>(
    "orders",
    filter: filter,
    cancellationToken: ct);
// @this_week is resolved to a BETWEEN range before expression compilation

Shorthand filters resolve macros at parse time and do not depend on this — the resolution step only applies to structured filters containing @ string values. When the resolver services are not registered, @ values pass through unresolved and fail at type coercion (the same behavior as any invalid filter value).

Feature values such as @me are resolved against the per-request IFunctionExecutionFeatureCollector (an optional constructor dependency). When the collector is wired, QueryableBuilder materializes features once per BuildQueryableAsync call and propagates them via FilterResolutionContext.Features so feature-bound functions resolve consistently with the endpoint pipeline. When the collector is not wired (e.g., standalone scripts or unit tests that bypass DI), the feature list defaults to empty and feature-bound @ functions resolve to their no-feature behavior.

Row-Level Filter

QueryableBuilder always applies the source's row-level filter when the source declares one. IRowFilterInjector is a mandatory constructor dependency — there is no opt-out, and the QueryableBuilder constructor throws ArgumentNullException if it is not supplied. AddQueryBuilderEntityFramework() registers the canonical RowFilterInjector (scoped) as part of its own service set, so calling that helper alone is sufficient to wire QueryableBuilder with row-filter support. AddCodeFirstSchemaRegistry() and AddQueryBuilderEndpoints() also register the injector via TryAdd, so any combination of helpers leaves a working registration.

The injector AND-composes DataSourceDefinition.RowFilter output (delegate or typed) into the outer query and every reachable subquery before @-resolution runs, so injected references (e.g., a row filter that AND-composes OwnerId = @me) resolve in the same pass as the caller's WHERE.

// Standalone usage WITHOUT calling AddQueryBuilderEntityFramework — the EF helper
// already wires these registrations, so this snippet only matters when a host
// composes services manually.
services.AddScoped<IRowFilterInjector, RowFilterInjector>();
services.AddScoped<IUserAccessor, MyUserAccessor>();
services.AddScoped<IFunctionExecutionFeatureProvider, MyUserContextProvider>();

// The injector resolves IUserAccessor, ISchemaRegistry, and ILogger via
// constructor injection. Callers invoke InjectAsync(query, ct) — the outer
// source is resolved internally from query.From.

When the source has no RowFilter, the injector pass is a no-op and BuildQueryableAsync proceeds without overhead.

Error Handling

Scenario	Exception	Error Code
Source not found	CoreException	ResourceNotFound
Type T doesn't match source entity	CoreException	InvalidConfiguration
No DbContext registered	CoreException	ResourceNotFound
Shorthand parser not registered	CoreException	InvalidConfiguration
IRowFilterInjector not registered (host bypassed AddQueryBuilderEntityFramework)	ArgumentNullException (from QueryableBuilder constructor at DI resolution)	n/a
Row-filter factory throws or row-filter walker hits a cyclical configuration	CoreException (from injector)	InvalidConfiguration
Row-filter walker hits a subquery referencing an unregistered source	CoreException (from injector)	ResourceNotFound

Example:

try
{
    var queryable = await _queryableBuilder.BuildQueryableAsync<Order>(
        "nonexistent-source",
        filter: null);
}
catch (CoreException ex) when (ex.ErrorCode == CoreErrorCode.ResourceNotFound)
{
    _logger.LogWarning("Source not found: {Message}", ex.Message);
    // Handle gracefully
}

Key Differences from ExecuteAsync

Aspect	IQueryableBuilder	IExecutionStrategy.ExecuteAsync
Returns	IQueryable<T> (deferred)	IQueryResult<T> (materialized)
Execution	Not executed	Fully executed
Custom Operations	Can apply GroupBy, custom Select, etc.	Results already materialized
Pagination	Apply downstream	Applied during execution
Use Case	Custom aggregations, complex queries	Standard CRUD queries

Registration

IQueryableBuilder is automatically registered when you call AddQueryBuilderEntityFramework():

services.AddQueryBuilderEntityFramework();
// IQueryableBuilder is now available via DI

Lifetime: Scoped (aligns with DbContext lifecycle)

---

Integration

With QueryBuilder.Core

Implements: IExecutionStrategy

Uses:

• QueryDefinition - Universal query format
• FilterDefinition - Filter expressions
• DataSourceDefinition - Source reference
• OrderingDefinition - Sorting specification
• DataSourceType.EntityFramework (enum value: 9)
• IExecutionContext - Execution hints
• IQueryResult<T> - Standardized results

With QueryBuilder.Expressions

Uses:

• ILinqQueryBuilder - Converts QueryDefinition to LINQ IQueryable
• IExpressionBuilder<T> - Compiles filter expressions with L1/L2 caching
• PropertyPathAnalyzer - Analyzes navigation paths

Benefits:

• Expression caching avoids recompilation for repeated query patterns
• Shared cache across all strategies

With QueryBuilder.SchemaRegistry

Schema Registry Fields (for EntityFramework sources):

public class DataSourceRegistry
{
    public string SourceName { get; set; }              // "orders"
    public DataSourceType Type { get; set; }            // EntityFramework
    public string? DbContextTypeName { get; set; }      // "App.Data.AppDbContext, App"
    public string? EntityTypeName { get; set; }         // "App.Domain.Order, App"
    public string? SourceEntityTypeName { get; set; }   // For projections
    public string DisplayName { get; set; }
    public string? Description { get; set; }
    public string CreatedBy { get; set; }
    public DateTimeOffset CreatedAt { get; set; }
}

Resolution Flow:

1. User queries "orders" source
2. Strategy looks up DataSourceRegistry by SourceName
3. Extracts DbContextTypeName
4. Resolves DbContext from DI via reflection
5. Queries DbContext.Set<T>() from the resolved context

---

Best Practices

1. Always Register Data Sources

// ❌ Bad - Query unregistered source
var query = new QueryDefinition
{
    From = "orders"
};
// Throws: CoreException - source not found

// ✅ Good - Register first
await registrationService.RegisterEntityFrameworkSourceAsync(
    sourceName: "orders",
    dbContextType: typeof(AppDbContext),
    entityType: typeof(Order),
    displayName: "Sales Orders",
    createdBy: "admin"
);

2. Use DTOs for API Responses

// ❌ Bad - Return all fields without projection
[HttpGet]
public async Task<ActionResult> GetOrders()
{
    var result = await _strategy.ExecuteAsync(query);
    return Ok(result.Data);  // May include sensitive data
}

// ✅ Good - Use Select to control response shape
[HttpGet]
public async Task<ActionResult> GetOrders()
{
    query.From = "order-summaries";  // Use registered projection source
    var result = await _strategy.ExecuteAsync(query);
    return Ok(result.Data);  // Controlled shape, API-friendly
}

3. Leverage Auto-Detection

// ❌ Manual includes (verbose, error-prone)
projectionRegistry.Register<Order, OrderDto>(
    o => new OrderDto { CustomerName = o.Customer.Name },
    includeNavigations: new[] { "Customer" }  // Explicit, redundant
);

// ✅ Auto-detection (concise, less error-prone)
projectionRegistry.Register<Order, OrderDto>(
    o => new OrderDto { CustomerName = o.Customer.Name }
    // Auto-detects "Customer" navigation
);

4. Use Pagination

// ❌ Bad - Fetch all records
var query = new QueryDefinition
{
    From = "orders"
};

// ✅ Good - Paginate
var query = new QueryDefinition
{
    From = "orders",
    OrderBy = new[] { new OrderingDefinition { Field = "OrderDate", Direction = SortDirection.Descending } },
    Offset = (pageNumber - 1) * pageSize,
    Limit = pageSize
};

5. Monitor Performance Metadata

var result = await strategy.ExecuteAsync(query);

// Log performance metrics
_logger.LogInformation(
    "Query executed: {BuildTime}ms build, {ExecTime}ms DB, {Total}ms total",
    ((TimeSpan)result.AdditionalMetadata["BuildTime"]).TotalMilliseconds,
    ((TimeSpan)result.AdditionalMetadata["ExecutionTime"]).TotalMilliseconds,
    result.ExecutionTime.TotalMilliseconds
);

// Alert on slow queries
if (result.ExecutionTime.TotalMilliseconds > 1000)
{
    _logger.LogWarning("Slow query detected: {Sql}", result.AdditionalMetadata["GeneratedSql"]);
}

6. Handle Errors Gracefully

var result = await strategy.ExecuteAsync(query);

if (!result.Success)
{
    if (result.Error is FeatureNotSupportedException featureEx)
    {
        return BadRequest($"Unsupported feature: {featureEx.Message}");
    }
    else if (result.Error is CoreException coreEx)
    {
        _logger.LogError(coreEx, "Query execution failed");
        return StatusCode(500, "Internal server error");
    }
    else
    {
        _logger.LogError(result.Error, "Unexpected error");
        return StatusCode(500, "Unexpected error");
    }
}

return Ok(result.Data);

7. Test with In-Memory Database

public class OrderTests
{
    private readonly DbContextOptions<AppDbContext> _options;

    public OrderTests()
    {
        _options = new DbContextOptionsBuilder<AppDbContext>()
            .UseInMemoryDatabase(databaseName: "TestDb")
            .Options;
    }

    [Fact]
    public async Task ExecuteAsync_ShouldFilterOrders()
    {
        // Arrange
        using var context = new AppDbContext(_options);
        context.Orders.Add(new Order { Status = "Active" });
        await context.SaveChangesAsync();

        var strategy = new EntityFrameworkExecutionStrategy(
            linqQueryBuilder,
            logger,
            serviceProvider,
            schemaRegistry,
            projectionRegistry
        );

        var query = new QueryDefinition
        {
            Where = FilterDefinitionExtensions.Equal("Status", "Active")
        };

        // Act
        var result = await strategy.ExecuteAsync(query);

        // Assert
        Assert.True(result.Success);
        Assert.Single(result.Data);
    }
}

---

Comparison with Other Strategies

EntityFramework vs InMemory

Aspect	EntityFramework	InMemory
Data Source	EF Core DbContext (database)	IDataSourceProvider (any in-memory)
Query Execution	LINQ-to-Entities → SQL	LINQ-to-Objects
Operators	Standard SQL operators	All operators including regex, fuzzy
Performance	Database I/O latency	In-memory (faster for small datasets)
Scalability	Database-limited	Memory-limited
Best For	Database-backed entities	Caches, APIs, files, regex queries

When to Choose Each Strategy

Choose EntityFramework when:

• ✅ Using Entity Framework Core in your application
• ✅ Need database-agnostic queries
• ✅ Have complex navigation property hierarchies
• ✅ Want automatic DTO projections
• ✅ Query across multiple DbContext instances

Choose InMemory when:

• ✅ Data from non-database sources (caches, APIs)
• ✅ Need regex or fuzzy matching
• ✅ Small to medium datasets in memory
• ✅ Rapid prototyping with mock data

---

Limitations

Current Limitations

1. Fuzzy Matching Not Supported:

// ❌ Not Supported
var query = new QueryDefinition
{
    Where = new FilterDefinition
    {
        Field = "CustomerName",
        Operator = FilterOperator.Fuzzy,
        Value = "John"
    }
};

await strategy.ExecuteAsync(query);
// Throws: FeatureNotSupportedException
// Message: "Fuzzy matching cannot be translated to SQL. Use InMemory strategy."

Workaround: Use InMemory strategy for fuzzy matching.

2. Regex Matching Not Supported:

// ❌ Not Supported
var query = new QueryDefinition
{
    Where = new FilterDefinition
    {
        Field = "Email",
        Operator = FilterOperator.Regex,
        Value = @"^admin@.*\.com$"
    }
};

await strategy.ExecuteAsync(query);
// Throws: FeatureNotSupportedException
// Message: "Regex matching cannot be translated to SQL. Use InMemory strategy."

SQL Server and most databases don't have native regex support. While some providers (PostgreSQL) support regex via custom extensions, cross-database compatibility requires excluding this feature.

Workarounds:

• Use InMemory strategy for regex matching
• Use LIKE patterns via wildcard syntax: email:*@example.com → LIKE '%@example.com'
• Create CLR functions in SQL Server for complex pattern matching

3. Explicit JOINs Not Supported:

// ❌ Not Supported
var query = new QueryDefinition
{
    Joins = new List<JoinDefinition>
    {
        new() { Type = JoinType.Inner, Table = "Customers", /* ... */ }
    }
};

// Validation throws: "JOIN operations not supported. Use navigation properties instead."

Workaround: Use navigation properties - strategy auto-includes them.

3. Aggregation Queries (GROUP BY, HAVING):

A query with a non-empty GroupBy routes to ExecuteWithAggregation, which projects the grouped result via AggregationExpressionBuilder. The projection is classifier-driven: AggregationExpressionBuilder dispatches on the GroupedSelectTerm results from GroupedSelectClassifier (in QueryBuilder.SchemaRegistry) to emit the group key, COUNT(*), and per-column aggregates. Bare aggregate keywords (count, sum, avg, average, min, max) are recognized directly from the request Select — no aggregate function objects are required in the projection.

For aggregation queries, Limit, Offset, and OrderBy operate on the resulting groups, not the source rows: the WHERE-filtered set is grouped in full (SELECT ... WHERE ... GROUP BY ...), then the groups are ordered and paged in memory. A page limit caps the number of groups returned, never the rows feeding each group's COUNT/SUM.

// ✅ Simple GROUP BY with COUNT
var query = new QueryDefinition
{
    From = "employees",
    Select = new SelectionDictionary
    {
        ["Department"] = true,
        ["count"] = true
    },
    GroupBy = ["Department"]
};

var result = await strategy.ExecuteAsync(query);
// Returns: [{ "Department": "Engineering", "count": 5 }, { "Department": "Sales", "count": 4 }, ...]

Navigational GROUP BY (Scalar Navigation):

// GROUP BY a scalar navigation property: group orders by their customer's city
var query = new QueryDefinition
{
    From = "orders",
    GroupBy = ["Customer.City"],
    Select = new SelectionDictionary
    {
        ["Customer.City"] = true,
        ["count"] = true
    }
};

var result = await strategy.ExecuteAsync(query);
// SQL equivalent: SELECT c.City, COUNT(*) FROM Orders o JOIN Customers c ON ... GROUP BY c.City
// Returns: [{ "Customer.City": "Seattle", "count": 12 }, { "Customer.City": "Portland", "count": 8 }, ...]

Navigational GROUP BY (Collection Navigation):

// GROUP BY a collection navigation property: group customers by their orders' status
var query = new QueryDefinition
{
    From = "customers",
    GroupBy = ["Orders.Status"],
    Select = new SelectionDictionary
    {
        ["Orders.Status"] = true,
        ["count"] = true
    }
};

var result = await strategy.ExecuteAsync(query);
// SQL equivalent: SELECT o.Status, COUNT(*) FROM Customers c JOIN Orders o ON ... GROUP BY o.Status
// Returns: [{ "Orders.Status": "Shipped", "count": 15 }, { "Orders.Status": "Pending", "count": 3 }, ...]

Collection GROUP BY uses SQL-native semantics: the query operates on flattened joined rows. Entities with empty collections are excluded (INNER JOIN behavior).

Navigational GROUP BY with Filter Splitting:

// Mixed WHERE filters are automatically split: root filters apply pre-SelectMany, element filters apply post-SelectMany
var query = new QueryDefinition
{
    From = "customers",
    GroupBy = ["Orders.Status"],
    Select = new SelectionDictionary
    {
        ["Orders.Status"] = true,
        ["count"] = true
    },
    Where = new FilterDefinition
    {
        LogicalOperator = LogicalOperator.And,
        Expressions =
        [
            new FilterDefinition { Field = "City", Operator = FilterOperator.Equal, Value = "Seattle" },
            new FilterDefinition { Field = "Orders.TotalAmount", Operator = FilterOperator.GreaterThan, Value = 100m }
        ]
    }
};

// City filter applied to customers (pre-SelectMany), TotalAmount filter applied to orders (post-SelectMany)

Multiple Aggregates:

// ✅ Supported - COUNT + SUM + AVG + MIN + MAX
var query = new QueryDefinition
{
    From = "employees",
    Select = new SelectionDictionary
    {
        ["Department"] = true,
        ["count"] = true,
        ["Salary"] = new SelectionConfig
        {
            Select = new SelectionDictionary
            {
                ["avg"] = true,
                ["sum"] = true
            }
        }
    },
    GroupBy = ["Department"]
};

HAVING Clause (Post-Aggregation Filtering):

// ✅ Supported - Filter with COUNT
var query = new QueryDefinition
{
    From = "employees",
    Select = new SelectionDictionary
    {
        ["Department"] = true,
        ["count"] = true
    },
    GroupBy = ["Department"],
    Having = new FilterDefinition
    {
        Function = new FunctionDefinition { Name = "count" }, // Explicit functions only (NO alias resolution)
        Operator = FilterOperator.GreaterThan,
        Value = 10
    }
};

// Returns only departments with > 10 employees

// ✅ Supported - Filter with AVG, SUM, MIN, MAX
var salaryQuery = new QueryDefinition
{
    From = "employees",
    Select = new SelectionDictionary
    {
        ["Department"] = true,
        ["Salary"] = new SelectionConfig
        {
            Select = new SelectionDictionary
            {
                ["avg"] = true
            }
        }
    },
    GroupBy = ["Department"],
    Having = new FilterDefinition
    {
        Function = new FunctionDefinition { Name = "avg" },
        Field = "Salary",      // Field to aggregate
        Operator = FilterOperator.GreaterThanOrEqual,
        Value = 75000
    }
};

// Returns departments where average salary >= $75,000

Supported HAVING Aggregates:

• @count() - Row count
• @sum(field) - Sum of values
• @avg(field) / @average(field) - Average of values
• @min(field) - Minimum value
• @max(field) - Maximum value

Limitations:

• Single GROUP BY field only (no multi-field grouping)
• SELECT is required for aggregation queries (no implicit count projection)
• HAVING uses explicit functions (e.g., @count() > 10) - no alias resolution
• 5 basic aggregates: COUNT, SUM, AVG, MIN, MAX (no COUNT DISTINCT)
• No computed fields in GROUP BY
• Aggregation ordering and pagination are applied in-memory after query execution (dictionary projections cannot be translated to SQL ORDER BY)
• Navigational GROUP BY supports single collection traversal only (e.g., Orders.Status is valid, Orders.Items.Quantity is not)
• Navigational GROUP BY with collection paths uses INNER JOIN semantics — entities with empty collections are excluded
• Mixed OR filters across root and collection field boundaries are rejected (AND is required for filter splitting)

Known Issues

1. Type Mismatch Errors:

Issue: Querying with wrong type for projection source
Impact: Runtime error instead of compile-time error
Workaround: Ensure type matches registered projection

2. DbContext Not Found:

Issue: DbContext not registered in DI or wrong type in Schema Registry
Impact: Query fails with "No DbContext registered"
Workaround: Verify DI registration and Schema Registry metadata

3. Navigation Property Not Configured:

Issue: Navigation property exists in filter but not in EF Core model
Impact: InvalidOperationException during Include()
Workaround: Configure navigation in DbContext.OnModelCreating()

Planned Enhancements

Advanced Aggregations

• Multi-field GROUP BY
• COUNT DISTINCT
• Computed fields in GROUP BY

Advanced Query Features

• Subqueries in filters
• Common Table Expressions (CTEs)
• Multi-field DISTINCT (single-field DISTINCT with projected queries is supported)

Optimizations

• Query plan caching
• Compiled query support
• Batch operations

---

Contributing

Contributions are welcome! Please:

1. Follow existing code style and patterns
2. Add comprehensive tests for new features
3. Update documentation (README, XML comments)
4. Ensure all tests pass
5. Consider EF Core compatibility

License

MIT License - See LICENSE file for details

---

Need Help?

• 📖 Core Documentation
• 📖 Expressions Documentation
• 📖 Schema Registry Documentation
• 📖 InMemory Documentation
• 🐛 Report Issues
• 💬 Discussions