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:

SELECT  C.*,

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

              .SetFirstResult(5)

              .SetMaxResults(10)

              .List();

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

SELECT * FROM query

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.

Tweet Share Share 1 comments
Tags: