DKNet.EfCore.Specifications 10.1.6

DKNet.EfCore.Specifications

A powerful and flexible specification pattern implementation for Entity Framework Core with dynamic LINQ query support powered by System.Linq.Dynamic.Core and LinqKit.

Features

• 🎯 Specification Pattern - Build reusable, composable query logic with Specification<TEntity>
• ⚡ Dynamic LINQ Queries - Runtime query construction using System.Linq.Dynamic.Core
• 🔗 Fluent API - Chainable DynamicAnd() and DynamicOr() extension methods
• 🛠️ Type-Safe Operations - Strongly-typed filter operations with automatic type handling
• 📊 LinqKit Integration - Seamlessly combine static and dynamic predicates with PredicateBuilder
• 🔄 Property Name Normalization - Supports camelCase, snake_case, kebab-case, and PascalCase
• 🎭 Model Projections - ModelSpecification<TEntity, TModel> for DTO/projection scenarios
• 🌊 Async Streaming - Page-based async enumeration for large result sets
• ✅ Null-Safe - Proper handling of nullable properties and null values in SQL
• 🔍 Enum Validation - Automatic enum type validation and conversion

Installation

dotnet add package DKNet.EfCore.Specifications

Quick Start

1. Basic Specification Usage

public class ActivePersonsSpec : Specification<Person>
{
    public ActivePersonsSpec()
    {
        // Static filter
        WithFilter(p => p.IsActive && !p.IsDeleted);
        
        // Include related entities
        AddInclude(p => p.Address);
        
        // Add ordering
        AddOrderBy(p => p.Name);
    }
}

// Usage with repository
var spec = new ActivePersonsSpec();
var persons = await repository.ToListAsync(spec);

2. Dynamic Predicates with DynamicAnd/DynamicOr

Build dynamic queries at runtime using fluent extension methods:

public class PersonSearchSpec : Specification<Person>
{
    public PersonSearchSpec(int? minAge, string? nameContains, string? department)
    {
        // Start with base predicate
        var predicate = PredicateBuilder.New<Person>(p => !p.IsDeleted);
        
        // Add dynamic filters conditionally
        if (minAge.HasValue)
            predicate = predicate.DynamicAnd("Age", DynamicOperations.GreaterThanOrEqual, minAge);
            
        if (!string.IsNullOrEmpty(nameContains))
            predicate = predicate.DynamicAnd("Name", DynamicOperations.Contains, nameContains);
            
        if (!string.IsNullOrEmpty(department))
            predicate = predicate.DynamicOr("Department.Name", DynamicOperations.Equal, department);
        
        WithFilter(predicate);
    }
}

// Usage
var spec = new PersonSearchSpec(minAge: 18, nameContains: "John", department: null);
var results = await repository.ToListAsync(spec);

3. Property Name Normalization

Property names are automatically normalized to PascalCase, supporting multiple naming conventions:

var predicate = PredicateBuilder.New<Employee>()
    // All of these are equivalent and normalize to "FirstName"
    .DynamicAnd("firstName", DynamicOperations.Equal, "John")      // camelCase
    .DynamicAnd("first_name", DynamicOperations.Equal, "John")     // snake_case
    .DynamicAnd("first-name", DynamicOperations.Equal, "John")     // kebab-case
    .DynamicAnd("FirstName", DynamicOperations.Equal, "John");     // PascalCase

// Nested properties also supported
predicate = predicate.DynamicAnd("address.city", DynamicOperations.Equal, "New York");
// Normalizes to: Address.City

4. Model Specifications for Projections

Use ModelSpecification<TEntity, TModel> for scenarios involving DTOs or projections:

public class EmployeeListSpec : ModelSpecification<Employee, EmployeeDto>
{
    public EmployeeListSpec(string? departmentFilter)
    {
        var predicate = PredicateBuilder.New<Employee>(e => e.IsActive);
        
        if (!string.IsNullOrEmpty(departmentFilter))
            predicate = predicate.DynamicAnd("Department.Name", DynamicOperations.Equal, departmentFilter);
        
        WithFilter(predicate);
        AddOrderBy(e => e.LastName);
        AddOrderBy(e => e.FirstName);
    }
}

// Usage with automatic projection (using Mapster or AutoMapper)
var spec = new EmployeeListSpec("Engineering");
var dtos = await repository.ToListAsync<Employee, EmployeeDto>(spec);

Available Operations

The DynamicOperations enum supports the following operations:

* Auto-Conversion: For non-string types (int, enum, bool, double, etc.), string operations are automatically converted to equality operations.

Null Value Handling

The library properly handles null values in SQL queries:

// NULL equality check
predicate = predicate.DynamicAnd("MiddleName", DynamicOperations.Equal, null);
// SQL: WHERE [MiddleName] IS NULL

// NULL inequality check
predicate = predicate.DynamicAnd("MiddleName", DynamicOperations.NotEqual, null);
// SQL: WHERE [MiddleName] IS NOT NULL

Enum Validation

Enum properties are automatically validated. Only Equal and NotEqual operations are supported for enums:

public enum Status { Active, Inactive, Pending }

// Valid enum operations
predicate = predicate.DynamicAnd("Status", DynamicOperations.Equal, Status.Active);
predicate = predicate.DynamicAnd("Status", DynamicOperations.NotEqual, Status.Inactive);

// Invalid enum values are ignored (predicate remains unchanged)
predicate = predicate.DynamicAnd("Status", DynamicOperations.Equal, "InvalidValue");

// Contains/StartsWith/EndsWith are auto-converted to Equal for enums
predicate = predicate.DynamicAnd("Status", DynamicOperations.Contains, Status.Active);
// Automatically becomes: Equal operation

Advanced Scenarios

Multi-Field Search with OR Logic

public class ProductSearchSpec : Specification<Product>
{
    public ProductSearchSpec(string searchTerm)
    {
        var predicate = PredicateBuilder.New<Product>(true);
        
        // Search across multiple fields using OR
        predicate = predicate
            .DynamicOr("Name", DynamicOperations.Contains, searchTerm)
            .DynamicOr("Description", DynamicOperations.Contains, searchTerm)
            .DynamicOr("SKU", DynamicOperations.Equal, searchTerm);
        
        WithFilter(predicate);
    }
}

Complex AND/OR Combinations

public class EmployeeFilterSpec : Specification<Employee>
{
    public EmployeeFilterSpec(string? department, decimal? minSalary, bool includeInactive)
    {
        var predicate = PredicateBuilder.New<Employee>(true);
        
        // Department filter (OR across multiple departments)
        if (!string.IsNullOrEmpty(department))
        {
            var deptPredicate = PredicateBuilder.New<Employee>(false);
            foreach (var dept in department.Split(','))
            {
                deptPredicate = deptPredicate.DynamicOr("Department.Name", DynamicOperations.Equal, dept.Trim());
            }
            predicate = predicate.And(deptPredicate);
        }
        
        // Salary filter (AND)
        if (minSalary.HasValue)
            predicate = predicate.DynamicAnd("Salary", DynamicOperations.GreaterThanOrEqual, minSalary);
        
        // Active status (AND)
        if (!includeInactive)
            predicate = predicate.DynamicAnd("IsActive", DynamicOperations.Equal, true);
        
        WithFilter(predicate);
    }
}

API Controller with Dynamic Queries

[HttpGet]
public async Task<IActionResult> SearchEmployees(
    [FromQuery] string? search,
    [FromQuery] string? department,
    [FromQuery] string? orderBy,
    [FromQuery] int page = 1,
    [FromQuery] int pageSize = 20)
{
    var spec = new ModelSpecification<Employee, EmployeeDto>();
    
    var predicate = PredicateBuilder.New<Employee>(e => !e.IsDeleted);
    
    // Dynamic search
    if (!string.IsNullOrEmpty(search))
    {
        predicate = predicate
            .DynamicOr("FirstName", DynamicOperations.Contains, search)
            .DynamicOr("LastName", DynamicOperations.Contains, search)
            .DynamicOr("Email", DynamicOperations.Contains, search);
    }
    
    // Department filter
    if (!string.IsNullOrEmpty(department))
        predicate = predicate.DynamicAnd("Department.Name", DynamicOperations.Equal, department);
    
    spec.WithFilter(predicate);
    
    // Dynamic ordering (supports camelCase, snake_case, etc.)
    if (!string.IsNullOrEmpty(orderBy))
        spec.AddOrderBy(orderBy, ListSortDirection.Ascending);
    else
        spec.AddOrderBy(e => e.LastName);
    
    var pagedResults = await _repository.ToPagedListAsync<Employee, EmployeeDto>(spec, page, pageSize);
    
    return Ok(new
    {
        items = pagedResults,
        totalCount = pagedResults.TotalItemCount,
        pageCount = pagedResults.PageCount
    });
}

Nested Property Filtering

var spec = new Specification<Order>();
var predicate = PredicateBuilder.New<Order>(true)
    // Nested property access
    .DynamicAnd("Customer.Address.City", DynamicOperations.Equal, "New York")
    .DynamicAnd("Customer.Address.ZipCode", DynamicOperations.StartsWith, "100")
    
    // Multi-level navigation
    .DynamicAnd("OrderItems.Product.Category.Name", DynamicOperations.Equal, "Electronics")
    
    // Combination with static predicates
    .And(o => o.OrderDate >= DateTime.Today.AddDays(-30));

spec.WithFilter(predicate);

Async Streaming for Large Result Sets

var spec = new ModelSpecification<Product, ProductDto>();
spec.WithFilter(p => p.IsActive);
spec.AddOrderBy(p => p.Id);

// Stream results page-by-page
await foreach (var product in _repository.PageAsync<Product, ProductDto>(spec))
{
    // Process each product without loading entire result set into memory
    await ProcessProductAsync(product);
}

5. Keyset (Cursor-Based) Pagination

Keyset pagination is significantly faster than OFFSET/FETCH for large tables because it uses an index seek instead of scanning and skipping rows. The technique avoids the "cold start" problem of OFFSET-based queries, which must count and discard all preceding rows on every page request.

Single-key (integer ID):

// First page
var firstPage = await context.Orders
    .OrderBy(o => o.Id)
    .Take(pageSize)
    .ToListAsync();

// Next page after the last seen Id
var nextPage = await context.Orders
    .OrderBy(o => o.Id)
    .AfterKeyset(o => o.Id, lastSeenId)
    .Take(pageSize)
    .ToListAsync();
// Generates: WHERE Id > @lastSeenId  →  efficient index seek

Composite key (tie-breaking with a secondary column):

// Next page after the last seen (CreatedDate, Id) pair
var nextPage = await context.Orders
    .OrderBy(o => o.CreatedDate)
    .ThenBy(o => o.Id)
    .AfterKeyset(o => o.CreatedDate, o => o.Id, lastDate, lastId)
    .Take(pageSize)
    .ToListAsync();
// Generates: WHERE CreatedDate > @date OR (CreatedDate = @date AND Id > @id)
// Semantically equivalent to: (CreatedDate, Id) > (@date, @id)

Backward navigation (previous page):

var prevPage = await context.Orders
    .OrderByDescending(o => o.CreatedDate)
    .ThenByDescending(o => o.Id)
    .BeforeKeyset(o => o.CreatedDate, o => o.Id, firstDate, firstId)
    .Take(pageSize)
    .ToListAsync();
// Generates: WHERE CreatedDate < @date OR (CreatedDate = @date AND Id < @id)

Using with the specification repository:

// Single-key page via IRepositorySpec
var spec = new OrdersOrderedByIdSpec();
var page = await repository.ToKeysetPageAsync(spec, o => o.Id, lastSeenId, pageSize: 20);

// Composite-key page via IRepositorySpec
var spec2 = new OrdersOrderedByDateSpec();
var page2 = await repository.ToKeysetPageAsync(
    spec2,
    o => o.CreatedDate, o => o.Id,
    lastDate, lastId,
    pageSize: 20);

Full pagination loop with keyset:

var cursor = 0; // start before all Ids
var pageSize = 100;

while (true)
{
    var page = await context.Products
        .OrderBy(p => p.Id)
        .AfterKeyset(p => p.Id, cursor)
        .Take(pageSize)
        .ToListAsync();

    if (page.Count == 0) break;

    foreach (var product in page)
        await ProcessAsync(product);

    cursor = page[^1].Id; // advance cursor to last item
}

Arbitrary-arity paging with HasPrevious/HasNext (ToKeysetPageAsync on IQueryable<T>):

AfterKeyset/BeforeKeyset cover one or two key columns and leave ordering to the caller. For an arbitrary number of columns — each with its own ascending/descending direction — and boundary flags telling the caller whether further pages exist, use ToKeysetPageAsync instead. It owns both the ordering and the cursor filter (built via MR.EntityFrameworkCore.KeysetPagination), so do not chain it after OrderBy.

// Page merchants by country asc, revenue desc, identifier asc
var page = await context.Merchants.ToKeysetPageAsync(
    b => b.Ascending(m => m.Country).Descending(m => m.Revenue).Ascending(m => m.Id),
    pageSize: 20,
    direction: KeysetPaginationDirection.Forward,
    reference: lastSeenMerchant); // last row of the previous page, or null for the first page

foreach (var merchant in page.Items) { /* ... */ }

if (page.HasNext) { /* show a "next page" control */ }
if (page.HasPrevious) { /* show a "previous page" control */ }

Paging backward from a reference row returns the page immediately before it, still in ascending declared order:

var prevPage = await context.Merchants.ToKeysetPageAsync(
    b => b.Ascending(m => m.Country).Descending(m => m.Revenue).Ascending(m => m.Id),
    pageSize: 20,
    direction: KeysetPaginationDirection.Backward,
    reference: firstSeenMerchant);

reference only needs to be an object whose property names match the configured keyset columns — it does not have to be a TEntity instance. Costs three round trips per call: the page query itself, plus one each for HasPrevious/HasNext.

Repository Extensions

The library provides rich extension methods for IRepositorySpec:

Entity-Only Operations

// Count
int count = await repository.CountAsync(spec);

// Any
bool hasResults = await repository.AnyAsync(spec);

// First
Employee employee = await repository.FirstAsync(spec);

// First or default
Employee? maybeEmployee = await repository.FirstOrDefaultAsync(spec);

// List
IList<Employee> employees = await repository.ToListAsync(spec);

// Paged list
IPagedList<Employee> pagedEmployees = await repository.ToPagedListAsync(spec, pageNumber: 1, pageSize: 20);

// Async enumeration
await foreach (var emp in repository.PageAsync(spec))
{
    // Process
}

// Get raw query (useful for debugging)
IQueryable<Employee> query = repository.Query(spec);
string sql = query.ToQueryString();

Model Projection Operations

// First or default with projection
EmployeeDto? dto = await repository.FirstOrDefaultAsync<Employee, EmployeeDto>(spec);

// List with projection
IList<EmployeeDto> dtos = await repository.ToListAsync<Employee, EmployeeDto>(spec);

// Paged list with projection
IPagedList<EmployeeDto> pagedDtos = await repository.ToPagedListAsync<Employee, EmployeeDto>(spec, 1, 20);

// Async enumeration with projection
await foreach (var dto in repository.PageAsync<Employee, EmployeeDto>(spec))
{
    // Process
}

// Get raw projected query
IQueryable<EmployeeDto> query = repository.Query<Employee, EmployeeDto>(spec);

Best Practices

1. Reusability - Create named specification classes for common query patterns
2. Composition - Build complex queries by combining simple predicates using And() and Or()
3. Type Safety - The library automatically handles type conversions and validates enum values
4. Null Safety - Null values are handled correctly (translates to IS NULL / IS NOT NULL in SQL)
5. Property Naming - Use any naming convention you prefer; it will be normalized to PascalCase
6. Performance - Use ModelSpecification<TEntity, TModel> with projections to reduce data transfer
7. Large Result Sets - Use PageAsync() for streaming or ToPagedListAsync() for pagination
8. High-Volume Pagination - Use AfterKeyset() / BeforeKeyset() for cursor-based pagination on large tables; it is far more efficient than Skip/Take because it avoids scanning discarded rows
9. Debugging - Use .ToQueryString() on the query to see generated SQL

Type-Specific Behavior

String Properties

• All operations supported: Equal, NotEqual, Contains, NotContains, StartsWith, EndsWith, comparison operators
• Null values handled correctly with IS NULL / IS NOT NULL

Numeric Properties (int, long, decimal, double, etc.)

• Comparison operations: Equal, NotEqual, GreaterThan, GreaterThanOrEqual, LessThan, LessThanOrEqual
• String operations (Contains, etc.) auto-converted to Equal

Enum Properties

• Only Equal and NotEqual supported
• Invalid enum values are ignored (no exception thrown)
• Automatic enum validation and conversion
• String operations auto-converted to Equal

Boolean Properties

• Equal and NotEqual operations
• String operations auto-converted to Equal

Nullable Types

• Full support for nullable reference types and nullable value types
• Null comparisons translate to proper SQL (IS NULL / IS NOT NULL)

Requirements

• .NET 9.0 or higher
• Entity Framework Core 9.0 or higher
• LinqKit.Microsoft.EntityFrameworkCore 8.1.0+
• System.Linq.Dynamic.Core 1.4.0+
• X.PagedList 9.2.0+ (for pagination)

License

Licensed under the MIT License. See LICENSE in the project root for license information.

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

Support

For issues, questions, or feature requests, please open an issue on the GitHub repository.