---
title: "Query SQLite with a recursive CTE in C#"
url: https://recursiveai.org/onelite/cookbook/query-sqlite-with-recursive-cte/
description: "Traverse an acyclic parent/child hierarchy using WITH RECURSIVE in OneLite, with ordered C# results and a runnable .NET example."
version: "1.0.0"
---

# 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.

[NuGet](https://www.nuget.org/packages/RecursiveAI.OneLite) · [GitHub](https://github.com/recursive-ai-oss/onelite-csharp) · [Docs](https://recursiveai.org/onelite/docs/) · [Download example (.zip)](https://recursiveai.org/onelite/cookbook/query-sqlite-with-recursive-cte/query-sqlite-with-recursive-cte.zip)

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

```shell
dotnet add package RecursiveAI.OneLite
```

Requires [.NET 10](https://dotnet.microsoft.com/download/dotnet/10.0) (net10.0); .NET 9 and earlier aren’t supported.

```csharp
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](https://recursiveai.org/onelite/cookbook/query-sqlite-with-recursive-cte/query-sqlite-with-recursive-cte.zip), 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.

## Related recipes

- [Calculate a SQLite running total in C#](https://recursiveai.org/onelite/cookbook/sqlite-running-total-window-function/)
- [Query SQLite R*Tree bounding boxes in C#](https://recursiveai.org/onelite/cookbook/search-spatial-data-with-sqlite-rtree/)

## 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](https://recursiveai.org/onelite/docs/adapter/) 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](https://recursiveai.org/onelite/docs/engine/#subqueries-and-ctes) 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](https://www.sqlite.org/lang_with.html#recursivecte) 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](https://www.sqlite.org/lang_with.html).

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.
