Fluent NHibernate-从底部查询层次结构数据

本文关键字：层次结构 数据 查询 底部 NHibernate- Fluent | 更新日期: 2023-09-27 18:25:28

例如，如果我有一个类似的表

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

层次结构树如下所示：

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

那么，我是否可以使用QueryOver或Criteria查询，当我输入id时，它将返回从其父对象到顶部的所有parent_id（或父对象）的列表？例如：

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

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

工作查询（但必须调用数据库两次）

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()));

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

更新：

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();

然后我收到了这样的消息：

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

这是完整的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()

Fluent NHibernate-从底部查询层次结构数据

NHibernate本机不支持这一点-您必须使用类似于表值函数和SQL查询的东西：

假设您使用的是MSSQL服务器，以及支持表值函数的版本（可能是2008年以后的版本？）：

SQL函数：

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

然后得到结果：

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

如果你想把它包含在Criteria或QueryOver函数中，你可以使用SQLCriteria或者SQLProjection，例如：

 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();
相关文章：