OrmLite v10.4 adds typed recursive, window and combined queries
ServiceStack v10.4 brings typed support to OrmLite for recursive hierarchies, named sub queries, window functions, set operations and keyset paging. These queries used to need hand-written SQL, and now they stay parameterized and composable with your existing filters and joins. The post walks through each feature with examples and the SQL they generate.
Key points
- Use WithRecursive(seed, recurse) to walk hierarchies in one query; maxDepth and detectCycles handle depth limits and cyclic data
- Name a sub query with With<T>() and reuse it as a typed table in the same query
- TopPerGroup(x => x.Customer, take: 3) returns each customer's 3 latest orders in one expression
- Union, Intersect and Except merge query parameters automatically; Firebird does not support INTERSECT or EXCEPT
- SeekAfter() needs the ORDER BY to end with a unique column such as the primary key
- Stream large results with SelectLazyAsync() and await foreach; leaving the loop closes the reader right away
ServiceStack v10.4 extends OrmLite with typed APIs for query shapes that usually force developers back into raw SQL. WithRecursive() covers hierarchies such as category trees and org charts, With() names a sub query as a common table expression, and window functions handle rankings and running totals. TopPerGroup() returns the first rows of each group, which covers cases like a customer's latest orders.
The release also adds Union, Intersect and Except for combining typed queries, and SeekAfter() for keyset pagination that stays fast at any depth. Large results can be streamed with SelectLazyAsync(). The features work with existing filters, joins and paging, and run on several databases, though Firebird lacks INTERSECT and EXCEPT and throws a NotSupportedException. The original post has the full examples and generated SQL.
Real-world use
- An ASP.NET Core API that walks category trees or org charts
- A reporting screen that lists each customer's latest orders or top rows per group
- An infinite-scroll or sync endpoint that needs fast deep paging with keyset pagination
- A background export job that streams a large table without loading it into memory
Why it matters
Hierarchies, window functions and keyset paging can now stay in typed OrmLite code instead of hand-written SQL strings. Check database support and the generated SQL for your provider before relying on them in production.