Ayende @ Rahien

My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:


+972 52-548-6969

, @ Q c

Posts: 6,131 | Comments: 45,567

filter by tags archive

Paged data + Count(*) with NHibernate: The really easy way!

time to read 15 min | 2877 words

Christian Maslen ping me about this article, which shows how to use NHibernate to execute multiply statements in a single round trip. Christian suggest a much neater solution:


        COUNT(*) OVER() AS TotalRows

FROM    Customers AS C

I was sure that it wouldn't work, but it does, and I consider this extremely cool. So, now I needed to figure out how to make NHibernate understand this. There are several options, but extending HQL is simplest one in this case.

NHibernate uses a dialect to let bridge the gap between Hibernate Query Language with is a database agnostics relational/object querying lanaguge. This allows NHibernate to work against multiply databases easily. The key here is that one of the extension points that NHibernate is offering is the ability to define your own custom functions, which can translate to arbitrary SQL.

In this case, here is the query that I want to end up with:

select b, rowcount() from Blog b

Here is the dialect extension:

public class CustomFunctionsMsSql2005Dialect : MsSql2005Dialect


       public CustomFunctionsMsSql2005Dialect()


              RegisterFunction("rowcount", new NoArgSQLFunction("count(*) over",

                     NHibernateUtil.Int32, true));



We register a new function, called rowcount, which translate to "count(*) over" string. The final "()" are added by NHibernate when rendering the function. Now, we need to register our new dialect:

<property name="hibernate.dialect">MyBlog.Console.CustomFunctionsMsSql2005Dialect, Blog.Console</property>

And here is the code we end up with:

IList list = session.CreateQuery("select b, rowcount() from Blog b")




foreach (object[] tuple in list)


       System.Console.WriteLine("Entity: {0}", ((Blog)tuple[0]).Id);

       System.Console.WriteLine("Row Count: {0}", (int)tuple[1]);


The generated SQL is:

WITH query AS (

     SELECT TOP 15 ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__, 

          blog0_.Id as Id4_,

          blog0_.Title as Title4_,

          blog0_.Subtitle as Subtitle4_,

          blog0_.AllowsComments as AllowsCo4_4_,

          blog0_.CreatedAt as CreatedAt4_,

          blog0_.Id as x0_0_,

          count(*) over() as x1_0_

     from Blogs blog0_)


WHERE __hibernate_row_nr__ > 5

ORDER BY __hibernate_row_nr__

Oh, and thanks for Fabio Maulo for helping me figure out the correct usage of custom functions.


Ayende Rahien

what is the full exception stack?

Comment preview

Comments have been closed on this topic.


  1. RavenDB Conference 2016–Slides - 11 hours from now
  2. Proposed solution to the low level interview question - about one day from now

There are posts all the way to Jun 02, 2016


  1. The design of RavenDB 4.0 (14):
    26 May 2016 - The client side
  2. RavenDB 3.5 whirl wind tour (14):
    25 May 2016 - Got anything to declare, ya smuggler?
  3. Tasks for the new comer (2):
    15 Apr 2016 - Quartz.NET with RavenDB
  4. Code through the looking glass (5):
    18 Mar 2016 - And a linear search to rule them
  5. Find the bug (8):
    29 Feb 2016 - When you can't rely on your own identity
View all series



Main feed Feed Stats
Comments feed   Comments Feed Stats