Plainquire

Seamless filtering, sorting, and paging for .NET Standard 2.1. Fully customizable. Model binding support and integration into Swagger UI.

Demo

Application: https://www.plainquire.com/demo

Swagger UI: https://www.plainquire.com/api

Usage for ASP.NET Core

1. Install NuGet packages

dotnet add package Plainquire.Filter
dotnet add package Plainquire.Filter.Mvc
dotnet add package Plainquire.Filter.Swashbuckle

2. Register services

using Plainquire.Filter.Mvc;
using Plainquire.Filter.Swashbuckle;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllers().AddFilterSupport();
builder.Services.AddSwaggerGen(options => options.AddFilterSupport());

3. Setup entity

using Plainquire.Filter;

[EntityFilter(Prefix = "")]
public class Freelancer
{
    public Guid Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

4. Create HTTP endpoint

using Plainquire.Filter;

[HttpGet]
public IEnumerable<Freelancer> GetFreelancers([FromQuery] EntityFilter<Freelancer> filter)
{
    var freelancers = GetFreelancersFromDatabase();
    var filteredFreelancers = freelancers.Where(filter);
    return filteredFreelancers;
}

5. Send HTTP request

BASE_URL=https://www.plainquire.com/api/Freelancer
curl -O "$BASE_URL/GetFreelancers?firstName=Joe"

6. Results in SQL statement

SELECT *
FROM "Freelancer"
WHERE instr(upper("FirstName"), 'JOE') > 0

Usage for non-web applications

1. Install NuGet package

dotnet add package Plainquire.Filter

2. Setup entity

See setup entity from above.

3. Create repository

public IEnumerable<Freelancer> GetFreelancers()
{
    var filter = new EntityFilter<Freelancer>().Add(x => x.FirstName, "~Joe");
    
    var freelancers = GetFreelancersFromDatabase();
    var filteredFreelancers = freelancers.Where(filter);
    return filteredFreelancers;
}

Features
Syntax
- Filter syntax
- Sort syntax
Filter entities
Sort entities
Page Entities
Upgrade from FilterExpressionCreator

Features

Filtering, sorting and pagination for ASP.NET Core
Customizable syntax
Support for Swagger / OpenUI and code generators via Swashbuckle.AspNetCore
Support for Entity Framework an other ORM mapper using IQueryable<T>
Support for In-memory lists / arrays via IEnumerable<T>
Binding for HTTP query parameters
Natural language date/time interpretation (e.g. yesterday, last week Tuesday, ...)
Filters, sorts and pages are serializable, e.g. to persist user defined filters
Customizable expressions via interceptors

Syntax

Filter syntax

Syntax reference

The default filter micro syntax uses operator/value pairs separated by ,, ;, or |

Separated values combined with logical OR. To combine values with logical AND, specify the filter multiple times.

Micro syntax	Operator	Description
	`Default`	Selects operator `Contains` for string values; `EqualCaseInsensitive` for others.
`~`	`Contains`	Hits when the filtered property contains the filter value
`^`	`StartsWith`	Hits when the filtered property starts with the filter value
`$`	`EndsWith`	Hits when the filtered property ends with the filter value
`=`	`EqualCaseInsensitive`	Hits when the filtered property equals the filter value (case-insensitive)
`==`	`EqualCaseSensitive`	Hits when the filtered property equals the filter value (case-sensitive)
`!`	`NotEqual`	Negates the `Default` operator. Operators other than `Default` cannot be negated (currently)
`<`	`LessThan`	Hits when the filtered property is less than the filter value
`<=`	`LessThanOrEqual`	Hits when the filtered property is less than or equal to the filter value
`>`	`GreaterThan`	Hits when the filtered property is greater than the filter value
`>=`	`GreaterThanOrEqual`	Hits when the filtered property is greater than or equals the filter value
`ISNULL`	`IsNull`	Hits when the filtered property is `null`
`NOTNULL`	`NotNull`	Hits when the filtered property is not `null`

Escape filter values

The backslash (\) is the default escape character. To search for a backslash, use a double backslash (\\). Escape filter operator characters and separators to include them in searches.

HTTP query samples

Query parameter	Description
`&gender==female`	Gender equals `female` (case insensitive)
`&gender==male,=female`	Gender equals `male` OR `female` (case insensitive)
`&gender===female`	Gender equals `female` (case sensitive)
`&gender=~male`	Gender contains `male` (fetches `female` too)
`&tag=ISNULL`	Tag is `null`
`&tag==`	Tag equals `""`
`&tag===A;B`	Tag equals `=A;B` (case insensitive due escaped syntax)
`&size=<100`	Size is lower than `100`
`&size=>100&size=<200`	Size is between `100` and `200`
`&created=>two-days-ago`	Created within the last `2 days`
`&created=yesterday`	Created `yesterday`
`&created=>2020-03`	Created after `Sunday, March 1, 2020`
`&created=2020`	Crated in the year `2020`

Sort syntax

Syntax reference

The sort micro syntax includes a property name with an optional sort direction marker (e.g., customer-asc). In an HTTP query parameter, you can use a comma-separated list of properties (e.g., &orderBy=customer,number-desc).

Direction	Position	Values
ascending	prefix	`+`, `asc-`, `asc`
ascending	postfix	`+`, `-asc`, `asc`
descending	prefix	`-`, `~`, `desc-`, `dsc-`, `desc` , `dsc`
descending	postfix	`-`, `~`, `-desc`, `-dsc`, `desc`, `dsc`

HTTP query samples

Query parameter	Description
`&orderBy=lastName`	Sort by `lastName` ascending
`&orderBy=lastName-`	Sort by `lastName` descending
`&orderBy=lastName,-firstName`	Sort by `lastName` ascending, than by `firstName` descending
`&orderBy=lastName.length`	Sort by `length of lastName` ascending

Page Syntax

Syntax reference

Micro syntax	Description
`page`	The page number to get
`pageSize`	The page size to use

HTTP query samples

Query parameter	Description
`&page=2&pageSize=3`	Takes the 2nd page by a page size of 3

Filter entities

Basic usage

Install NuGet packages

Package Manager : Install-Package Plainquire.Filter
CLI : dotnet add package Plainquire.Filter

Bind filter from query-parameters

using Plainquire.Filter;

[HttpGet]
public Task<List<Order>> GetOrders([FromQuery] EntityFilter<Order> order)
{
    return dbContext.Orders.Where(filter).ToList();
}

Create filter from code

using Plainquire.Filter;

var orders = new[] {
   new Order { Customer = "Joe Miller", Number = 100 },
   new Order { Customer = "Joe Smith", Number = 200 },
   new Order { Customer = "Joe Smith", Number = 300 },
};

// Create filter
var filter = new EntityFilter<Order>()
   .Add(x => x.Customer, "Joe")
   .Add(x => x.Number, FilterOperator.GreaterThan, 250);

// Print filter
Console.WriteLine(filter);
// Output: x => (((x.Customer != null) AndAlso x.Customer.ToUpper().Contains("JOE")) AndAlso (x.Number > 250))

// Filter using queryables (e.g. Entity Framework)
var filteredOrders = dbContext.Orders.Where(filter).ToList();

// Filter using LINQ
var filteredOrders = orders.Where(filter).ToList();

Configure filters

Generated filter expressions can be configured via FilterConfiguration.

Create configuration

using Plainquire.Filter;

// Parse filter values using german locale (e.g. "5,5" => 5.5f).
var configuration = new FilterConfiguration { CultureInfo = new CultureInfo("de-DE") };

Provide configuration

// For MVC model binding via dependency injection
services.Configure<FilterConfiguration>(c => c.IgnoreParseExceptions = true);

// Via constructor
new EntityFilter<Order>(configuration);

// Via static default
FilterConfiguration.Default

Configuration reference

Configuration	Description
`CultureName`	Culture used for parsing in `languagecode2-country` - `regioncode2` format (e.g., "en-US").
`UseConditionalAccess`	Controls the use of conditional access to navigation properties
`IgnoreParseExceptions`	Fallback to `x => true` if any exception occurs during value parsing
`FilterOperatorMap`	Map between micro syntax and operator. Micro syntax is case-sensitive
`BooleanMap`	Map between string and boolean. Strings are case-insensitive
`ValueSeparatorChars`	Characters used to split values in micro syntax
`EscapeCharacter`	Escape character used in micro syntax

Filter by special values

Filter by `== null` / `!= null`

// For 'Customer is null'
filter.Add(x => x.Customer, FilterOperator.IsNull);
// Output: x => (x.Customer == null)

// For 'Customer is not null'
filter.Add(x => x.Customer, FilterOperator.NotNull);
// Output: x => (x.Customer != null)

// via query parameter
var getOrdersUrl = "/GetOrders?customer=ISNULL"
var getOrdersUrl = "/GetOrders?customer=NOTNULL"

While filtered for == null / != null, (accidently) given values are ignored:

filter.Add(x => x.Customer, FilterOperator.NotNull, "values", "are", "ignored");

Filter by `""` / `string.Empty`

// For 'Customer == ""'
filter.Add(x => x.Customer, string.Empty);
// Output: x => (x.Customer == "")

// For 'Customer is not null'
filter.Add(x => x.Customer, FilterOperator.NotEqual, string.Empty);
// Output: x => (x.Customer != "")

// via query parameter
var getOrdersUrl = "/GetOrders?customer="

Filter by Date/Time

Date/Time values can be given in the form of a fault-tolerant round-trip date/time pattern

// Date
filter.Add(x => x.Created, ">2020/01/01");
// Output: x => (x.Created > 01.01.2020 00:00:00)

// Date/Time
filter.Add(x => x.Created, ">2020-01-01-12-30");
// Output: x => (x.Created > 01.01.2020 12:30:00)

// Partial values are supported too
filter.Add(x => x.Created, "2020-01");
// Output: x => ((x.Created >= 01.01.2020 00:00:00) AndAlso (x.Created < 01.02.2020 00:00:00))

Filter by Date/Time with natural language

Thanks to nChronic.Core natural language for date/time is supported.

// This
filter.Add(x => x.Created, ">yesterday");

// works as well as
filter.Add(x => x.Created, ">3-months-ago-saturday-at-5-pm");

Details can be found here: https://github.com/mojombo/chronic

Filter by enum

Enum values can be filtered by its name as well as by it's numeric representation.

// Equals by name
filter.Add(x => x.Gender, "=divers");
// Output: x => (x.Gender == Divers)

// Equals by numeric value
filter.Add(x => x.Gender, "=1");
// Output: x => (Convert(x.Gender, Int64) == 1)

// Contains, value is expanded
filter.Add(x => x.Gender, "~male");
// Output: x => ((x.Gender == Male) OrElse (x.Gender == Female))

enum Gender { Divers, Male, Female }

Filter by numbers

Filter for numbers support contains operator but may be less performant.

// Equals
filter.Add(x => x.Number, "1");
// Output: x => (x.Number == 1)

// Contains
filter.Add(x => x.Number, "~1");
// Output: x => x.Number.ToString().ToUpper().Contains("1")

Logical Operators

Add/Replace filter using logical OR

Multiple values given to one call are combined using conditional OR.

// Customer contains `Joe` || `Doe`

var filter = new EntityFilter<Order>();

// via operator
filter.Add(x => x.Customer, FilterOperator.Contains, "Joe", "Doe");
filter.Replace(x => x.Customer, FilterOperator.Contains, "Joe", "Doe");

// via syntax
filter.Add(x => x.Customer, "~Joe,~Doe");
filter.Replace(x => x.Customer, "~Joe,~Doe");

// via query parameter
var getOrdersUrl = "/GetOrders?customer=~Joe,~Doe"

Add/Replace filter using logical AND

Multiple calls are combined using conditional AND.

// Customer contains `Joe` && `Doe`

var filter = new EntityFilter<Order>();

// via operator
filter
    .Add(x => x.Customer, FilterOperator.Contains, "Joe")
    .Add(x => x.Customer, FilterOperator.Contains, "Doe");

// via syntax
filter
    .Add(x => x.Customer, "~Joe")
    .Add(x => x.Customer, "~Doe");

// via query parameter
var getOrdersUrl = "/GetOrders?customer=~Joe&customer=~Doe"

Nested filters

Nested objects are filtered directly (x => x.Address.City == "Berlin")

Nested lists are filtered using .Any() (x => x.Items.Any(item => (item.Article == "Laptop")))

// Create filters
var addressFilter = new EntityFilter<Address>()
    .Add(x => x.City, "==Berlin");

var itemFilter = new EntityFilter<OrderItem>()
    .Add(x => x.Article, "==Laptop");

var orderFilter = new EntityFilter<Order>()
    .AddNested(x => x.Address, addressFilter)
    .AddNested(x => x.Items, itemFilter);

// Print filter
Console.WriteLine(orderFilter);
// Output:
// x => ((x.Address != null) AndAlso (x.Address.City == "Berlin"))
// x => ((x.Items != null) AndAlso x.Items.Any(x => (x.Article == "Laptop")))

public class Order
{
    public int Number { get; set; }
    public string Customer { get; set; }

    public Address Address { get; set; }
    public List<OrderItem> Items { get; set; }
}

public record Address(string Street, string City);
public record OrderItem(int Position, string Article);

Retrieve syntax and filter values

var filter = new EntityFilter<Order>()
    .Add(x => x.Customer, FilterOperator.Contains, "Joe", "Doe");

// Retrive filter syntax
string filterSytax = filter.GetPropertyFilterSyntax(x => x.Customer);
// Output: ~Joe,~Doe

// Retrive filter values
ValueFilter[] filterValues = filter.GetPropertyFilterValues(x => x.Customer);
// Output:
// [{
//   "Operator": "Contains",
//   "Value": "Joe",
//   "IsEmpty": false
// }, {
//   "Operator": "Contains",
//   "Value": "Doe",
//   "IsEmpty": false
// }]

REST / MVC

To filter an entity via model binding, the entity must be marked with EntityFilterAttribute

Register model binders

Package Manager : Install-Package Plainquire.Filter.Mvc
CLI : dotnet add package Plainquire.Filter.Mvc

using Plainquire.Filter.Mvc;

// Register required stuff by calling 'AddFilterSupport()' on IMvcBuilder instance
services.AddControllers().AddFilterSupport();

Map HTTP query parameters to `EntityFilter`

With model binding enabled, REST requests can be filtered using query parameters:

using Plainquire.Filter;

var getOrdersUrl = "/GetOrders?customer==Joe&number=>4711"

[HttpGet]
public Task<List<Order>> GetOrders([FromQuery] EntityFilter<Order> filter)
{
    Console.WriteLine(filter);
    // Output:
    // x => (
    //   ((x.Customer != null) AndAlso (x.Customer.ToUpper() == "JOE"))
    //   AndAlso (x.Number > 4711)
    // )

    var queryParams = filter.ToQueryParams();
    // Output: customer==Joe&number=>4711
}

Configure model binding

By default, parameters for properties of filtered entity are named {Entity}{Property}. By default, all public non-complex properties (string, int, DateTime, ...) are recognized. Parameters can be renamed or removed using FilterAttribute and EntityFilterAttribute.

For the code below Number is not mapped anymore and Customer becomes CustomerName:

using Plainquire.Filter.Abstractions;

// Remove prefix, e.g. property 'Number' is mapped from 'number', not 'orderNumber'
[EntityFilter(Prefix = "")]
public class Order
{
     // 'Number' is removed from filter and will be ignored
    [Filter(Filterable = false)]
    public int Number { get; set; }

    // 'Customer' is mapped from query-parameter 'customerName'
    [Filter(Name = "CustomerName")]
    public string Customer { get; set; }
}

Filter sets

Multiple entity filters can be combined to a set of filters using the EntityFilterSetAttribute.

using Plainquire.Filter;
using Plainquire.Filter.Abstractions;

// Use
[HttpGet]
public Task<List<Order>> GetOrders([FromQuery] OrderFilterSet filterSet)
{
    var order = filterSet.Order;
    var orderItem = filterSet.OrderItem;
}

// Instead of
public Task<List<Order>> GetOrders([FromQuery] EntityFilter<Order> order, EntityFilter<OrderItem> orderItem) { ... }

[EntityFilterSet]
public class OrderFilterSet
{
    public EntityFilter<Order> Order { get; set; }
    public EntityFilter<OrderItem> OrderItem { get; set; }
}

Swagger / OpenAPI

Register OpenAPI support

Swagger / OpenAPI is supported when using Swashbuckle.AspNetCore.

Package Manager : Install-Package Plainquire.Filter.Swashbuckle
CLI : dotnet add package Plainquire.Filter.Swashbuckle

using Plainquire.Filter.Swashbuckle;

services.AddSwaggerGen(options =>
{
    // Register filters used to modify swagger.json
    options.AddFilterSupport();
});

Register XML documentation

To get descriptions for generated parameters from XML documentation, paths to documentation files can be provided.

services.AddSwaggerGen(options =>
{
    var filterDoc = Path.Combine(AppContext.BaseDirectory, "Plainquire.Filter.xml");
    options.AddFilterSupport(filterDoc);
    options.IncludeXmlComments(filterDoc);
});

Support for Newtonsoft.Json

By default System.Text.Json is used to serialize/convert Plainquire specific stuff. If you like to use Newtonsoft.Json you must register it:

Package Manager : Install-Package Plainquire.Filter.Mvc.Newtonsoft
CLI : dotnet add package Plainquire.Filter.Mvc.Newtonsoft

using Plainquire.Filter.Mvc.Newtonsoft;

// Register support for Newtonsoft by calling
// 'AddFilterNewtonsoftSupport()' on IMvcBuilder instance
services.AddControllers().AddFilterNewtonsoftSupport();

Interception

Creation of filter expression can be intercepted via IFilterInterceptor. While implicit conversions to Func<TEntity, bool> and Expression<Func<TEntity, bool>> exists, explicit filter conversion is required to apply an interceptor.

var filter = new EntityFilter<Order>();
var interceptor = new FilterStringsCaseInsensitiveInterceptor();
var filterExpression = filter.CreateFilter(interceptor) ?? (x => true);
var filteredList = orders.Where(filterExpression.Compile());
var filteredDb = _dbContext.Orders.Where(filterExpression);

Default interceptor

A default interceptor can be provided via static IFilterInterceptor.Default.

Sample interceptor

Interceptor to omit filter values having an empty value. Allows to omit filters added by empty query parameters (&birthday=) but prevents filtering for empty strings (&name=).

public class OmitEmptyFilterInterceptor : IFilterInterceptor
{
    public Expression<Func<TEntity, bool>>? CreatePropertyFilter<TEntity>(PropertyInfo propertyInfo, IEnumerable<ValueFilter> filters, FilterConfiguration configuration)
    {
        var nonEmptyFilters = filters.Where(ValueIsNotNullOrEmpty).ToList();

        var noFilterRequired = nonEmptyFilters.Count == 0;
        return noFilterRequired
            ? PropertyFilterExpression.EmptyFilter<TEntity>()
            : PropertyFilterExpression.CreateFilter<TEntity>(propertyInfo, nonEmptyFilters, configuration, this);
    }

    private static bool ValueIsNotNullOrEmpty(ValueFilter valueFilter)
        => !string.IsNullOrEmpty(valueFilter.Value);

    Func<DateTimeOffset> IFilterInterceptor.Now => () => DateTimeOffset.Now;
}

Advanced scenarios

Deep copy

The EntityFilter<T> class supports deep cloning by calling the Clone() method

var copy = filter.Clone();

Casting

Filters can be cast between entities, e.g. to convert them between DTOs and database models.

Properties are matched by type (check if assignable) and name (case-sensitive)

var dtoFilter = new EntityFilter<OrderDto>().Add(...);
var orderFilter = dtoFilter.Cast<Order>();

Serialization

Using `System.Text.Json`

Objects of type EntityFilter<T> can be serialized via System.Text.Json.JsonSerializer without further requirements

var json = JsonSerializer.Serialize(filter);
filter = JsonSerializer.Deserialize<EntityFilter<Order>>(json);

Using `Newtonsoft.Json`

When using Newtonsoft.Json additional converters are required

Package Manager : Install-Package Plainquire.Filter.Newtonsoft
CLI : dotnet add package Plainquire.Filter.Newtonsoft

using Plainquire.Filter.Newtonsoft;

var json = JsonConvert.SerializeObject(filter, JsonConverterExtensions.NewtonsoftConverters);
filter = JsonConvert.DeserializeObject<EntityFilter<Order>>(json, JsonConverterExtensions.NewtonsoftConverters);

Combine filter expressions

To add custom checks to a filter either call .Where(...) again

var filteredOrders = orders
    .Where(filter)
    .Where(item => item.Items.Count > 2);

or where this isn't possible combine filters with CombineWithConditionalAnd

using Plainquire.Filter.Abstractions;

var extendedFilter = new[]
    {
        filter.CreateFilter(),
        item => item.Items.Count > 2
    }
    .CombineWithConditionalAnd();

var filteredOrders = orders.Where(extendedFilter.Compile());

Sort entities

Basic usage

Install NuGet packages

Package Manager : Install-Package Plainquire.Sort
CLI : dotnet add package Plainquire.Sort

Create a sort

using Plainquire.Sort;

var orders = new[] {
   new Order { Customer = "Joe Miller", Number = 100 },
   new Order { Customer = "Joe Smith", Number = 200 },
   new Order { Customer = "Joe Smith", Number = 300 },
};

// Create sort
var sort = new EntitySort<Order>()
    .Add(x => x.Customer, SortDirection.Ascending)
    .Add(x => x.Number, SortDirection.Descending);

// Print sort
Console.WriteLine($"{orders.OrderBy(sort)}");
// Output: orders.OrderBy(x => IIF((x == null), null, x.Customer)).ThenByDescending(x => x.Number)

// Use sort with LINQ
var sortedOrders = orders.OrderBy(sort).ToList();
// Or queryables (e.g. Entity Framework)
var sortedOrders = dbContext.Orders.OrderBy(sort).ToList();

[EntityFilter]
public class Order
{
   public int Number { get; set; }
   public string Customer { get; set; }
}

Or bind sort from query-parameters

using Plainquire.Sort;

[HttpGet]
public Task<List<Order>> GetOrders([FromQuery] EntitySort<Order> sort)
{
    return dbContext.Orders.OrderBy(sort).ToList();
}

Configure sorting

Generated sort expression can be configured via SortConfiguration.

Create configuration

using Plainquire.Sort.Abstractions;

var configuration = new SortConfiguration();
configuration.AscendingPostfixes.Add("^");

Provide configuration

// For MVC model binding via dependency injection
services.Configure<SortConfiguration>(c => c.IgnoreParseExceptions = true);

// Via constructor
new EntitySort<Order>(configuration);

// Via static default
SortConfiguration.Default

Configuration reference

Configuration	Description
`AscendingPrefixes`	Prefixes used to identify an ascending sort order
`AscendingPostfixes`	Postfixes used to identify an ascending sort order
`DescendingPrefixes`	Prefixes used to identify a descending sort order
`DescendingPostfixes`	Postfixes used to identify a descending sort order
`IgnoreParseExceptions`	Fallback to `source.OrderBy(x => 0)` if any exception occurs during value parsing
`UseConditionalAccess`	Controls the use of conditional access to navigation properties (e.g. `person => person?.Name`)
`CaseInsensitivePropertyMatching`	Indicates whether to use case-insensitive property matching

Sort entities

// Order is sorted by `Address` ascending.
var sort = new EntitySort<Order>();

// via operator
sort.Add(x => x.Address, SortDirection.Ascending);

// via syntax
sort.Add("Address-asc")

// via query parameter
var getOrdersUrl = "/GetOrders?orderBy=customer-asc"

Sort nested entities

Nested objects are sorted directly (x=> x.OrderBy(order => order.Customer)). Deep property paths (e.g. order => order.Customer.Length) are supported. Methods calls (e.g. order => order.Customer.SubString(1)) are not supported for security reasons.

Nested lists cannot be sorted directly. You can create an own EntitySort for it and sort the nested list by.

// Create sort
var addressSort = new EntitySort<Address>()
    .Add(x => x.City);

// AddNested() is equivalent to adding the paths directly
var orderSort = new EntitySort<Order>()
    .AddNested(x => x.Address, addressSort);

// Is equivalent to AddNested() above
var orderSort = new EntitySort<Order>()
    .Add(x => x.Address.City, SortDirection.Ascending);

// Print sort
Console.WriteLine(orders.OrderBy(orderSort).ToString());
// Output:
// orders => orders.OrderBy(x => IIF((IIF((x == null), null, x.Address) == null), null, x.Address.City))

public class Order
{
    public int Number { get; set; }
    public string Customer { get; set; }
    public Address Address { get; set; }
}

public record Address(string Street, string City);

Retrieve syntax and sort direction

var orderSort = new EntitySort<Order>()
    .Add(x => x.Customer, SortDirection.Ascending);

// Retrive sort syntax
var syntax = orderSort.GetPropertySortSyntax(x => x.Customer);
// Output: Customer-asc

// Retrive sort direction
var direction = orderSort.GetPropertySortDirection(x => x.Customer);
// Output: Ascending

// Retrive sort expression string:
var orderExpression = orders.OrderBy(orderSort).ToString()

REST / MVC

To sort an entity via model binding, the entity must be marked with EntityFilterAttribute

Register model binders

Package Manager : Install-Package Plainquire.Sort.Mvc
CLI : dotnet add package Plainquire.Sort.Mvc

using Plainquire.Sort.Mvc;

// Register required stuff by calling 'AddSortSupport()' on IMvcBuilder instance
services.AddControllers().AddSortSupport();

Map HTTP query parameter to `EntitySort`

With model binding enabled, REST requests can be sorted using query parameter orderBy.

using Plainquire.Sort;

var getOrdersUrl = "/GetOrders?orderBy=customer,number-desc"

[HttpGet]
public Task<List<Order>> GetOrders([FromQuery] EntitySort<Order> sort)
{
    var orders = new List<Order>();
    var sortedOrders = orders.OrderBy(sort);
    Console.WriteLine($"{sortedOrders.OrderBy(sort)}");
    // Output: orders.OrderBy(x => IIF((x == null), null, x.Customer)).ThenByDescending(x => x.Number)

    var queryParams = sort.ToString();
    // Output: Customer-asc, Number-desc
}

Configure model binding

By default, parameters for properties of sorted entity are named {Entity}{Property}. By default, all public non-complex properties (string, int, DateTime, ...) are recognized. Parameters can be renamed or removed using FilterAttribute and EntityFilterAttribute.

For the code below Number is not mapped anymore and Customer becomes CustomerName.

using Plainquire.Filter.Abstractions;

// Remove prefix, e.g. property 'Number' is mapped from 'number', not 'orderNumber'
// Use 'sortBy' as query parameter name instead of default 'orderBy'
[EntityFilter(Prefix = "")]
public class Order
{
     // 'Number' is removed from sort and will be ignored
    [Filter(Sortable = false)]
    public int Number { get; set; }

    // 'Customer' is mapped from query-parameter 'customerName'
    [Filter(Name = "CustomerName")]
    public string Customer { get; set; }
}

Order sets

Multiple entity sorts can be combined to a set of filters using the EntitySortSetAttribute.

using Plainquire.Sort;
using Plainquire.Sort.Abstractions;

// Use
[HttpGet]
public Task<List<Order>> GetOrders([FromQuery] OrderSortSet orderSet)
{
    var orderSort = orderSet.Order;
    var orderItemSort = orderSet.OrderItem;
}

// Instead of
public Task<List<Order>> GetOrders([FromQuery] EntitySort<Order> orderSort, EntitySort<OrderItem> orderItemSort) { ... }

[EntitySortSet]
public class OrderSortSet
{
    public EntitySort<Order> Order { get; set; }
    public EntitySort<OrderItem> OrderItem { get; set; }
}

Swagger / OpenAPI

Register OpenAPI support

Swagger / OpenAPI is supported when using Swashbuckle.AspNetCore.

Package Manager : Install-Package Plainquire.Sort.Swashbuckle
CLI : dotnet add package Plainquire.Sort.Swashbuckle

using Plainquire.Sort.Swashbuckle;

services.AddSwaggerGen(options =>
{
    // Register filters used to modify swagger.json
    options.AddSortSupport();
});

Support for Newtonsoft.Json

By default, System.Text.Json is used to serialize/convert Plainquire specific stuff. If you like to use Newtonsoft.Json you must register it.

Package Manager : Install-Package Plainquire.Sort.Mvc.Newtonsoft
CLI : dotnet add package Plainquire.Sort.Mvc.Newtonsoft

using Plainquire.Sort.Mvc.Newtonsoft;

// Register support for Newtonsoft by calling
// 'AddSortNewtonsoftSupport()' on IMvcBuilder instance
services.AddControllers().AddSortNewtonsoftSupport();

Interception

Creation of sort expression can be intercepted via ISortInterceptor.

var sort = new EntitySort<Order>();
var interceptor = new CaseInsensitiveSortInterceptor();
var filtered = orders.OrderBy(sort, interceptor);

A default interceptor can be provided via static ISortInterceptor.Default.

Advanced scenarios

Deep copy

The EntitySort<T> class supports deep cloning by calling the Clone() method

var copy = sort.Clone();

Casting

Sorting can be cast between entities, e.g. to convert them between DTOs and database models.

Properties are matched by type (check if assignable) and name (case-sensitive)

var dtoSort = new EntitySort<OrderDto>().Add(...);
var orderSort = dtoSort.Cast<Order>();

Serialization

Using `System.Text.Json`

Objects of type EntitySort<T> can be serialized via System.Text.Json.JsonSerializer without further requirements

var json = JsonSerializer.Serialize(sort);
sort = JsonSerializer.Deserialize<EntitySort<Order>>(json);

Using `Newtonsoft.Json`

When using Newtonsoft.Json additional converters are required

Package Manager : Install-Package Plainquire.Sort.Newtonsoft
CLI : dotnet add package Plainquire.Sort.Newtonsoft

using Plainquire.Sort.Newtonsoft;

var json = JsonConvert.SerializeObject(sort, JsonConverterExtensions.NewtonsoftConverters);
sort = JsonConvert.DeserializeObject<EntitySort<Order>>(json, JsonConverterExtensions.NewtonsoftConverters);

Page Entities

Basic usage

Install NuGet packages

Package Manager : Install-Package Plainquire.Page
CLI : dotnet add package Plainquire.Page

Create a page

using Plainquire.Page;

// Direct pageing is the preferred way
var pagedOrders = orders.Page(pageNumber: 2, pageSize: 3).ToList();

// Alternative, create a EntityPage object
var page = new EntityPage(pageNumber: 2, pageSize: 3);

// Use page with LINQ
var pagedOrders = orders.Page(page).ToList();
// Or queryables (e.g. Entity Framework)
var pagedOrders = dbContext.Orders.Page(page).ToList();

Configure pagination

Create configuration

using Plainquire.Page.Abstractions;

var configuration = new PageConfiguration() { IgnoreParseExceptions = true };

Provide configuration

// For MVC model binding via dependency injection
services.Configure<PageConfiguration>(c => c.IgnoreParseExceptions = true);

// Via constructor
new EntityPage<Order>(configuration);

// Via static default
PageConfiguration.Default

Configuration reference

Configuration	Description
`IgnoreParseExceptions`	Omit paging in case of any exception while parsing the value

REST / MVC

To page an entity via model binding, the entity must be marked with EntityFilterAttribute

Register model binders

Package Manager : Install-Package Plainquire.Page.Mvc
CLI : dotnet add package Plainquire.Page.Mvc

using Plainquire.Page.Mvc;

// Register required stuff by calling 'AddPageSupport()' on IMvcBuilder instance
services.AddControllers().AddPageSupport();

Map HTTP query parameter to `EntityPage`

With model binding enabled, REST requests can be paged using query parameters page and pageSize.

using Plainquire.Page;

var getOrdersUrl = "/GetOrders?page=2&pageSize=3"

[HttpGet]
public Task<List<Order>> GetOrders([FromQuery] EntityPage<Order> page)
{
    return dbContext.Orders.Page(page).ToList();
}

Configure model binding

Parameters can be renamed EntityFilterAttribute.

For the code below page number is taken from query parameter pageNumber and page size from size.

using Plainquire.Filter.Abstractions;

[EntityFilter(PageNumberParameter = "pageNumber", PageSizeParameter = "size")]
public class Order
{
    public string Customer { get; set; }
}

Swagger / OpenAPI

Register OpenAPI support

Swagger / OpenAPI is supported when using Swashbuckle.AspNetCore.

Package Manager : Install-Package Plainquire.Page.Swashbuckle
CLI : dotnet add package Plainquire.Page.Swashbuckle

using Plainquire.Page.Swashbuckle;

services.AddSwaggerGen(options =>
{
    // Register filters used to modify swagger.json
    options.AddPageSupport();
});

Support for Newtonsoft.Json

By default, System.Text.Json is used to serialize/convert Plainquire specific stuff. If you like to use Newtonsoft.Json you must register it.

Package Manager : Install-Package Plainquire.Page.Mvc.Newtonsoft
CLI : dotnet add package Plainquire.Page.Mvc.Newtonsoft

using Plainquire.Page.Mvc.Newtonsoft;

// Register support for Newtonsoft by calling
// 'AddPageNewtonsoftSupport()' on IMvcBuilder instance
services.AddControllers().AddPageNewtonsoftSupport();

Interception

Creation of page expression can be intercepted via IPageInterceptor.

var page = new EntityPage();
var interceptor = new PageBackwardInterceptor();
var paged = orders.Page(page, interceptor);

A default interceptor can be provided via static IPageInterceptor.Default.

Advanced Scenarios

Deep copy

The EntityPage<T> class supports deep cloning by calling the Clone() method

var copy = page.Clone();

Serialization

Using `System.Text.Json`

Objects of type EntityPage<T> can be serialized via System.Text.Json.JsonSerializer without further requirements

var json = JsonSerializer.Serialize(page);
page = JsonSerializer.Deserialize<EntityPage<Order>>(json);

Using `Newtonsoft.Json`

When using Newtonsoft.Json additional converters are required

Package Manager : Install-Package Plainquire.Page.Newtonsoft
CLI : dotnet add package Plainquire.Page.Newtonsoft

using Plainquire.Page.Newtonsoft;

var json = JsonConvert.SerializeObject(page, JsonConverterExtensions.NewtonsoftConverters);
sort = JsonConvert.DeserializeObject<EntityPage<Order>>(json, JsonConverterExtensions.NewtonsoftConverters);

Upgrade from FilterExpressionCreator

Install Schick.FilterExpressionCreator* 4.7.x.
Fix all warnings. This can largely be done by sear and replacing with regular expressions
- Search for: FS.FilterExpressionCreator(\.Abstractions|Mvc|Mvc\.Newtonsoft|Newtonsoft)?(\.\w+)?
- Replace with: Plainquire.Filter$1
- Search for: \[FilterEntity($.*$)]
- Replace with: [EntityFilter$1]
Fix remaining errors and warnings following description of breaking changes
Uninstall all Schick.FilterExpressionCreator* stuff
Install corresponding Plainquire.* packages

Schick.FilterExpressionCreator.Newtonsoft Release 2.1.0

Release 2.1.0 Toggle Dropdown 4.7.0 4.6.1 4.6.0 4.5.0 4.4.0 4.3.0 4.2.2 4.2.1 4.2.0 4.1.1-beta1

Documentation

Plainquire

Demo

Usage for ASP.NET Core

1. Install NuGet packages

2. Register services

3. Setup entity

4. Create HTTP endpoint

5. Send HTTP request

6. Results in SQL statement

Usage for non-web applications

1. Install NuGet package

2. Setup entity

3. Create repository

Table of contents

Features

Syntax

Filter syntax

Sort syntax

Page Syntax

Filter entities

Basic usage

Configure filters

Create configuration

Provide configuration

Configuration reference

Filter by special values

Filter by == null / != null

Filter by "" / string.Empty

Filter by Date/Time

Filter by Date/Time with natural language

Filter by enum

Filter by numbers

Logical Operators

Add/Replace filter using logical OR

Add/Replace filter using logical AND

Nested filters

Retrieve syntax and filter values

REST / MVC

Register model binders

Map HTTP query parameters to EntityFilter

Configure model binding

Filter sets

Swagger / OpenAPI

Register OpenAPI support

Register XML documentation

Support for Newtonsoft.Json

Interception

Default interceptor

Sample interceptor

Advanced scenarios

Deep copy

Casting

Serialization

Using System.Text.Json

Using Newtonsoft.Json

Combine filter expressions

Sort entities

Basic usage

Configure sorting

Create configuration

Provide configuration

Configuration reference

Sort entities

Sort nested entities

Retrieve syntax and sort direction

REST / MVC

Register model binders

Map HTTP query parameter to EntitySort

Configure model binding

Order sets

Swagger / OpenAPI

Register OpenAPI support

Support for Newtonsoft.Json

Interception

Advanced scenarios

Deep copy

Casting

Schick.FilterExpressionCreator.Newtonsoft
Release 2.1.0

Release 2.1.0

4.7.0

4.6.1

4.6.0

4.5.0

4.4.0

4.3.0

4.2.2

4.2.1

4.2.0

4.1.1-beta1

Filter by `== null` / `!= null`

Filter by `""` / `string.Empty`

Map HTTP query parameters to `EntityFilter`

Using `System.Text.Json`

Using `Newtonsoft.Json`

Map HTTP query parameter to `EntitySort`

Using `System.Text.Json`

Using `Newtonsoft.Json`

Map HTTP query parameter to `EntityPage`

Using `System.Text.Json`

Using `Newtonsoft.Json`