Recursive AI

Query SQLite with a recursive CTE in C#

OneLite is a free, open-source SQLite-compatible database for .NET from Recursive AI. MIT or Apache-2.0, no native dependencies.

A recursive common table expression (CTE) walks parent/child rows in one SQL query.

Terminal
dotnet add package RecursiveAI.OneLite

Requires .NET 10 (net10.0); .NET 9 and earlier aren’t supported.

C#
using RecursiveAI.OneLite;
 
await using var db =
    await Database.OpenAsync(":memory:");
var names =
    await db.QueryAsync<string>($"""
    WITH RECURSIVE
    org(id, parent, name) AS (
      VALUES(1,NULL,'Root'),(2,1,'Sales'),
            (3,1,'Engineering'),
            (4,3,'Tools')
    ), tree(id, name, depth) AS (
      SELECT id, name, 0 FROM org
      WHERE id=1
      UNION ALL
      SELECT o.id, o.name, t.depth+1
      FROM org o JOIN tree t ON o.parent=t.id
    )
    SELECT depth || ':' || name FROM tree
    ORDER BY depth, id
    """);
foreach (var name in names)
    Console.WriteLine(name);
Text
0:Root
1:Sales
1:Engineering
2:Tools

The input is an acyclic hierarchy: Root has Sales and Engineering as children; Engineering has Tools. The snippet at the top defines those rows inline, so it needs no database file. It includes the root and orders results by depth, then ID. If a query fails, OneLite throws OneLiteException carrying the SQL error code and message.

The query methods require interpolated strings: values inserted with {value} become bound SQL parameters.

Unzip the example, then dotnet run.

Variations

Only the root and its direct children. Replace the final SELECT in the query above with:

SQL
SELECT depth || ':' || name FROM tree
WHERE depth<=1
ORDER BY depth, id
Text
0:Root
1:Sales
1:Engineering

This filters the returned rows; it does not stop the recursive walk at that depth.

The short answer

Use WITH RECURSIVE with an anchor SELECT for the starting node and a recursive SELECT that joins children to the previous step. Run the statement through OneLite’s QueryAsync<T> from C#. The example returns a root, its children and a grandchild; the final ORDER BY depth, id makes the printed order explicit. This WITH RECURSIVE query is standard SQLite SQL and returns the same results through Microsoft.Data.Sqlite; see the adapter documentation to run OneLite underneath it.

This is a hierarchy query, not a loop of database calls from C#. org defines the input rows; tree defines the growing result. OneLite’s engine documentation describes recursive CTEs as streaming through a queue, with UNION or UNION ALL and LIMIT/OFFSET in the recursive SELECT. Its MATERIALIZED and NOT MATERIALIZED hints are accepted and ignored.

How do you write a SQLite recursive CTE example?

Start by choosing what a row in the recursive result means. Here it contains the node ID, its name and its depth below the chosen root. The anchor assigns depth zero. The recursive member follows o.parent=t.id and increments the depth. The outer SELECT formats those results for the console; it does not control which children enter the walk.

In a real table, use a unique node ID and a parent column with the same ID convention. A NULL parent identifies a root, but this example starts from a particular ID rather than every NULL parent. A missing root returns no rows. A child pointing at a nonexistent parent will not be reached from this root. Decide whether that is acceptable for your data before using the query as a complete inventory.

The SQLite WITH documentation explains the anchor-and-recursive-member structure.

What is the difference between WITH and WITH RECURSIVE?

An ordinary CTE names a query result for use by the rest of the statement. A recursive CTE also refers to itself to produce another step. In this example, org is ordinary and tree is recursive, even though both appear in the same WITH RECURSIVE clause. The keyword does not make every CTE in the clause recursive; see the WITH overview.

Keep this UNION ALL example acyclic. Each generated row is retained, including a repeat reached by another path. Switching to UNION removes duplicate complete rows, not duplicate IDs alone. Because depth changes at each step, a cycle can still produce different rows indefinitely. Validate the parent links or use a deliberately bounded traversal for data that might contain cycles.

How do you order SQLite recursive CTE results?

Put the display order on the outer SELECT. Here depth groups the root before its children, and ID breaks ties between siblings. That is a level-oriented display, not an alphabetical sort and not a promise about internal queue order.

The variation filters displayed depth after traversal. It is appropriate when you want fewer output rows from a known finite hierarchy. It is not a cycle guard: filtering the final result does not prevent the recursive member from generating more rows. Keep termination and presentation as separate decisions.

All onelite recipes