Best way to fetch tree data in NHibernate

Question

I want to fetch Hierarchical/Tree data something like below from a Table which has following definiton.

Tree Table:
"""""""""""
Id   |ParentId
"""""""""""
Work1|null
Work2|Work1
Work3|Work2
...

Required Query result Data (no need to be tabbed)- If I Pick 'Work1' I should complete Ids which are under its root something like below. If I pick 'Work2' then also I should complete Ids above and below its root.

> Work1 
----------
>   Work2
----------
>     Work3
---------

What is the best way in NHibernate to fetch data in the above scenario in optimized manner.

Answer 1

To find out what the "best way" is, more information regarding the actual scenario would be needed. What kind of "optimization" are you looking for? Minimal amount of data (only the rows you are really going to need) or minimal number of SQL queries (preferably one roundtrip to the database) or any other?

Scenario 1: Menu or tree structure that is loaded once and kept in memory for longer periods of time (not a list that updates every few seconds). Small number of rows in the table (small is relative but I'd say anything below 200).

In this case I would just get the whole table with one query like this:

var items = session.Query<Work>()
    .Fetch(c => c.ParentWork)
    .Fetch(c => c.ChildWorks).ToList();

var item = session.Get<Work>(id);

This will result in a single SQL query which simply loads all the rows from the table. item will contain the complete tree (parents, grandparents, children, etc.).

Scenario 2: Large number of rows and only a fraction of rows needed. Only few levels in the hierarchy are to be expected.

In this case, just load the item and let NHibernate to the rest with lazy loading or force it to load everything by writing a recursive method to traverse parents and children. This will cause a N+1 select, which may or may not be slower than scenario 1 (depending on your data).

Here is a quick hack demonstrating this:

var item = session.Get<Work>(id);

Work parent = item.ParentWork;
Work root = item;
// find the root item
while (parent != null)
{
    root = parent;
    parent = parent.ParentWork;
}
// scan the whole tree
this.ScanChildren(root);
// -----
private void ScanChildren(Work item)
{
    if (item == null)
    {
        return;
    }

    foreach (Work child in item.ChildWorks)
    {
        string name = child.Name;
        this.ScanChildren(child);
    }
}

Edit:

Scenario 3: Huge amount of data. Minimal number of queries and minimal amount of data.

In this case, I would think not of a tree structure but of having layers of data that we load one after another.

var work = repo.Session.Get<Work>(id);

// get root of that Work
Work parent = work.ParentWork;
Work root = work;
while (parent != null)
{
    root = parent;
    parent = parent.ParentWork;
}

// Get all the Works for each level
IList<Work> worksAll = new List<Work>() { root };
IList<Work> worksPerLevel = new List<Work>() { root };

// get each level until we don't have any more Works in the next level
int count = worksPerLevel.Count;
while (count > 0)
{
    worksPerLevel = this.GetChildren(session, worksPerLevel);
    // add the Works to our list of all Works
    worksPerLevel.ForEach(c => worksAll.Add(c));
    count = worksPerLevel.Count;
}

// here you can get the names of the Works or whatever
foreach (Work c in worksAll)
{
    string s = c.Name;
}

// this methods gets the Works in the next level and returns them
private IList<Work> GetChildren(ISession session, IList<Work> worksPerLevel)
{
    IList<Work> result = new List<Work>();
    // get the IDs for the works in this level
    IList<int> ids = worksPerLevel.Select(c => c.Id).ToList();

    // use a WHERE IN clause do get the Works 
    // with the ParentId of Works in the current level
    result = session.QueryOver<Work>()
        .Where(
            NHibernate.Criterion.Restrictions.InG<int>(
                NHibernate.Criterion.Projections.Property<Work>(
                    c => c.ParentWork.Id),
                ids)
        )
        .Fetch(c => c.ChildWorks).Eager // this will prevent the N+1 problem
        .List();

    return result;
}

This solution will not cause a N+1 problem, because we use an eager load for the children, so NHibernate will know the state of the child lists and not hit the DB again. You will only get x+y selects, where x is the number of selects to find the root Work and y is the number of levels (max depth of he tree).