Pagination with total row count in NHibernate

Question

I am trying to paginate a simple query using HQL, and retrieve the total row count as part of the same query.

My query is simple enough...

var members = UnitOfWork.CurrentSession.CreateQuery(@"
    select m
    from ListMember as m
    join fetch m.Individual as i") 
    .SetFirstResult(pageIndex*pageSize)
    .SetMaxResults(pageSize)
    .List<ListMember>();

The Individual is mapped as a many-to-one on the ListMember class. This works great. The pagination works as expected and generates the following Sql...

SELECT   TOP ( 10 /* @p0 */ ) DirPeerG1_1_0_,
                 Director1_0_1_,
                 Director2_1_0_,
                 Forename2_0_1_,    
                 Surname0_1_
FROM     (SELECT listmember0_.DirPeerGrpMemberID    as DirPeerG1_1_0_,
             listmember1_.DirectorKeyID         as Director1_0_1_,
             listmember0_.DirectorKeyId         as Director2_1_0_,
             listmember1_.Forename1             as Forename2_0_1_,
             listmember1_.Surname               as Surname0_1_,
             ROW_NUMBER()
               OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row
          FROM   tblMembers listmember0_
             inner join tblIndividuals listmember1_
               on listmember0_.DirectorKeyId = listmember1_.DirectorKeyID) as query
WHERE    query.__hibernate_sort_row > 10 /* @p1 */
ORDER BY query.__hibernate_sort_row

I read this article posted by Ayende called Paged data + Count(*) with NHibernate: The really easy way!, so I tried to implement it in my query.

I followed the steps in the article to add the custom HQL function called rowcount(), and changed my query to this...

var members = UnitOfWork.CurrentSession.CreateQuery(@"
    select m, rowcount()
    from ListMember as m
    join fetch m.Individual as i") 
    .SetFirstResult(pageIndex*pageSize)
    .SetMaxResults(pageSize)
    .List<ListMember>();

The Sql that is generated is almost correct, however it includes one of the columns twice resulting in this error...

System.Data.SqlClient.SqlException: The column '...' was specified multiple times for 'query'.

The Sql it generates looks like this...

SELECT   TOP ( 10 /* @p0 */ ) 
         col_0_0_, 
         col_1_0_, 
         Director1_0_1_, 
         DirPeerG1_1_0_, 
         Director1_0_1_, 
         Director2_1_0_, 
         Forename2_0_1_,    
         Surname0_1_
FROM     (SELECT 
      listmember0_.DirPeerGrpMemberID as col_0_0_, 
      count(*) over() as col_1_0_, 
      listmember1_.DirectorKeyID as Director1_0_1_, 
      listmember0_.DirPeerGrpMemberID as DirPeerG1_1_0_, 
      listmember1_.DirectorKeyID as Director1_0_1_, 
      listmember0_.DirectorKeyId as Director2_1_0_, 
      listmember1_.Forename1 as Forename2_0_1_, 
      listmember1_.Surname as Surname0_1_, 
             ROW_NUMBER()
               OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row
          FROM   RCMUser.dbo.tblDirPeerGrpMembers listmember0_
             inner join RCMAlpha.dbo.tblDirectorProfileDetails listmember1_
               on listmember0_.DirectorKeyId = listmember1_.DirectorKeyID) as query
WHERE    query.__hibernate_sort_row > 10 /* @p1 */
ORDER BY query.__hibernate_sort_row

For some reason it includes the Director1_0_1_ column twice in the projection, which causes this error. This Sql is frustratingly close to what I would like, and I’m hoping an NHibernate expert out there can help explain why this would happen.

Suggestions Tried

Thanks to the suggestion from @Jason . I tried it with the non-generic version of .List() method to execute the query but this unfortunately also produced the same Sql with the duplicate column...

var members = UnitOfWork.CurrentSession.CreateQuery(@"
    select m, rowcount()
    from ListMember as m
    join fetch m.Individual as i")
    .SetFirstResult(pageIndex * pageSize)
    .SetMaxResults(pageSize)
    .List()
    .Cast<Tuple<ListMember, int>>()
    .Select(x => x.First);

Update

It doesn't look like this is going to be possible without getting into the NH source code. My solution requirements have changed and I am no longer going to pursue the answer.

In summary, the solution would be to either...

• Use Futures or MultiQuery to execute two statements in a single command - one to retrieve the page of data and one the total row count.
• Modify your pagination solution to do without a total result count - Continuous scrolling for example.

Answer 1

Hmm, one issue is that you're using a ListMember-typed List method. In the example at the page you linked, he uses List() which returns a list of tuples. The first item of your tuple would be a ListMember and the second would be the row count. That List<> might affect your query and would probably throw an exception even if it did return.

Try using:

var tuples = UnitOfWork.CurrentSession.CreateQuery(@"
select m, rowcount()
from ListMember as m
join fetch m.Individual as i") 
.SetFirstResult(pageIndex*pageSize)
.SetMaxResults(pageSize)
.List();

var members = tuples.Select<Tuple<ListMember, int>, ListMember>(x => x.Item1);

but I kinda agree with @dotjoe. A MultiQuery might be easier. It's what I use. Here's a a good link about it from the same author you linked to before (Ayende).