在 NHibernate 中使用临时表

Question

我正在尝试将 NHibernate 与 sql server 功能结合起来进行高级使用。
我正在使用 NHibernate 的 ICriteria 接口为我的列表视图提供分页、排序和过滤。 业务对象之一是来自 3 个不同表的项目的聚合。 为了在数据库中进行此聚合，我使用了一个接受参数的 transact-sql 函数。
我使用 session.GetNamedQuery 返回的 IQuery 接口来调用该函数。但为了使用分页/过滤/排序代码，我想使用 ICriteria 接口。 为了实现我考虑的目标：

1. 打开一个新事务
2. ，调用该函数，该函数将创建一个全局临时表（而不是像现在那样返回结果）
3. 以某种方式改变 NHibernate 映射，以便它适用于临时表（不确定我可以这样做，这也必须特定于我创建事务的范围...）
4. 使用新映射，使用 ICriteria 接口在新表上运行查询
5. 删除临时表

，所以有很多问题：

1. 你能建议吗另一种选择？
2. 是否可以在运行时本地替换 NHibernate 映射中的表以用于特定的代码范围？
3. 生成和处理临时表的成本有多大？

Answer 1

你能用视图代替函数吗？该视图可以聚合 3 个表，由 NHibernate 映射，并且可以轻松分页/排序/过滤。

Answer 2

我写了一篇博客文章，其中展示了如何在此处执行此操作： 使用 nhibernate 和 sql server 临时表进行性能密集型查询的分页

Answer 3

我在使用 SQL 2012 Dialect 的 Hibernate SQL 查询中使用表变量，如下所示。
对于我的场景，无论我如何构建过滤器，我都无法在 17 秒内获得特定的参数化分页查询。通过使用表变量，抓取页面大小为 1000 的页面 N 的时间导致亚秒级响应。

所有的魔力都发生在下面的 SQL 中：

1. 创建表变量 - 声明 @temporderstatus 表
2. 选择行放入表变量 - insert into @temporderstatus 由 sqlfilter 字符串中的 sql 过滤
3. 从表变量中选择数据返回到 NHibernate 排序，以便分页返回可预测的结果集 - select OrderNum, Customer... from @temporderstatus ORDER BY StatusCodeChangedDate, OrderNum
4. 使用 NHibernate 分页函数插入必要的 ROW/OFFSET 语句SQL Server 2012 方言 - SetFirstResult((pagination.Page - 1) * pagination.PageSize).SetMaxResults( pagination.PageSize )

与临时表不同，表变量会自行清理，因此非常理想对于 NHibernate 场景，您有一个正在处理分页请求的 Web 服务器。

这是我的代码...

            var session = _sessionManager.GetStatelessSession();
            StringBuilder sqlfilter = new StringBuilder(
                @"FROM Orders o join OrderType ot ON o.OrderType = ot.OrderType where o.StatusDate between :fromDate and :toDate" );
            var mainQuery = session.CreateSQLQuery(
                $@"declare @temporderstatus table (OrderNum int not null, CustomerID int,  OrderType varchar(16), Status varchar(3), StatusCodeChangedDate datetime, OrderDate datetime, DeliveryDate datetime)
      insert into @temporderstatus
      SELECT o.OrderNum, o.CustomerID, ot.Description AS OrderType, o.StatusCode AS Status, o.StatusCodeChangedDate, o.OrderDate, o.DeliveryDate 
      {sqlfilter}
      select OrderNum, CustomerID, OrderType, Status, StatusCodeChangedDate, OrderDate, DeliveryDate 
      from @temporderstatus  
      ORDER BY StatusCodeChangedDate, OrderNum 
      ");

       //construct the count query
       var totalCountQuery = session.CreateSQLQuery($"SELECT COUNT(1) OCount {sqlfilter} ");
            totalCountQuery.AddScalar("OCount", NHibernateUtil.Int32);
            totalCountQuery.SetParameter("fromDate", criteria.fromDate);
            totalCountQuery.SetParameter("toDate", criteria.toDate);
            var totalCountResults = totalCountQuery.UniqueResult<int>();
            pagination.TotalResultCount = totalCountResults;
            if (pagination.TotalResultCount == 0)
            {
                //no results so don't waste time doing another query
                return new List<OrderDataDto>();
            }

       //finish constructing the main query
            mainQuery.AddEntity(typeof(OrderDataDto));
            mainQuery.SetParameter("fromDate", criteria.fromDate);
            mainQuery.SetParameter("toDate", criteria.toDate);
            var mainQueryResults = mainQuery
                .SetFirstResult((pagination.Page - 1)*pagination.PageSize).SetMaxResults(pagination.PageSize);

            var results = mainQueryResults.List<OrderDataDto>();
            return results;