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 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 Dialect.
Brilliant idea with the custom dialect override. This is clean and superior to rebuiling the sources obviously. I wish I thought about this a while ago, when we encountered the issue initially. I am going to update my blog post to reflect it. Thanks!
David, any chance you could post the entire set of classes for the custom dialect? I’m having some trouble getting the correct set of nhibernate source files pulled down for 3.0 and it does not appear to be fixed for 3.0 yet as I’ve encountered the same problem with the 3.0 binaries.
Hi Marc,
I have put a link to the file in the post above. Let me know how you get along?
David,
We have been using your dialect for NHibernate 2.0. It used to work well. Thanks a lot.
However, we updated our system to work on Nhibernate 3.0, and we cant get our project to build. It throws the following error:
Error 1 ‘NHibernate.SqlCommand.Parameter’ does not contain a definition for ‘OriginalPositionInQuery’ and no extension method ‘OriginalPositionInQuery’ accepting a first argument of type ‘NHibernate.SqlCommand.Parameter’ could be found (are you missing a using directive or an assembly reference?) C:TFSJOJOPortalBranchesKentuckyPersistenceCustomSql2008Dialect.cs 89 23 Persistence
Error 2 Warning as Error: ‘NHibernate.Dialect.CustomSql2005Dialect.ExtractColumnOrAliasNames(NHibernate.SqlCommand.SqlString, out System.Collections.Generic.List, out System.Collections.Generic.Dictionary)’ hides inherited member ‘NHibernate.Dialect.Dialect.ExtractColumnOrAliasNames(NHibernate.SqlCommand.SqlString, out System.Collections.Generic.List, out System.Collections.Generic.Dictionary)’. Use the new keyword if hiding was intended. C:TFSJOJOPortalBranchesKentuckyPersistenceCustomSql2008Dialect.cs 191 29 Persistence
Error 3 Warning as Error: ‘NHibernate.Dialect.CustomSql2005Dialect.QuotedAndParenthesisStringTokenizer’ hides inherited member ‘NHibernate.Dialect.Dialect.QuotedAndParenthesisStringTokenizer’. Use the new keyword if hiding was intended. C:TFSJOJOPortalBranchesKentuckyPersistenceCustomSql2008Dialect.cs 336 22 Persistence
Do you have a version of your dialect to work with NHibernate 3.0? I would really appreciate any help I can get.
Thanks
Ben Kojabash
Hi Ben,
I will look into this when I get a chance, to be honest, I was hoping they would fix this in NH 3.0! I posted on jira that they still have not solved the problem
.
Good news is that this has been assigned to a Julian, Lets hope he gets that sorted soon.
David,
I hope so… But I got your code working with NHibernate 3.0. It was just some renaming, and oop design changes they have made. Here are the updates I had to make to get it working:
1- Change “param.OriginalPositionInQuery = parameterPositon;” to “param.ParameterPosition = parameterPositon;”
2- Change “public class QuotedAndParenthesisStringTokenizer : IEnumerable” to “public new class QuotedAndParenthesisStringTokenizer : IEnumerable”
3- Change “private static void ExtractColumnOrAliasNames(SqlString select, out List columnsOrAliases,
out Dictionary aliasToColumn)” to “private new static void ExtractColumnOrAliasNames(SqlString select, out List columnsOrAliases,
out Dictionary aliasToColumn)”
Hi Ben,
Have you tried using the orginal class “QuotedAndParenthesisStringTokenizer” and method “ExtractColumnOrAliasNames” instead of overriding these?
My PC is packed away ATM as we are in the process of moving, so I have not had time to run tests on this and see what else may be effected.