DynamicQuery Dynamic Query

DynamicQuery provides a set of lightweight, type-safe building blocks for generating LINQ filtering and sorting expressions at runtime based on DTO/query objects.

Namespace

using MiCake.Util.Query.Dynamic;

Core Components

Filter - Single-Field Filter

var filter = Filter.Create(
    propertyName: "Name",
    values: new List<FilterValue>
    {
        FilterValue.Create("张三", ValueOperatorType.Contains)
    },
    valuesJoinType: FilterJoinType.Or
);

Sort - Sorting

var sort = new Sort
{
    PropertyName = "CreateTime",
    Ascending = false  // Descending
};

FilterValue - Filter Value

var filterValue = FilterValue.Create(
    value: "test",
    operatorType: ValueOperatorType.Contains
);

Operator Types (ValueOperatorType)

Operator	Description	Example
Equal	Equal to	Name == "John"
NotEqual	Not equal to	Status != 0
Contains	Contains	Name.Contains("phone")
StartsWith	Starts with	Name.StartsWith("Apple")
EndsWith	Ends with	Email.EndsWith("@example.com")
GreaterThan	Greater than	Price > 100
LessThan	Less than	Stock < 10
GreaterThanOrEqual	Greater than or equal to	Age >= 18
LessThanOrEqual	Less than or equal to	Price <= 1000

Automatically Generating Filters with Attributes

Defining a Query Object

public class ProductQueryDto : IDynamicQueryObj
{
    [DynamicFilter(OperatorType = ValueOperatorType.Contains)]
    public string? Name { get; set; }

    [DynamicFilter(PropertyName = "Price", OperatorType = ValueOperatorType.GreaterThanOrEqual)]
    public decimal? MinPrice { get; set; }

    [DynamicFilter(PropertyName = "Price", OperatorType = ValueOperatorType.LessThanOrEqual)]
    public decimal? MaxPrice { get; set; }

    [DynamicFilter(OperatorType = ValueOperatorType.Equal)]
    public int? CategoryId { get; set; }
}

Generating Filter Conditions

var queryDto = new ProductQueryDto
{
    Name = "phone",
    MinPrice = 1000,
    MaxPrice = 5000,
    CategoryId = 5
};

// Automatically generate filter conditions
FilterGroup filterGroup = queryDto.GenerateFilterGroup();

// The generated expression is equivalent to:
// p => p.Name.Contains("phone")
//      && p.Price >= 1000
//      && p.Price <= 5000
//      && p.CategoryId == 5

Applying to IQueryable

var query = _dbContext.Products.AsQueryable();

// Apply dynamic filters
query = query.ApplyFilters(filterGroup);

// Apply sorting
query = query.ApplySorting(new Sort
{
    PropertyName = "Price",
    Ascending = true
});

// Execute the query
var products = await query.ToListAsync();

FilterGroup and CompositeFilterGroup

FilterGroup - Filter Group

Combines multiple filter conditions:

var group = new FilterGroup
{
    Filters = new List<Filter>
    {
        Filter.Create("Name", new List<FilterValue>
        {
            FilterValue.Create("phone", ValueOperatorType.Contains)
        }),
        Filter.Create("Price", new List<FilterValue>
        {
            FilterValue.Create(1000m, ValueOperatorType.GreaterThanOrEqual)
        })
    },
    FiltersJoinType = FilterJoinType.And  // AND join
};

// Apply the filter group
var query = _dbContext.Products.Filter(group);

CompositeFilterGroup - Composite Filter Group

Combines multiple FilterGroups:

var composite = new CompositeFilterGroup
{
    FilterGroups = new List<FilterGroup>
    {
        new FilterGroup { /* First group of conditions */ },
        new FilterGroup { /* Second group of conditions */ }
    },
    FilterGroupsJoinType = FilterJoinType.Or  // OR join between groups
};

// Apply the composite filter
var query = _dbContext.Products.Filter(composite);

Nested Properties

Dot notation is supported for accessing nested properties:

public class OrderQueryDto : IDynamicQueryObj
{
    [DynamicFilter(PropertyName = "Customer.Name", OperatorType = ValueOperatorType.Contains)]
    public string? CustomerName { get; set; }

    [DynamicFilter(PropertyName = "Address.City", OperatorType = ValueOperatorType.Equal)]
    public string? City { get; set; }
}

// Generated expression:
// o => o.Customer.Name.Contains("John") && o.Address.City == "Beijing"

Class-Level Configuration

Use [DynamicFilterJoin] to configure the class-level join type:

[DynamicFilterJoin(JoinType = FilterJoinType.And)]  // Default is AND
public class ProductQuery : IDynamicQueryObj
{
    [DynamicFilter(OperatorType = ValueOperatorType.Contains)]
    public string? Name { get; set; }

    [DynamicFilter(OperatorType = ValueOperatorType.Equal)]
    public int? CategoryId { get; set; }
}

// Generates: Name.Contains("...") AND CategoryId == ...

Usage Examples

Using in a Web API

[ApiController]
[Route("api/[controller]")]
public class ProductController : ControllerBase
{
    private readonly IRepository<Product, int> _repository;

    [HttpGet]
    public async Task<List<Product>> Search([FromQuery] ProductQueryDto query)
    {
        var filterGroup = query.GenerateFilterGroup();

        var products = await _repository.Query()
            .Filter(filterGroup)
            .Sort(new Sort { PropertyName = "CreateTime", Ascending = false })
            .ToListAsync();

        return products;
    }
}

Complex Queries

public class OrderQueryDto : IDynamicQueryObj
{
    // Fuzzy query on the order number
    [DynamicFilter(OperatorType = ValueOperatorType.Contains)]
    public string? OrderNumber { get; set; }

    // Order status (multi-select)
    [DynamicFilter(OperatorType = ValueOperatorType.In)]
    public List<OrderStatus>? Statuses { get; set; }

    // Amount range
    [DynamicFilter(PropertyName = "TotalAmount", OperatorType = ValueOperatorType.GreaterThanOrEqual)]
    public decimal? MinAmount { get; set; }

    [DynamicFilter(PropertyName = "TotalAmount", OperatorType = ValueOperatorType.LessThanOrEqual)]
    public decimal? MaxAmount { get; set; }

    // Date range
    [DynamicFilter(PropertyName = "CreateTime", OperatorType = ValueOperatorType.GreaterThanOrEqual)]
    public DateTime? StartDate { get; set; }

    [DynamicFilter(PropertyName = "CreateTime", OperatorType = ValueOperatorType.LessThanOrEqual)]
    public DateTime? EndDate { get; set; }

    // Customer name
    [DynamicFilter(PropertyName = "Customer.Name", OperatorType = ValueOperatorType.Contains)]
    public string? CustomerName { get; set; }
}

Dynamic Sorting

[HttpGet]
public async Task<List<Product>> GetProducts(
    [FromQuery] ProductQueryDto query,
    [FromQuery] string? sortBy = "CreateTime",
    [FromQuery] bool ascending = false)
{
    var filterGroup = query.GenerateFilterGroup();

    var products = await _repository.Query()
        .Filter(filterGroup)
        .Sort(new Sort { PropertyName = sortBy, Ascending = ascending })
        .ToListAsync();

    return products;
}

Multi-Field Sorting

var sorts = new List<Sort>
{
    new Sort { PropertyName = "Priority", Ascending = false },
    new Sort { PropertyName = "CreateTime", Ascending = false }
};

var query = _repository.Query()
    .Filter(filterGroup)
    .Sort(sorts);

FilterExtensions Extension Methods

Method	Description
Filter(Filter)	Applies a single filter
Filter(IEnumerable<Filter>)	Applies multiple filters
Filter(FilterGroup)	Applies a filter group
Filter(CompositeFilterGroup)	Applies a composite filter group
GetFilterExpression<T>()	Gets the filter expression

SortingExtensions Extension Methods

Method	Description
Sort(Sort)	Applies a single sort
Sort(IEnumerable<Sort>)	Applies multiple sorts

Best Practices

1. Use IDynamicQueryObj

// ✅ Correct: implement the interface to use the extension methods
public class ProductQuery : IDynamicQueryObj
{
    [DynamicFilter(OperatorType = ValueOperatorType.Contains)]
    public string? Name { get; set; }
}

// ❌ Wrong: doesn't implement the interface
public class ProductQuery
{
    public string? Name { get; set; }
}

2. Null Values Are Skipped Automatically

var query = new ProductQuery
{
    Name = "phone",  // generates a filter condition
    CategoryId = null,  // skipped automatically
    MinPrice = 0  // ⚠️ Note: 0 is not skipped
};

// Only the Name filter condition is generated
var filterGroup = query.GenerateFilterGroup();

3. Use the In Operator

public class OrderQuery : IDynamicQueryObj
{
    [DynamicFilter(OperatorType = ValueOperatorType.In)]
    public List<OrderStatus>? Statuses { get; set; }
}

// Usage
var query = new OrderQuery
{
    Statuses = new List<OrderStatus>
    {
        OrderStatus.Pending,
        OrderStatus.Processing
    }
};

// Generates: o => new[] { 0, 1 }.Contains(o.Status)

4. Handle Nullable Types

// ✅ Correct: use nullable types
public class ProductQuery : IDynamicQueryObj
{
    [DynamicFilter(OperatorType = ValueOperatorType.Equal)]
    public int? CategoryId { get; set; }  // Nullable
}

// ❌ Wrong: non-nullable types have default value issues
public class ProductQuery : IDynamicQueryObj
{
    [DynamicFilter(OperatorType = ValueOperatorType.Equal)]
    public int CategoryId { get; set; }  // The default value 0 generates a filter condition
}

5. Combine Filter and Sort

// ✅ Correct: filter first, then sort
var result = await _repository.Query()
    .Filter(filterGroup)
    .Sort(sort)
    .Skip(skip)
    .Take(pageSize)
    .ToListAsync();

// ❌ Not recommended: sort first, then filter
var result = await _repository.Query()
    .Sort(sort)
    .Filter(filterGroup)  // may affect the sorting result
    .ToListAsync();

Implementation Details and Notes

1. Null value skipping: null values are skipped automatically, no filter condition is generated
2. Type conversion: uses TypeDescriptor.GetConverter or System.Convert.ChangeType
3. Conversion failure: throws an InvalidOperationException
4. In operator: automatically converted to a strongly-typed List
5. Property access restriction: only public get properties are allowed
6. Thread safety: generated expressions are thread-safe

Debugging Suggestions

View the Generated Expression

var filterGroup = query.GenerateFilterGroup();
var expression = _repository.Query().GetFilterExpression();

// Print the expression
Console.WriteLine(expression?.ToString());
// Output: p => (p.Name.Contains("phone") AndAlso (p.Price >= 1000))

Test Boundary Values

// Test an empty string
var query1 = new ProductQuery { Name = "" };  // will be skipped

// Test the value 0
var query2 = new ProductQuery { MinPrice = 0 };  // will not be skipped

// Test an empty collection
var query3 = new OrderQuery { Statuses = new List<OrderStatus>() };  // will be skipped

Important Notes

1. Null value handling: null, empty strings, and empty collections are skipped automatically
2. Type safety: compile-time type checking avoids runtime errors
3. EF Core compatibility: the generated expressions can be translated to SQL by EF Core
4. Nested properties: dot notation is supported for accessing nested properties
5. Thread safety: can be used safely in multi-threaded environments