Recently, while implementing a page-able data grid with nHibernate and MVC Contrib Grid, I came across a strange problem. My result set had duplicates, and the strange thing was that it would only happen when paging my record set. Anyway, I thought I would write a little post about how I solved the problem, just in case someone else comes across it.
Firstly, lets look at simplified description of the problem. Those using MySQL have the luxury of limit, which makes paging data sets a breeze, but in SQL Server(and Oracle) things get a bit more “tricky”. The trick is to count the rows on the result set, using RowCount over something, and mix that in with a sub query, but there is a problem with nHibernate T-SQL 2005 Dialect. The RowCount was being used on the sub-query, and not the parent query. Now that I had discovered the problem.
“ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row” is in the wrong place!
After some research, I found that other people were having this problem too. Marcin Daczkowski has an excellent work-around, that he blogged about, he also describes the problem with some NH generated SQL examples, I won’t repeat myself here, so have a look at his post if you are not sure and check his bug report here.
I found a another solution here too- not sure this one works though, ultimately I had to come up with a solution that suited my project.
There are some reasons why I can’t use Marcin Daczkowski solution.
– Firstly, it does not look like the guys at nHibernate be able to release the patched version of nHibernate any time soon,I guess they are very busy working hard on version 3.0! Can’t wait for that realease! see the comments here.
– Secondly, if I build my own version of nHibernate, I will need to also rebuild all my dependencies, linking them with Marcins patched version. That means FluentNHibernate needs to be rebuilt, NHibernate.Caches.SysCache needs to be rebuilt, NHibernate.ByteCode.Castle needs to be rebuilt, you get the picture?
So after some thought and source code investigation, I came up with the idea of making a customised Dialect, and just use Darcins patched files. Now, I don’t need to build a patched version of my all my open source dependencies, as I have my own SQL dialect set up in the fluent configuration.
_sessionFactory = Fluently.Configure()
.Database(MsSqlConfiguration
.MsSql2008
.ConnectionString(connectionString)
.CurrentSessionContext("web")
.Dialect(CustomSQL2008Dialect)
)
My Dialect classes are set up like this:
public class CustomSQL2008Dialect: CustomSQL2005Dialect {
public GWMsSql2008Dialect () {
// Duplicate of the contents of MsSsql2008Dialect constructor goes here
}
}
public class CustomSQL2005Dialect: MsSql2000Dialect {
// the contents Darcins MsSql2005Dialect file goes here.
// MsSql2005Dialect at NHibernate JIRA
}
Luckily, I have unit tests set up for all my repository methods, and after seeing the green bar in NUnit, I was more than satisfied with the custom dialect. I hope the guys at NHibernate manage to get things working in their next release, keep up the good work guys! And a special thanks to
to Darcin, for writing the patch.
Here is a copy the Custom Sql 2008 Dialect.