NHibernate 3 分页和确定总行数

Question

我在某处(不记得在哪里以及如何)读到 NHibernate 3 允许在执行分页查询(在一个数据库查询中)时确定记录总数。这是对的吗？

我有这个代码:

public IEnumerable<X> GetOrganisms(int PageSize, int Page, out int total)
{
    var query = (from e in Session.Query<X>() select e).AsQueryable();

    return query.Skip((Page - 1) * PageSize).Take(PageSize).ToList();
}

并希望尽可能有效地初始化 'total'。

谢谢。

基督徒

PS:

潜在的“解决方案”？:

Total = (int) Session.CreateCriteria<X>()
.SetProjection(Projections.RowCount())
.FutureValue<Int32>().Value;

var query = (from e in Session.Query<X>() select e).AsQueryable();

return query.Skip((Page - 1) * PageSize).Take(PageSize).ToList();

Answer 1

您的潜在解决方案将在一个事务中处理，但将是两个 db 调用。如果您必须只有一个 db 调用，则应按照对等建议使用多查询/ future 查询。有关 future 语法的更多信息，请查看此帖子:http://ayende.com/blog/3979/nhibernate-futures .

这里有几种方法可以完成你的场景......

QueryOver(2 db 调用):

var query = session.QueryOver<Organism>();
var result = query
    .Skip((Page - 1) * PageSize)
    .Take(PageSize)
    .List();
var rowcount = query.RowCount();

使用 100 个生物体的样本集，并查询 11-20 个生物体，以下是发送到数据库的两个查询:

SELECT TOP (@p0) Id0_0_, Title0_0_ FROM (SELECT this_.Id as Id0_0_, this_.Title as Title0_0_, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM Organism this_) as query WHERE query.__hibernate_sort_row > @p1 ORDER BY query.__hibernate_sort_row;@p0 = 10 [Type: Int32 (0)], @p1 = 10 [Type: Int32 (0)]
SELECT count(*) as y0_ FROM Organism this_

QueryOver (1 db call with Future):

var query = session.QueryOver<Organism>()
    .Skip((Page - 1) * PageSize)
    .Take(PageSize)
    .Future<Organism>();
var result = query.ToList();
var rowcount = session.QueryOver<Organism>()
    .Select(Projections.Count(Projections.Id()))
    .FutureValue<int>().Value;

查询与之前相同的数据集，这是生成的查询:

SELECT TOP (@p0) Id0_0_, Title0_0_ FROM (SELECT this_.Id as Id0_0_, this_.Title as Title0_0_, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM Organism this_) as query WHERE query.__hibernate_sort_row > @p1 ORDER BY query.__hibernate_sort_row;SELECT count(this_.Id) as y0_ FROM Organism this_;;@p0 = 10 [Type: Int32 (0)], @p1 = 10 [Type: Int32 (0)]

标准(1 db call with Future):

var criteria = session.CreateCriteria<Organism>()
    .SetFirstResult((Page - 1) * PageSize)
    .SetMaxResults(PageSize)
    .Future<Organism>();
var countCriteria = session.CreateCriteria<Organism>()
    .SetProjection(Projections.Count(Projections.Id()))
    .FutureValue<int>().Value;

再次，查询相同的数据集，在同一个查询中具有 future 结果的标准:

SELECT TOP (@p0) Id0_0_, Title0_0_ FROM (SELECT this_.Id as Id0_0_, this_.Title as Title0_0_, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM Organism this_) as query WHERE query.__hibernate_sort_row > @p1 ORDER BY query.__hibernate_sort_row;SELECT count(this_.Id) as y0_ FROM Organism this_;;@p0 = 10 [Type: Int32 (0)], @p1 = 10 [Type: Int32 (0)]

请注意，所有三种查询样式都会产生完全相同的查询。 future 的语法只允许 NHibernate 进行一次数据库调用，而不是两次。

如果您使用的是 NHibernate 3，我认为最优雅的处理方式是使用新的 QueryOver 语法。 (您在建议的解决方案中使用了旧的 NHibernate.Linq 语法。最好学习 QueryOver 语法。)