NHibernate 3分页并确定总行数 [英] NHibernate 3 paging and determining the total number of rows

问题描述

我在某处(无法记住位置和方式)读到了NHibernate 3允许在执行分页查询(在一个数据库查询中)的同时确定记录总数.是这样吗?

I have read somewhere (can't remeber where and how) that NHibernate 3 allows the determination of the total number of records whilst performing a paged query (in one database query). Is this right?

我有此代码:

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

，并希望尽可能高效地初始化总计".

and would like to initialise 'total' as efficiently as possible.

谢谢.

基督徒

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

推荐答案

您的潜在解决方案将在一个事务中处理，但将被两个db调用.如果必须只进行一次数据库调用，则应使用对等建议的multiquery/future查询.有关未来语法的更多信息，请查看以下文章: http://ayende.com/blog/3979/nhibernate-futures .

Your potential solution will be handled in one transaction, but will be two db calls. If you must have only one db call, you should use a multiquery/future query as peer suggested. For more information on the future syntax, check out this post: http://ayende.com/blog/3979/nhibernate-futures.

有几种方法可以实现您的方案...

Here are a few ways to accomplish your scenario...

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

以100个生物的样本集为例，并查询11-20个生物，这是发送到数据库的两个查询:

With a sample set of 100 Organisms, and querying for the organisms 11-20, here are the two queries sent to the db:

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(与Future进行1个数据库调用):

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;

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

Querying for the same set of data as before, this is the query that is generated:

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进行1分贝通话):

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;

再次，在同一查询中查询同一组数据，具有未来结果的条件:

Again, querying for the same set of data, criteria with future results in the same query:

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)]

请注意，所有三种查询样式都会导致完全相同的查询.将来的语法仅允许NHibernate进行一次数据库调用，而不是两次.

Notice that all three query styles result in the same exact queries. The future syntax simply allows NHibernate to make one database call rather than two.

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

If you are using NHibernate 3, I think the most elegant way to handle this is using the new QueryOver syntax. (You used the old NHibernate.Linq syntax in your proposed solution. You are better off to learn the QueryOver syntax instead.)

这篇关于NHibernate 3分页并确定总行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！