How to build a tree in C# given just the child elements?

Answer 1

Disclaimer: As I have no experience with NHibernate, I'm going out on a limb here.

Ayende's blog post uses a join fetch statement, which is NHibernate-specific SQL, called HQL. Since NHibernate is just another layer on top of the database and SQL databases aren't designed to handle hierarchies, it would still require recursive queries to fetch the entire tree.

But Ayende also mentions in one of his comments that it's easier to query a tree, if both the direct and indirect relations are stored in the database. He refers to a structure like this:

Ancestor                      Descendant        IsChild
-------------------------------------------------------
Household cleaning Supplies   Air fresheners    True
Household cleaning Supplies   Paper Towels      True
Household cleaning Supplies   Car Airfreshner   False
Air fresheners                Car Airfreshner   True
Utensils                      Forks             True

Ayende uses a Level column that indicates the depth of a node in the tree, which could be used to limit the depth of the tree in the query. But IsChild will demonstrate the principle as well.

Finding ancestors

Given the descendant node Car Airfreshner, you can select all ancestor nodes in one query. You can reconstruct parent-child relations from these nodes by checking the IsChild value.

SELECT * FROM TreeRelations WHERE Descendant = 'Car Airfreshner'

Creating a tree

Given the ancestor node Household cleaning Supplies, you can build a tree starting from this node by selecting all the descendants in a single query. The IsChild value can be used to find parent-child relations.

SELECT * FROM TreeRelations WHERE Ancestor = 'Household cleaning Supplies'

Both queries combined

To get the entire tree for any given node, you can combine both queries to retrieve all ancestors and descendants and build a tree from that:

SELECT * FROM TreeRelations WHERE Descendant = 'Entry node' OR Ancestor = 'Entry node'