ToolBox.EntityFramework 2.4.0

ToolBox.EntityFramework.Filters

A lightweight, high-performance library for dynamic filtering, sorting, and pagination with Entity Framework Core.

Features

✨ Dynamic Filtering - Build complex filter expressions with AND/OR logic
🔄 Multi-Column Sorting - Sort by multiple properties with direction control
📄 Smart Pagination - Efficient server-side pagination with rich metadata
⚡ Performance Optimized - Reflection caching and AsNoTracking() support
🎯 FluentValidation Ready - Built-in validators included
🧪 Type-Safe - Expression trees that translate directly to SQL
🔧 ASP.NET Core Integration - Extension methods and DI registration

Quick Start

Basic Pagination

var request = new PageRequest(pageNumber: 1, pageSize: 20);
var result = await dbContext.Products.ToPageAsync(request);

Console.WriteLine($"Page {result.PageNumber}/{result.TotalPages}");
Console.WriteLine($"Total Items: {result.TotalItems}");

With Sorting

var request = new PageRequest(1, 20)
{
    Sorts = new[]
    {
        new SortDescriptor { PropertyName = "Name", Direction = SortDirection.Ascending },
        new SortDescriptor { PropertyName = "CreatedDate", Direction = SortDirection.Descending }
    }
};

var result = await dbContext.Products.ToPageAsync(request);

With Filtering

var request = new PageRequest(1, 20)
{
    Filters = new[]
    {
        new FilterGroup
        {
            Filters = new[]
            {
                new ExpressionFilter 
                { 
                    PropertyName = "Price", 
                    ValueString = "50", 
                    Comparison = Comparison.GreaterThan 
                },
                new ExpressionFilter 
                { 
                    PropertyName = "Category", 
                    ValueString = "Electronics", 
                    Comparison = Comparison.Equal 
                }
            },
            FilterAssociation = FilterAssociation.And
        }
    }
};

var result = await dbContext.Products.ToPageAsync(request);

Complete Example

var request = new PageRequest(1, 20)
{
    Filters = new[]
    {
        new FilterGroup
        {
            Filters = new[]
            {
                new ExpressionFilter { PropertyName = "Price", ValueString = "100", Comparison = Comparison.GreaterThan },
                new ExpressionFilter { PropertyName = "InStock", ValueString = "true", Comparison = Comparison.Equal }
            },
            FilterAssociation = FilterAssociation.And
        }
    },
    Sorts = new[]
    {
        new SortDescriptor { PropertyName = "Name", Direction = SortDirection.Ascending }
    }
};

var result = await dbContext.Products.ToPageAsync(request);

Supported Comparisons

ASP.NET Core Integration

Program.cs

using ToolBox.EntityFramework.Filters;

var builder = WebApplication.CreateBuilder(args);

// Register validators and services
builder.Services.AddToolBoxFilters();

var app = builder.Build();

Controller Usage

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

    public ProductsController(AppDbContext context)
    {
        _context = context;
    }

    [HttpPost("search")]
    public async Task<ActionResult<PageOf<Product>>> Search(
        [FromBody] PageRequest request,
        CancellationToken cancellationToken)
    {
        // Automatic validation with FluentValidation
        var result = await _context.Products
            .ToPageAsync(request, cancellationToken);
            
        return Ok(result);
    }
}

API Request Example

POST /api/products/search
{
  "pageNumber": 1,
  "pageSize": 20,
  "filters": [
    {
      "filters": [
        {
          "propertyName": "Price",
          "valueString": "50",
          "comparison": 1,
          "filterAssociation": 0
        },
        {
          "propertyName": "Category",
          "valueString": "Electronics",
          "comparison": 0,
          "filterAssociation": 0
        }
      ],
      "filterAssociation": 0
    }
  ],
  "sorts": [
    {
      "propertyName": "Name",
      "direction": 0
    }
  ]
}

API Response Structure

{
  "pageNumber": 1,
  "pageSize": 20,
  "totalItems": 156,
  "totalPages": 8,
  "hasPreviousPage": false,
  "hasNextPage": true,
  "items": [
    {
      "id": 1,
      "name": "Product A",
      "price": 99.99,
      "category": "Electronics"
    }
  ]
}

Advanced Features

Complex Filter Logic

Combine multiple filter groups with AND/OR logic:

var filters = new[]
{
    new FilterGroup
    {
        // (Price > 100 AND Category = "Electronics")
        Filters = new[]
        {
            new ExpressionFilter { PropertyName = "Price", ValueString = "100", Comparison = Comparison.GreaterThan },
            new ExpressionFilter { PropertyName = "Category", ValueString = "Electronics", Comparison = Comparison.Equal }
        },
        FilterAssociation = FilterAssociation.And
    },
    new FilterGroup
    {
        // OR (FeaturedProduct = true)
        Filters = new[]
        {
            new ExpressionFilter { PropertyName = "FeaturedProduct", ValueString = "true", Comparison = Comparison.Equal }
        },
        FilterAssociation = FilterAssociation.Or
    }
};

SQL Output:

WHERE (Price > 100 AND Category = 'Electronics') OR (FeaturedProduct = 1)
ORDER BY Name ASC
OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY

Case-Insensitive Property Names

Property names are matched case-insensitively:

// All of these work
new SortDescriptor { PropertyName = "Name" }
new SortDescriptor { PropertyName = "name" }
new SortDescriptor { PropertyName = "NAME" }

Validation

Built-in FluentValidation validators ensure:

• Page number ≥ 1
• Page size between 1-100 (configurable)
• Valid property names
• Non-empty filter values
• Valid comparison operators

Performance

• Reflection Caching: PropertyInfo lookups cached in ConcurrentDictionary
• Single Query: Filters and sorts translated to single SQL query
• No Tracking: AsNoTracking() applied automatically
• Expression Trees: Direct translation to SQL (no in-memory filtering)

Configuration

Custom Page Size Limits

public class PageRequest
{
    private const int MaxPageSize = 100; // Modify this constant
    // ...
}

Clear Reflection Cache

// Useful for testing or hot reload scenarios
SortBuilder.ClearCache();
ExpressionBuilder.ClearCache();

API Reference

PageRequest

PageOf<T>

Extension Methods

// IQueryable<T> extensions
Task<PageOf<T>> ToPageAsync(PageRequest request, CancellationToken cancellationToken = default)
Task<PageOf<T>> ToPageAsync(int pageNumber, int pageSize, ...)

Requirements

• .NET 10.0+
• Entity Framework Core 9.0+
• FluentValidation 11.0+ (optional, for validation)

Error Handling

The library provides detailed error messages:

// Property not found
Property 'InvalidName' not found on type 'Product'. Available properties: Id, Name, Price, Category

// Invalid type for string operation
Contains comparison is only supported for string properties. Property 'Price' is of type 'Decimal'.

// Empty property name
Sort property name cannot be empty.

Best Practices

1. Use AsNoTracking() - Already applied automatically for read queries
2. Index filtered/sorted columns - Add database indexes on frequently queried properties
3. Limit page size - Default max is 100 items per page
4. Validate input - Use provided FluentValidation validators
5. Cache property lookups - Handled automatically via ConcurrentDictionary

Performance Tips

// ✅ Good - Single query
var result = await dbContext.Products
    .Include(p => p.Category)
    .ToPageAsync(request);

// ❌ Bad - Multiple queries
var products = await dbContext.Products.ToListAsync(); // Loads everything
var filtered = products.Where(...); // In-memory filtering