Dekiru.QueryFilter 9.4.6

Dynamic Query Library

This library provides a parser for a simple query language that can be used to filter data in the form of IQueryable<T>. The main use case is to provide a simple query language for REST APIs to use with Entity Framework Core. The library is inspired by the OData query language, but only a subset of the OData query language is supported (i.e., $filter, $orderby, $select, and $expand).

Note on versioning. Previously, this library was released with multi-targeting, where the first digit of the version number indicated the target framework. Starting with 9.4.0, this is no longer the case. The library will only target the lowest still supported .NET version (at the time of writing, .NET 8). The library will move onto v10.x.y on November 10, 2026, when .NET 8 goes out of support.

The library is available as a NuGet package: Dekiru.QueryFilter.

The library is primarily intended to be used with Entity Framework Core, but it can be used with any data source that supports IQueryable<T> (Excluding Include-methods, as they are tightly coupled with Entity Framework Core). There is no explicit dependency on Entity Framework Core, the library will attempt to dynamically load the Microsoft.EntityFrameworkCore assembly when it is needed, which is when IncludeDynamic is called.

Should this load fail, the library will throw an exception. To avoid this, the Microsoft.EntityFrameworkCore assembly can be loaded explicitly by calling QueryFilterParser.RegisterEntityFrameworkAssembly(Assembly) and passing the assembly that contains the Microsoft.EntityFrameworkCore namespace.

The following methods are available:

• Count<T>(string? filter) - Returns the number of entries in the collection that matches the filter. The filter is optional, and if it is not supplied, the method will return the total number of entries in the collection. If supplied, it follows the same syntax as FilterDynamic.
• SelectDynamic<Dictionary<string, object?>>(string[] columns) - Projects the collection to a new collection of an anonymous type with the specified columns.
• SelectDynamic<TSource, TResult>(string[] columns) - Projects the collection to a new collection of the specified type with the specified columns.
• FilterDynamic<T>(string filter) - Filters the collection using the specified filter.
• IncludeDynamic<T>(string include) - Includes the specified navigation properties in the collection.
  • IncludeDynamic<T>(string[] includes) - Wrapper for including multiple properties via IncludeDynamic(string include).
• SortDynamic<T>(string orderBy) - Orders the collection by the specified order.
• RemoveCyclesAsync<T>() - Removes cycles from the collection. This is useful when serializing the collection to JSON. It does this by extracting which properties have been explicitly included in the query, and sets the rest to null. This method will enumerate the collection (thus executing the SQL query), and should be used as late as possible in the query pipeline.
  • RemoveCycles<T>() - Synchronous version of RemoveCyclesAsync<T>().
  • RemoveCyclesFirstOrDefaultAsync<T>() - Calls FirstOrDefaultAsync instead of ToListAsync.
  • RemoveCyclesFirstOrDefault<T>() - Synchronous version of RemoveCyclesFirstOrDefaultAsync<T>().

Generally, any C# property or method (whether static or instance) that belong to a supported type can be used in the query language. This, however, excludes extension methods such as Any or Count. Some extension methods are still supported, see more about this in the Functions section.

The only limiting factor is whether or not Entity Framework Core can translate the expression to SQL. Entity Framework Core will throw an exception if it encounters an expression that cannot be translated to SQL.

If you encounter such an exception, you may need to rewrite the query to avoid the offending expression. If encounter an exception that you believe should be translatable to SQL, please open an issue.

There is a special meta variable called $, that can be used to refer to the Entity Framework Core DbContext instance. This can be used to filter on entities that are not connected to the entity that is being queried.

Note: Null semantics, as well as case sensitivity, are determined by the underlying data source.

Filtering

Filtering is done via a simplified query langauge, which is inspired by the OData query language. See below for a list of supported operators and functions. It is possible to filter on properties of the entity, as well as on navigation properties and their properties, at any depth.

Note: Filtering on navigation properties will not affect any included properties, it will only affect the root collection. To filter included properties, the filter must be applied in the IncludeDynamic call.

Macros

Macros may be registered by calling any of the FilterMacros.Add methods. Macros are used to define reusable expressions that can be used in queries, and can be defined for any type. Macros are defined in the form of a lambda expression, whose body will be inlined into the query, and can be used to encapsulate complex expressions as well as exposing extension methods as defined on EF.Functions.

Macros may have 0 to 5 arguments, which may be of any type.

When used in a filter expression, macros are referenced by their name, and can be called with arguments. Arguments are passed in the form of a lambda expression, and can be used in the macro body. They are invoked by prefixing the macro name with a @, like so: @myMacro, @myMacro(), or @myMacro(arg1, arg2).

Because the "current" entity is implicit, there are some exceptions when passing a reference to a macro. Given the following macro:

FilterMacros.Add<MyEntity, int>("myMacro", (e, id) => e.Id == id)

A filter expression using this macro on the MyEntity entity would look like this:

@myMacro(42) or @myMacro(this, 42)

While resolving a macro, if the library fails to find a macro with the name and parameters specified (i.e. @myMacro(42)), it will insert the current entity at the head of the argument list and try again (i.e. as if call had been @myMacro(this, 42)). Because of the implicit this argument, the macro can be called with just the argument, or with the this argument explicitly passed. As such, a macro defined as FilterMacros.Add<MyEntity>("notDeleted", e => !e.Deleted) can be called as @notDeleted or @notDeleted(this). Parentheses are required when passing additional arguments to the macro.

It is also permissible to call a macro without using the @ prefix, but in that case parentheses are required and the argument types must match the macro definition exactly, as due to technical limitations the library cannot infer the types of the arguments.

Macros can also be used while filtering on navigation properties. In this case, the macro must be defined for the type of the navigation property:

myEntities.any(e: @myMacro(e, 42))

Here's how one might define a macro to wrap the EF.Functions.Like method:

FilterMacros.Add<string, string, bool>("like", (s, pattern) => EF.Functions.Like(s, pattern))

And here's how one might use it in a filter expression:

@like(Name, "John%")

Including navigation properties

Navigation properties can be included in the query by using the IncludeDynamic method. It matches the feature set of Include and ThenInclude in Entity Framework Core.

The include syntax has the following variants:

• MyProperty - Includes the navigation property MyProperty.
• MyProperty.SubProperty - Includes the navigation property MyProperty, and then includes the navigation property SubProperty.
• MyProperty1,MyProperty2 - Includes the navigation properties MyProperty1 and MyProperty2.

Each variant can be combined with the others. If the included property is a collection, a filter expression can be used to filter the included values. This is done by appending the filter to the property like so: MyProperty(Name eq 'Foo'). Note that the filter at the root level does not use a lambda expression, as it is implied. Nested filters will require a lambda expression, however.

Note: Applying a filter to an included collection will not affect the root collection, it will only affect the included values. To filter the root collection, the filter must be applied in the FilterDynamic call.

Beyond filtering, it is also possible to sort and paginate the included properties. This is done by appending sort, skip, and/or take to the property like so: MyProperty:sort(Name desc):skip(10):take(5). The sort, skip, and take expressions can be combined in any order, but the sort expression will always be applied first.

It is possible to sort on multiple properties by repeating the sort expression: MyProperty:sort(Name desc):sort(Age asc). The sort expressions will be applied in the order they are specified. Specifying the sort direction is optional, and if it is not supplied, the default sort direction will be ascending.

There are some configurable limits to the depth of the included properties, as well as the number of items that can be included. These limits can be configured by calling the SetMaxIncludeDepth and SetMaxIncludeItems methods on the QueryFilterExtensions class. The default values are no limit on the depth or number of items.

Limiting which properties can be included, sorted, or filtered

The following attributes are available to limit which properties can be included, sorted, or filtered:

• [DisableInclude] - Disables including the property.
• [DisableSort] - Disables sorting on the property.
• [DisableFilter] - Disables filtering on the property.

All attributes can currently only be applied to properties. Making [DisableInclude] work on classes is a planned feature.

Operators

The following infix/prefix operators are supported:

• and - SQL AND operator
• or - SQL OR operator
• not - SQL NOT operator
• eq - SQL = operator
• ne - SQL <> operator
• gt - SQL > operator
• ge - SQL >= operator
• lt - SQL < operator
• le - SQL <= operator
• in - SQL IN operator

Function calls

All methods that work on properties can be called either as a static method or as a member method. The syntax is either myMethod(SomeCollection) or SomeCollection.myMethod(). There is no functional difference between the two syntaxes, it is just a matter of preference.

Some methods may be called with a predicate, they are defined as a lamba expression with the following syntax:

SomeCollection.myMethod(c: c.Name eq 'John')

Nested predicates are supported:

SomeCollection.any(c: c.OtherProp.Count(r: r.Name eq 'Bar') gt 5)

Functions

Aside from the methods as described above, the following extension methods are available:

• Collection
  • Count(identifier) - Returns the number of entries in the collection: Items.count()
  • Count(identifier, predicate) - Returns the number of entries in the collection that matches the predicate: Items.count(i: i.Name eq "Foo")
    • In addition to the normal count methods, CountLong also exists with the same overloads, returning a long instead of an int.
  • Sum(identifier, selector) - Returns the sum of a property in all entries in the collection: Items.sum(i: i.SomeNumber)
  • Max(identifier, selector) - Returns the maximum value of a property in the collection: Items.max(i: i.SomeNumber)
  • Any(identifier) - Returns true if the collection contains any entries: Items.any()
  • Any(identifier, predicate) - Returns true if the collection contains any entries that match the predicate: Items.any(i: i.Name eq "Foo")
  • All(identifier, predicate) - Returns true if all of the entries in the collection matches the predicate: Items.all(i: i.Name eq "Foo")
• Conversion - Normally, the library attempts to handle conversions automatically. However, in some cases it may be necessary to explicitly convert a value. The following conversion methods are available:
  • Long(value) - Converts a value to a long: SomeProp.long()
  • ULong(value) - Converts a value to an unsigned long: SomeProp.ulong()
  • Int(value) - Converts a value to an int: SomeProp.int()
  • UInt(value) - Converts a value to an int: SomeProp.uint()
  • Short(value) - Converts a value to a short: SomeProp.short()
  • UShort(value) - Converts a value to an ushort: SomeProp.ushort()
  • Byte(value) - Converts a value to a byte: SomeProp.byte()
  • SByte(value) - Converts a value to an sbyte: SomeProp.sbyte()
  • Decimal(value) - Converts a value to a decimal: SomeProp.decimal()
  • Double(value) - Converts a value to an double: SomeProp.double()
  • Float(value) - Converts a value to an float: SomeProp.float()
  • DateOnly(value) - Converts a value to a DateOnly: SomeProp.dateOnly()
  • TimeOnly(value) - Converts a value to a TimeOnly: SomeProp.timeOnly()
  • DateTimeOffset(value) - Converts a value to a DateTimeOffset: SomeProp.dateTimeOffset()
  • DateTime(value) - Converts a value to a DateTime: SomeProp.dateTime()
  • TimeSpan(value) - Converts a value to a TimeSpan: SomeProp.timeSpan()
  • Guid(value) - Converts a value to a Guid: SomeProp.guid()
  • String(value) - Converts a value to a string: SomeProp.string()
• Date
  • Now() - Returns a DateTime object representing the current date and time: now()
  • UtcNow() - Returns a DateTime object representing the current date and time in UTC: utcNow()
  • Today() - Returns a DateTime object representing the current date: today()
  • Ago(span) - Returns a DateTime object with the specified ISO 8601 duration subtracted from current date and time: since("PT1D")
  • AgoUtc(span) - Returns a DateTime object with the specified ISO 8601 duration subtracted from current date and time in UTC: sinceUtc("PT1D")
• Object
  • Between(value, fromValue, toValue) - Returns true if the value is equal to, or between, two other values. Works on any type that supports gte and lte: SomeNumber.between(1, 10)
  • Coalesce(value1, value2) - Returns the value of the property if it is not null, otherwise the supplied value will be returned: Name.coalesce("bar")
  • IsNull(identifier) - Returns true if the property is null: Name.isnull()
  • IsNotNull(identifier) - Returns true if the property is not null: Name.isnotnull()
• String
  • Lower(identifier) - Converts a string to lower case: Name.lower() (This is equivalent to Name.ToLower(), and is only included to support existing queries that use this syntax.)
  • Upper(value) - Converts a string to upper case: Name.upper()
  • NotContains(identifier, arg) - Negated Contains: name.NotContains("foo")
  • Empty(identifier) - Returns true if the property is an empty string: Name.empty()

Function names are case insensitive.

Sample queries

Order of operations

The order of operations is as follows, from highest to lowest precedence:

• Identifiers, literals, array expressions, and parentheses
• Member access, and function calls
• IN operator
• NOT and unary minus operator
• Multiplicative operators (*, /, %)
• Additive operators (+, -)
• Relative operators (lt, lte, gt, gte)
• Equality operators (eq, ne)
• Logical AND operator
• Logical OR operator
• Lambda expressions