Operator

Operator declares the comparison applied by a Condition. There are 28 operators total. Every operator that starts with I is the case-insensitive variant — both sides are normalized with .ToLower() before comparison. On PostgreSQL the six pattern variants can match with ILIKE instead, since 3.5.0 — see case-insensitive matching.

Required values at a glance

Each operator expects a specific number of entries in Condition.Values. Sending the wrong count throws a LogicException whose message is one of ConditionWithOperator[<op>]MustHasOnlyOneValue, ConditionWithOperator[Between-NotBetween]MustHasOnlyTwoValues, ConditionWithOperator[In-IIn-NotIn-INotIn]MustHasOneOrMoreValues, or ConditionWithOperator[IsNull-IsNotNull]MustHasNoValues. There is no error-code property — the string is the message.

Equality

Contains / StartsWith / EndsWith

In / NotIn (set membership)

Ordered comparisons & ranges

Null checks

JSON examples

Range (Between):

{
  "sort": 1,
  "field": "Price",
  "dataType": "Number",
  "operator": "Between",
  "values": [100, 500]
}

Set membership (IIn):

{
  "sort": 1,
  "field": "Country",
  "dataType": "Text",
  "operator": "IIn",
  "values": ["USA", "Canada", "UK"]
}

Null check (IsNull):

{
  "sort": 1,
  "field": "DeletedAt",
  "dataType": "DateTime",
  "operator": "IsNull",
  "values": []
}

Case-insensitive matching on PostgreSQL

New in 3.5.0. By default the I* operators lower both sides, field.ToLower().Contains("value"), which a relational provider writes as lower(column) LIKE …. A pg_trgm GIN or GiST index on the column cannot serve that, and it can serve column ILIKE '%value%'. How the case-insensitive operators match is one choice for the whole process, TextMatching, in DynamicWhere.ex.Enums; no request carries it.

A deployment on PostgreSQL chooses it once, at startup:

using DynamicWhere.ex.Enums;
using DynamicWhere.ex.Source;

DwText.Configure(o => o.CaseInsensitive = TextMatching.ILike);

// Or from configuration. A key nothing answers to — "CaseInsensitiv" — refuses to start,
// and so does a single value where the section belongs: "DynamicWhere:Text": "ILike".
DwText.Configure(new DwTextOptions().Bind(configuration.GetSection("DynamicWhere:Text")));

{
  "DynamicWhere": {
    "Text": {
      "CaseInsensitive": "ILike"
    }
  }
}

With ILike chosen, a condition on Name with the value "ab" compiles on Npgsql to:

• The predicate is built and parsed exactly as before, and the parsed lambda is then rewritten into EF.Functions.ILike, with a backslash as the escape character. The value is the one the condition sent, trimmed and lowered, with every backslash, % and _ in it escaped, so it matches as text and never as a pattern. A negation keeps its NOT, and the null guard stays in front. getQueryString shows the ILIKE SQL.
• IEqual, INotEqual, IIn and INotIn stay lower(column) = value, which an index on lower(column) serves, and a Having condition stays lowered, since an aggregate uses no index. The case-sensitive operators are not affected.
• It applies wherever the library filters through Where: a Filter, every set of a Segment and a Summary's row filter, guarded or not. Only the predicate the library parsed is rewritten; one the caller composed with LINQ beneath it is left as written.
• Only a query EF Core's own provider translates is rewritten. LINQ to objects, the IEnumerable<T> overloads and a provider that wraps EF Core's, such as LinqKit's AsExpandable(), keep lowering.
• Against PostgreSQL 16 it returns the same rows as lowering, and as LINQ to objects, for all six operators over ASCII values carrying %, _, backslashes and quotes, through the plain, guarded and segment paths.

Related

• DataType → which operators each logical type accepts.
• Condition validation → the exact messages thrown on count mismatches.
• Error codes → full reference.
• Breaking changes → what opting in to ILIKE changes, and for whom.