Plainquire

<img src="https://plainquire.github.io/plainquire/badges/release.svg?"> <img src="https://plainquire.github.io/plainquire/badges/license.svg?"> <img src="https://plainquire.github.io/plainquire/badges/build.svg?"> <img src="https://plainquire.github.io/plainquire/badges/tests.svg?"> <img src="https://plainquire.github.io/plainquire/badges/coverage.svg?">

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;
}

Table of contents

• Features
• Syntax
  • Filter syntax
  • Sort syntax
• Filter entities
  • Basic usage
  • Configure filters
  • Filter by special values
  • Logical Operators
  • Nested filters
  • Retrieve syntax and filter values
  • REST / MVC
  • Swagger / OpenAPI
  • Support for Newtonsoft.Json
  • Interception
  • Advanced scenarios
• Sort entities
  • Basic usage
  • Configure sorting
  • Sort entities
  • Sort nested entities
  • Retrieve syntax and sort direction
  • REST / MVC
  • Swagger / OpenAPI
  • Support for Newtonsoft.Json
  • Interception
  • Advanced scenarios
• Page Entities
  • Basic usage
  • Configure pagination
  • REST / MVC
  • Swagger / OpenAPI
  • Support for Newtonsoft.Json
  • Interception
  • Advanced Scenarios
• 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 | | ------------------------------------------ | ------------------------------------------------------- | | <code>&gender=<b>=female</b></code> | Gender equals female (case insensitive) | | <code>&gender=<b>=male,=female</b></code> | Gender equals male OR female (case insensitive) | | <code>&gender=<b>==female</b></code> | Gender equals female (case sensitive) | | <code>&gender=<b>~male</b></code> | Gender contains male (fetches female too) | | <code>&tag=<b>ISNULL</b></code> | Tag is null | | <code>&tag=<b>=</b></code> | Tag equals "" | | <code>&tag=<b>==A;B</b></code> | Tag equals =A;B (case insensitive due escaped syntax) | | <code>&size=<b><100</b></code> | Size is lower than 100 | | <code>&size=<b>>100&size=<200</b></code> | Size is between 100 and 200 | | <code>&created=<b>>two-days-ago</b></code> | Created within the last 2 days | | <code>&created=<b>yesterday</b></code> | Created yesterday | | <code>&created=<b>>2020-03</b></code> | Created after Sunday, March 1, 2020 | | <code>&created=<b>2020</b></code> | 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 | | -------------------------------------------------------- | ------------------------------------------------------------ | | <code>&orderBy=<b>lastName</b></code> | Sort by lastName ascending | | <code>&orderBy=<b>lastName-</b></code> | Sort by lastName descending | |