It's a girl

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:

SELECTC.*,

COUNT(*)OVER()AS TotalRows

FROMCustomers 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:

publicclassCustomFunctionsMsSql2005Dialect : MsSql2005Dialect

{

public CustomFunctionsMsSql2005Dialect()

{

RegisterFunction("rowcount", newNoArgSQLFunction("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: