Query Semantics

Query Semantics

This is the specification. It defines exactly how a Query is evaluated, and every provider is written to obey it. If you are implementing a provider, porting QueryForge to another language, or trying to explain why a particular row did or did not come back, this is the page.

The rules live in PepperX.QueryForge.Querying.ConditionSemantics and are called by all three providers, so there is one definition rather than three that drift.

---

The pipeline

Evaluation happens in a fixed order. Every provider follows it.

1. Filter      apply Criteria                → the matching set
2. Count       size of the matching set      → Meta.Total
3. Sort        apply SortColumns             → a deterministic order
4. Page        apply Paging                  → the requested window
5. Project     apply SelectColumns           → narrowed rows
6. Group       apply GroupByColumns          → a hierarchy, if any

Two consequences worth stating plainly:

• Paging applies after filtering, so Meta.Total.Rows describes the filtered set, not the table.
• When GroupByColumns is non-empty the pipeline changes shape: paging applies to the outermost group keys rather than to rows, and the counts describe groups. See Grouping and Hierarchies.

---

Usability

Before anything is evaluated, each condition is tested for whether it contributes at all. This is ConditionSemantics.IsExecutable.

A condition is unusable — and is silently dropped — when any of these hold:

1. ColumnName is null, empty, or whitespace.
2. Operator is not a defined ConditionOperator value.
3. Value is null and the operator is not Equals or NotEquals.
4. The operator is Between and ValueTo is null.

In pseudocode:

usable(c):
    if blank(c.ColumnName):            return false
    if not defined(c.Operator):        return false
    v = unwrap(c.Value)
    if c.Operator in {Equals, NotEquals}:  return true     // null is meaningful here
    if v is null:                      return false
    if c.Operator == Between:          return unwrap(c.ValueTo) is not null
    return true

On top of that, every provider drops a condition whose ColumnName is not a real column of the target — see column resolution below.

Why dropped rather than false. A filter the caller never filled in must not silently empty the result. A search box that is blank means "no constraint", not "match nothing". This is the single most important behavioural decision in QueryForge, and it applies to sorts, groupings and projections too: an input that cannot be honoured is ignored, never treated as an error condition that empties the page.

What happens to a group that loses every condition

It contributes nothing and is skipped. It does not become an always-true or always-false clause, and it does not affect how the remaining groups are joined.

If every group ends up empty, the criteria as a whole imposes no filter and everything matches.

---

Combining conditions and groups

Logic does two jobs. Which one applies depends on where it sits.

Logic	Inside a ConditionGroup	On QueryCriteria
And	join conditions with AND	join groups with AND
Or	join conditions with OR	join groups with OR
AndNot	join with AND, then negate the group	join groups with AND (the Not is ignored)
OrNot	join with OR, then negate the group	join groups with OR (the Not is ignored)

So a criteria tree evaluates as:

result = ⨁criteria.Logic  [ maybe_negate( ⨁group.Logic [ condition… ] ) for each group ]

Negation is a group-level feature. There is no way to negate a single condition other than by putting it in its own group with AndNot/OrNot, or by using the operator's negative form (NotEquals, NotContains).

Worked example

{
  "criteria": {
    "logic": 0,
    "groups": [
      { "logic": 1, "conditions": [
          { "columnName": "Country", "operator": 0, "value": "Germany" },
          { "columnName": "Country", "operator": 0, "value": "Canada"  } ] },
      { "logic": 2, "conditions": [
          { "columnName": "Department", "operator": 0, "value": "HR" } ] }
    ]
  }
}

Reads as:

(Country = 'Germany' OR Country = 'Canada') AND NOT (Department = 'HR')

Group 1 uses Or (1) to join its two conditions. Group 2 uses AndNot (2): the And joins it to the previous group, the Not negates it. The criteria-level And (0) joins the groups.

---

Three-valued logic

SQL evaluates a comparison against NULL as unknown, not false, and unknown survives negation — NOT (unknown) is still unknown, and a row whose predicate is unknown does not appear.

QueryForge reproduces this everywhere, including in memory and through EF Core. It is why a negated filter returns the same rows on every provider instead of quietly picking up null rows on some.

The truth tables, with null standing for unknown:

AND	true	false	unknown
true	true	false	unknown
false	false	false	false
unknown	unknown	false	unknown

OR	true	false	unknown
true	true	true	true
false	true	false	unknown
unknown	true	unknown	unknown

NOT
true	false
false	true
unknown	unknown

A row is returned only when the final result is true. Unknown does not qualify.

Where a condition evaluates to unknown

Given a usable condition, let actual be the row's value and expected the condition's value:

Case	Result
Equals with expected null	actual is null — a definite true or false
NotEquals with expected null	actual is not null — a definite true or false
any other operator, actual is null	unknown
Between with a null upper bound	unknown
otherwise	the comparison's result

So IS NULL and IS NOT NULL are the only tests that give a definite answer about a null column. Every other operator applied to a null column is unknown, which keeps a null row out of the result whether or not its group is negated.

How each provider implements it

• In-Memory — implements the tables above literally, with bool? where null is unknown.
• Dapper — emits SQL and lets the database do it, which is where the tables come from.
• EF Core — cannot rely on NOT (…), because EF Core's null compensation would rewrite col != value into col <> value OR col IS NULL and let null rows back in. Instead a negated group is compiled by inverting each condition's operator and adding an explicit col IS NOT NULL guard, which produces the same rows.

---

Column resolution

A column name is matched case-insensitively against the columns the target actually exposes. What "actually exposes" means depends on the provider:

Provider	The whitelist is	Discovered by
Dapper	the target's real result-set columns	SELECT * FROM target WHERE 1 = 0, cached per object
EF Core	the entity type's readable instance properties	reflection, cached per type
In-Memory	the model's readable instance properties	reflection, cached per type

A name that is not on the whitelist is dropped, in filters, sorts, groupings and projections alike. It is never escaped and emitted, which is what stops a query being used to discover your schema. See Security.

In-Memory exception. When you supply your own valueAccessor, existence checking is switched off and every column name is taken at face value — QueryForge cannot know what your accessor can resolve. You own that decision. See In-Memory Provider.

---

The operators

Equality

Operator	Value is null	Value is set
Equals	column IS NULL	column = @p
NotEquals	column IS NOT NULL	column <> @p

In memory, string equality is case-insensitive (OrdinalIgnoreCase), matching the default SQL Server collation, which is the behaviour QueryForge has always had. On a database, the collation of the column decides — a case-sensitive collation makes string equality case-sensitive there. This is a real divergence and is listed under Cross-Provider Parity.

Non-string equality goes through QueryValueComparer, which reconciles mismatched CLR types — so 1, 1L and "1" all compare equal.

Ordering comparisons

LessThan, GreaterThan, LessThanOrEqualTo, GreaterThanOrEqualTo map to <, >, <=, >=.

They are not string comparisons. On a database, the value is coerced to the column's real type before binding, so Age > 9 ranks 30 above 9 rather than sorting lexically. In memory, QueryValueComparer compares numerically wherever both sides can be read as numbers.

Between

Inclusive at both ends — column BETWEEN @lower AND @upper, equivalently column >= lower AND column <= upper.

Requires both Value and ValueTo. If ValueTo is missing, the condition is unusable and is dropped. QueryForge does not reorder the bounds: if Value is greater than ValueTo the range is empty, exactly as it is in SQL.

Text matching

Operator	Pattern	SQL
Contains	%value%	column LIKE @p ESCAPE …
NotContains	%value%	column NOT LIKE @p ESCAPE …
StartsWith	value%	column LIKE @p ESCAPE …
EndsWith	%value	column LIKE @p ESCAPE …

Wildcards inside the caller's value are escaped so they match literally. Only the wildcards QueryForge adds are significant. Searching for 50% finds the literal text "50%", not "anything starting with 50". %, _ and the escape character itself are escaped on every engine; SQL Server additionally escapes [, which starts a character class there. The matching ESCAPE clause is emitted alongside.

There is no negated form of StartsWith or EndsWith in the operator set. Wrap the condition in a group with AndNot to get one.

On the database, case sensitivity is the column's collation. In memory, text matching is OrdinalIgnoreCase.

EF Core applies text operators to string properties only. Applying one to a non-string property drops the condition, because the alternative — a client-side ToString() — is not translatable to SQL.

---

Value coercion

A Query arriving as JSON carries almost no usable type information: numbers can appear as strings, dates always do. Permissive engines paper over this; strict ones do not. PostgreSQL rejects integer > text outright rather than guessing.

So the Dapper provider coerces every filter value to the column's real CLR type — discovered from the result set alongside the column name — before binding it. That keeps one request working on every engine and lets the database compare using the column's own type and its indexes.

ValueCoercion.TryCoerce handles, in order:

Target type	Accepted input
already the target type	passed through
enum	member name (case-insensitive) or an underlying numeric value
Guid	a Guid, or any string Guid.Parse accepts
DateTime	a DateTimeOffset (its .DateTime is taken) or an invariant-culture string
DateTimeOffset, DateOnly, TimeOnly, TimeSpan	invariant-culture strings
bool	true/false, the strings "true"/"false", or any number where non-zero is true
string	ToString()
anything else	Convert.ChangeType with invariant culture

All parsing is invariant culture, so a request behaves the same regardless of server locale.

A value that cannot represent the column's type at all — "abc" for an integer column — is not an error. TryCoerce returns false and the value is bound unchanged, so the comparison simply matches nothing. An unusable filter should return no rows, not a 500.

The EF Core provider does the equivalent against the property's CLR type, with one difference: a value it cannot convert causes the condition to be dropped rather than bound unchanged, because an expression tree has no way to express "compare an int column to the string 'abc'".

---

Sorting

SortColumns are applied in list order: the first is the primary key, the second breaks its ties, and so on. Unknown columns are dropped; if every sort column is dropped the result is unordered (subject to the paging note below).

Null placement — the standard

Engines disagree by default:

Engine	Default ascending	Default descending
PostgreSQL, Oracle	nulls last	nulls first
SQL Server, MySQL, SQLite	nulls first	nulls last

The same query therefore returned rows in a different order depending on the database. QueryForge pins it:

Nulls sort first ascending and last descending, on every provider and every engine.

How each provider achieves it:

• Dapper — the dialect emits an explicit NULLS FIRST / NULLS LAST where the engine's default differs (PostgreSQL, Oracle) and nothing where it already matches (SQL Server, MySQL, SQLite).
• EF Core — EF defers ORDER BY to the database and so inherited the same problem. The provider orders by an explicit null-rank key (col == null ? 0 : 1) before the real sort key, for nullable columns only.
• In-Memory — QueryValueComparer returns null as less than everything, and descending reverses that.

Ordering with no sort columns

A result with no usable sort column has no guaranteed order. On SQL Server and Oracle the paging clause requires an ORDER BY, so the dialect emits a placeholder (ORDER BY (SELECT NULL) on SQL Server) — this satisfies the syntax without imposing a meaningful order. Do not rely on it being stable across calls: for stable paging, always sort by something unique.

---

Paging

size   = Paging.Size   > 0 ? Paging.Size   : 12
number = Paging.Number > 0 ? Paging.Number : 1
offset = (number - 1) * size

• Non-positive values fall back to the defaults rather than throwing.
• Meta.Total.Pages is ceil(total / size).
• A page past the end returns an empty page with the true totals still reported — not an error.
• On a grouped query, size counts outermost groups, not rows.

Projection

SelectColumns narrows what comes back. Empty means everything.

• Dapper narrows the SELECT list, so unselected columns are never fetched.
• EF Core builds a Select with a MemberInit, so unselected columns are never fetched. Results are untracked, because EF Core does not track instances constructed inside a projection — which is what you want, since a partially populated entity must never be written back.
• In-Memory copies the object, leaving unselected properties at their default.

Three rules protect you from a projection doing something surprising:

1. Unknown column names are dropped. A selection naming only unknown columns is treated as no selection at all, and everything comes back — rather than blanking every row.
2. Grouping columns always survive the projection, in every provider, because the hierarchy is rebuilt from them after the rows come back. Dropping them would leave nothing to group by.
3. A model that cannot be shaped is returned whole. Shaping needs a parameterless constructor and settable properties; a model without them is left unprojected rather than throwing. A projection is a bandwidth optimization, and failing an entire query over one is the wrong trade.

Grouping

Summarized here, specified in full in Grouping and Hierarchies.

• Non-empty GroupByColumns switches Meta.Type to Grouped; Groups is populated and Models is empty.
• Levels nest outermost-first, in list order.
• Paging applies to the outermost level only. Meta.Total.Rows becomes the number of distinct outermost keys.
• A node holds either SubGroups or Items, never both.
• Count is the number of leaf rows beneath a node at any depth, not the number of direct children.
• null is a valid key and forms its own group.
• Group keys are ordered by that level's SortOrder, nulls first ascending and last descending.
• Leaf items keep the order SortColumns established.
• Unknown grouping columns are dropped. If every grouping column is unknown, the result falls back to flat.

---

Determinism guarantees

Given the same data and the same Query, every provider returns:

• the same set of rows,
• in the same order — provided SortColumns is total (see the note on unique sorts),
• with the same Meta.Total.Rows and Meta.Total.Pages,
• and, when grouped, the same tree with the same keys, order, counts and nesting.

The exceptions — string collation, Oracle's treatment of empty strings, and floating-point representation — are enumerated in Cross-Provider Parity.