Ayende @ Rahien

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

ayende@ayende.com

+972 52-548-6969

, @ Q c

Posts: 5,953 | Comments: 44,408

filter by tags archive

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


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.


Comments

Ayende Rahien

what is the full exception stack?

Comment preview

Comments have been closed on this topic.

FUTURE POSTS

No future posts left, oh my!

RECENT SERIES

  1. The RavenDB Comic Strip (3):
    28 May 2015 - Part III – High availability & sleeping soundly
  2. Special Offer (2):
    27 May 2015 - 29% discount for all our products
  3. RavenDB Sharding (3):
    22 May 2015 - Adding a new shard to an existing cluster, splitting the shard
  4. Challenge (45):
    28 Apr 2015 - What is the meaning of this change?
  5. Interview question (2):
    30 Mar 2015 - fix the index
View all series

Syndication

Main feed Feed Stats
Comments feed   Comments Feed Stats