Best way to get tree data in NHibernate

Question

I want to get hierarchical data / tree data, as shown below, from a table that has the following values.

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

Required query result. Data (no need to bookmark). If I chose "Work1", I have to fill in the identifiers that are under its root, as shown below. If I select "Work2", then I must also fill in the identifiers above and below my root.

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

What is the best way in NHibernate to get data in the above scenario in an optimized way.

Answer 1

, " ", . "" ? ( , ) SQL- ( ) ?

1: , ( , ). ( , - 200).

:

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

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

SQL-, . item (, , ..).

2: . .

NHibernate , , . N + 1, , 1 ( ).

, :

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:

3: . .

, , .

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;
}

N + 1, , NHibernate DB . x + y, x - , Work, y - ( ).