Pagination with total row count in NHibernate

Question

I am trying to split a simple HQL query and retrieve the total number of rows as part of the same query.

My request is quite simple ...

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

Individual is displayed as multi-valued in the ListMember class. This works great. 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: a really easy way! so I tried to implement it in my request.

I followed the steps in the article to add a 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 leading to this error twice ...

System.Data.SqlClient.SqlException: The column "..." was specified several times for the query.

The created Sql 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 disappointing close to what I would like, and Im hoping the NHibernate expert there can help explain why this will happen.

Offers tried

Thanks to the suggestion from @Jason. I tried this with a minor version of the .List () method to execute a query, but this, unfortunately, also led to the same Sql with a 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 would be possible without going to the NH source code. My decision requirements have changed, and I will no longer be responsible for the answer.

So the solution will be either ...

• Use Futures or MultiQuery to execute two statements in the same command: one to retrieve a data page and one common row counter.
• Change your pagination solution to do without a total number of results. For example, continuous scrolling.

Answer 1

Hm, one problem is that you are using List List List List. In the example on the page you linked, it uses List (), which returns a list of tuples. The first element of your tuple will be ListMember, and the second is the number of rows. This list <> may affect your request and will probably throw an exception even if it returns.

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 agree with @dotjoe. MultiQuery could be simpler. This is what I use. Here's a good link about this from the same author you contacted earlier (Ayende).