Grouping and Hierarchies

Grouping and Hierarchies

Add GroupByColumns and a flat result becomes a nested key / count / items tree, with row counts at every level, computed by the engine rather than by your application code.

This page specifies exactly how that works — the algorithm, the guarantees, the performance profile, and the parts that surprise people.

---

The shape

public record HierarchyNode<TModel>(
    object? Key,
    int Count,
    IReadOnlyList<HierarchyNode<TModel>>? SubGroups,
    IReadOnlyList<TModel>? Items);

{
  "meta": { "total": { "rows": 2, "pages": 5 }, "type": "Grouped" },
  "groups": [
    { "key": "Canada", "count": 9, "subGroups": [
        { "key": "HR", "count": 5, "items": [ /* 5 rows */ ] },
        { "key": "IT", "count": 4, "items": [ /* 4 rows */ ] } ] },
    { "key": "Germany", "count": 12, "subGroups": [
        { "key": "IT", "count": 7, "items": [ /* 7 rows */ ] },
        { "key": "HR", "count": 5, "items": [ /* 5 rows */ ] } ] }
  ]
}

The contract

1. A node holds either SubGroups or Items, never both. Which one is decided purely by depth: the innermost grouping level carries Items, every other level carries SubGroups.
2. Count is the number of leaf rows beneath the node at any depth — not the number of direct children. Children's counts sum to their parent's.
3. Depth equals GroupByColumns.Count, after unknown columns have been dropped.
4. null is a valid key and forms its own group. It is not merged with empty strings, and it is not skipped.
5. Keys are ordered by that level's SortOrder, with nulls first ascending and last descending.
6. Leaf items keep the order SortColumns established. Grouping does not reshuffle rows within a group.
7. Key is the raw column value, not a string — a number stays a number, a date stays a date.

---

Paging applies to groups

This is the single most important thing to understand, and the most common surprise.

When a query is grouped, Paging slices the outermost group keys, not the rows. Meta.Total.Rows becomes the number of distinct outermost keys, and Meta.Total.Pages is ceil(that / size).

So "paging": { "size": 5 } on a query grouped by Country returns five countries — and every row belonging to those five countries.

Why every row comes back

Because Count is defined as the number of leaf rows beneath a node, and that cannot be known from a partially fetched set. If the provider fetched only 5 rows per country, it could not truthfully say a country has 40. Truthful counts require the rows.

The practical consequence: page size bounds how many groups come back, not how much data. Grouping by a low-cardinality column over a large table returns a large payload. Two ways to keep it bounded:

• Filter first. Criteria is applied before grouping, so a date range or status filter shrinks the row set the groups are built from.
• Group by something with more keys, so each group is smaller.

If what you actually want is "the top 5 rows in each country", that is a windowed query, which QueryForge does not model. Express it as a view or a table-valued function and query that instead.

Meta for a grouped query

Field	Value
Meta.Type	Grouped
Meta.Total.Rows	distinct outermost keys matching the filter
Meta.Total.Pages	ceil(keys / size)
Models	empty
Groups	the page of keys, each fully populated

---

The algorithm

All three providers follow the same three steps. Only step 1 and 2 differ in where they run.

1. Count the distinct outermost keys      → Meta.Total
2. Page the distinct outermost keys       → the keys on this page
3. Fetch every row whose outermost key is on that page
4. Hand those rows to HierarchyBuilder    → the tree

HierarchyBuilder is in the core package and is shared, so the tree is byte-identical regardless of how the rows were fetched. It:

• buckets rows by the level's key, in first-seen order;
• sorts the buckets explicitly by SortOrder using QueryValueComparer, so the result does not depend on dictionary enumeration order;
• recurses into each bucket for the next level;
• sets Count to the bucket's row count — which, because a bucket holds every row beneath it, is automatically the leaf count at any depth.

Null keys are handled by a small null-safe key wrapper, so null can be a dictionary key rather than being dropped.

Per provider

	Dapper	EF Core	In-Memory
Count keys	SELECT COUNT(*) FROM (SELECT DISTINCT key …) qf_groups	.Select(key).Distinct().CountAsync()	Distinct() over the filtered rows
Page keys	SELECT DISTINCT key … ORDER BY key … <paging>	.Distinct().OrderBy(nullRank).ThenBy(k).Skip().Take()	OrderBy + Skip/Take
Fetch rows	… WHERE (filter) AND key IN (@p0, @p1, …)	.Where(pagedKeys.Contains(key))	filter by a key set
Round trips	3	3	0 — one pass in memory

The Dapper key predicate handles nulls explicitly, because IN never matches NULL:

Keys on the page	Predicate
all non-null	key IN (@p0, @p1, …)
includes null	(key IN (@p0, …) OR key IS NULL)
only null	key IS NULL
none	1 = 0

---

Multiple levels

Levels nest outermost-first, in list order.

var query = QueryBuilder.New()
    .GroupBy(
        new GroupByDescriptor("Country",    SortOrder.Ascending),
        new GroupByDescriptor("Department", SortOrder.Ascending),
        new GroupByDescriptor("Role",       SortOrder.Descending))
    .Sort(new SortDescriptor("Score", SortOrder.Descending))
    .Page(size: 5, number: 1)
    .Build();

Produces Country → Department → Role → rows, four levels deep, where:

• Country and Department keys sort ascending, Role keys sort descending;
• paging slices Country only — 5 countries per page;
• within each Role node, rows are ordered by Score descending;
• Count on a Country node is every row in that country, across all its departments and roles.

There is no hard limit on depth. Practical depth is bounded by payload size, since every row under the paged outermost keys is returned.

---

Ordering, precisely

Two independent orderings are at work, and it is worth keeping them apart:

What	Controlled by	Applies to
The order of group keys at each level	that level's GroupByDescriptor.SortOrder	SubGroups / the top-level Groups list
The order of rows inside a leaf node	Query.SortColumns	Items

SortColumns does not affect group key order, and GroupByColumns.SortOrder does not affect row order.

Null keys follow the standard: first ascending, last descending, at every level, on every provider. That is enforced by QueryValueComparer in memory, by the dialect's NULLS FIRST / NULLS LAST in Dapper, and by an explicit null-rank ordering key in EF Core.

---

Projection and grouping

Grouping columns always survive SelectColumns, on every provider — the hierarchy is rebuilt from them after the rows come back, so dropping them would leave nothing to group by.

var query = QueryBuilder.New()
    .Select("UserId", "FirstName", "Score")           // Country is NOT selected
    .GroupBy(new GroupByDescriptor("Country"))
    .Build();

The Dapper provider adds Country to its SELECT list; EF Core adds it to the MemberInit; the in-memory shaper keeps it. Whether the returned models therefore carry a populated Country property depends on the provider: Dapper and EF Core materialize the column and so it is populated. This is a deliberate, documented consequence of asking for a grouped projection.

---

Degenerate cases

Case	Behaviour
GroupByColumns is empty	flat result — this is the normal path
Every grouping column is unknown	falls back to a flat result rather than failing
Some grouping columns are unknown	the unknown ones are dropped; the rest still nest
The filter matches nothing	Groups is empty, Meta.Total.Rows is 0
Paging past the last group	Groups is empty, totals still accurate
Every row has a null key	one group, key: null, holding everything

The Dapper compiler throws InvalidOperationException — "A grouped query needs at least one GroupByColumn naming a real column of the target object" — only if it is called for a grouped statement with no valid grouping column. The executor checks first and routes to the flat path, so you will not see this in normal use; it appears only when calling the compiler directly.

---

Data-grid mapping

QueryForge's hierarchy is shaped for the server-side grouping protocols the major grids use.

Grid concept	QueryForge
DevExtreme group in loadOptions	GroupByColumns, in order
DevExtreme group key / count / items	HierarchyNode.Key / .Count / .Items
AG Grid row-group groupKeys	one GroupByDescriptor per level
Kendo group descriptors	GroupByColumns, field → ColumnName, dir → SortOrder
Group-level paging	Paging on a grouped query

A thin frontend mapper is all that is needed; see Recipes.

---

Performance notes

• Grouped queries cost three round trips on Dapper and EF Core, against one for a flat query.
• The row-fetch statement is the expensive one. It returns every row under the paged keys, so filter before grouping.
• The IN list is bounded by page size — 5 groups means 5 parameters — so it never approaches a parameter-count limit under normal use.
• The tree build is one pass over the fetched rows per level, with dictionary bucketing.
• In-memory grouping is a single pass, no round trips, and is the fastest path by a wide margin for data you already hold.