Fluent NHibernate - query hierarchy data from the bottom

Question

For example if I have a table like this

id  | parent_id
-----------------
1   | null
2   | 1
3   | 1
4   | 1
5   | 2
6   | 3
7   | 3
8   | 4
9   | 7
....

The hierarchy tree will look like this:

                           X1
                           |
 ------------------------------------------------------
 |                         |                          |
 X2                        X3                         X4
 |                         |                          |
 |                ------------------                  |
 |                |                |                  |
 X5               X6               X7                 X8 
                                   |
                                   X9

So, is there anyway that I can query using QueryOver or Criteria that when I input the id, it will return the list of all the parent_id (or parent object) from its parent to the top? For example:

input | result
-----------------------
1     | null
2     | [1]
3     | [1]
5     | [2, 1]
8     | [4, 1]
9     | [7, 3, 1]
....

==================

Working query (but have to call database twice)

var roleIds = session.CreateSQLQuery("SELECT Id FROM dbo.fn_get_parent_roles(:id)")
                .SetInt32("id", roleId)
                .List<int>();

Role x = null;
var query = session.QueryOver<Role>(() => x)
    .Where(Restrictions.In("x.RoleId", roleIds.ToArray()));

==================

Updated:

var hierarchyQuery = new SQLCriterion(
                new SqlString("{alias}.RoleId IN (SELECT Id FROM dbo.fn_get_parent_roles(:id))"),
                new object[] { roleId },
                new IType[] { NHibernateUtil.Int32 });
var results = session.QueryOver<Role>()
            .Where(hierarchyQuery)
            .List();

Then I got this message:

Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index

Here is full StackTrace

   at System.ThrowHelper.ThrowArgumentOutOfRangeException()
   at System.Collections.Generic.List`1.get_Item(Int32 index)
   at NHibernate.Criterion.SQLCriterion.ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary`2 enabledFilters)
   at NHibernate.Loader.Criteria.CriteriaQueryTranslator.GetWhereCondition(IDictionary`2 enabledFilters)
   at NHibernate.Loader.Criteria.CriteriaJoinWalker..ctor(IOuterJoinLoadable persister, CriteriaQueryTranslator translator, ISessionFactoryImplementor factory, ICriteria criteria, String rootEntityName, IDictionary`2 enabledFilters)
   at NHibernate.Loader.Criteria.CriteriaLoader..ctor(IOuterJoinLoadable persister, ISessionFactoryImplementor factory, CriteriaImpl rootCriteria, String rootEntityName, IDictionary`2 enabledFilters)
   at NHibernate.Impl.SessionImpl.List(CriteriaImpl criteria, IList results)
   at NHibernate.Impl.CriteriaImpl.List(IList results)
   at NHibernate.Impl.CriteriaImpl.List[T]()
   at NHibernate.Criterion.QueryOver`1.List()
   at NHibernate.Criterion.QueryOver`1.NHibernate.IQueryOver<TRoot>.List()
   at At2ClaimBO.Src.Repositories.UserRepo.GetUsers(String searchStr, Int32 roleId, Int32 page) in D:\Arunsawad\At2ClaimBO\At2ClaimBO\Src\Repositories\UserRepo.cs:line 85
   at At2ClaimBO.Src.Services.ClaimService.GetUsers(String searchStr, Int32 roleLevel, Int32 page) in D:\Arunsawad\At2ClaimBO\At2ClaimBO\Src\Services\ClaimService.cs:line 1326
   at At2ClaimBO.Controllers.AdminController.SearchHead(String search_str, String role_id, Int32 page) in D:\Arunsawad\At2ClaimBO\At2ClaimBO\Controllers\AdminController.cs:line 259
   at lambda_method(Closure , ControllerBase , Object[] )
   at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)
   at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary`2 parameters)
   at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary`2 parameters)
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass42.<BeginInvokeSynchronousActionMethod>b__41()
   at System.Web.Mvc.Async.AsyncResultWrapper.<>c__DisplayClass8`1.<BeginSynchronous>b__7(IAsyncResult _)
   at System.Web.Mvc.Async.AsyncResultWrapper.WrappedAsyncResult`1.End()
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult)
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass37.<>c__DisplayClass39.<BeginInvokeActionMethodWithFilters>b__33()
   at System.Web.Mvc.Async.AsyncControllerActionInvoker.<>c__DisplayClass4f.<InvokeActionMethodFilterAsynchronously>b__49()

Answer 1

NHibernate does not support this natively - you would have to use something like a table valued function and a SQL Query:

Assuming you are using MSSQL server, and a version which supports table valued functions (probably 2008 onwards?):

SQL function:

CREATE FUNCTION fnGetParents(@id INT)
    RETURNS @tbl TABLE (ID INT NOT NULL)
    AS
BEGIN       
    ;WITH t AS (
        SELECT ParentId AS Id
        FROM Hierarchy
        WHERE Id = @id
        UNION ALL
        SELECT ParentId
        FROM Hierarchy
        INNER JOIN t ON t.Id = Hierarchy.Id
    ) 
    INSERT INTO @tbl
    SELECT DISTINCT Id FROM t
    WHERE Id IS NOT NULL

    RETURN;
END 

and then to get the results:

session.CreateSQLQuery("SELECT Id FROM dbo.fnGetParents(:id)")
    .SetInt32("id", id)
    .List<int>();

If you want to include it in a Criteria or QueryOver function, you can use a SQLCriteria or SQLProjection, eg:

 var hierarchyQuery = new SQLCriterion(
                          new SqlString("{alias}.id IN (SELECT Id FROM dbo.fnGetParents(?))"),
                          new object[] {id}, 
                          new[] {NHibernateUtil.Int32});
 var results = session.QueryOver<Hierarchy>()
          .Where(hierarchyQuery)
          .List();