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,124 | Comments: 45,475

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 3.5 whirl wind tour: You want all the data, you can’t handle all the data - 10 hours from now
  2. The design of RavenDB 4.0: Making Lucene reliable - about one day from now
  3. RavenDB 3.5 whirl wind tour: I’ll find who is taking my I/O bandwidth and they SHALL pay - 2 days from now
  4. The design of RavenDB 4.0: Physically segregating collections - 3 days from now
  5. RavenDB 3.5 Whirlwind tour: I need to be free to explore my data - 4 days from now

And 14 more posts are pending...

There are posts all the way to May 30, 2016


  1. RavenDB 3.5 whirl wind tour (14):
    29 Apr 2016 - A large cluster goes into a bar and order N^2 drinks
  2. The design of RavenDB 4.0 (13):
    28 Apr 2016 - The implications of the blittable format
  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