NHibernate IQuery to ISQLQuery Black Magic

Let me start with a warning. I really can’t recommend that you do what I am about to tell you. However the problems that led me to do something crazy are certainly worthy of discussion. And the solution that I employed itself is most definitely ‘interesting’ (that’s a euphemism)

We refactored our application this week to account for improved understanding of the domain. One of the changes was to take a flat structure and to turn it into a relational one (in both the domain and database) . Easy I thought. I was very wrong.

The first issue that I encountered was that using ICritera I could not make multiple joins to the same table. I converted the repository method to use IQuery. I no longer received runtime errors but the data that I found was wrong. This was because the wrong SQL was being generated. I posted on the forums and learned that: I’d run into a known bug / limitation.

Therefore I resorted to using ISQLQuery (yuk). I fixed several repository methods and then I ran into a very complicated IQuery. It was very painful to write the first time. In fact it was this experience that had caused me to change to using ICriteria as my default query method with specifications. I had a full suite of unit tests to protect me so I was confident that I could make the changes without breaking anything but I didn’t want to do the conversion manually. Surely there was a trick?

It turns out that there is – NHibernate.Hql.Classic.QueryTranslator. The property that I needed is protected but thanks to a quick search of the forums I didn’t even need to write my own reflection based method of calling it because someone had done it for me (thanks Nels). So what I did was take the HQL and convert it to SQL. I then did some string replacement to morph the SQL into what I needed. I am not proud of this but it was fast and it goes to show for flexible NHibernate is if you want to do some certifiable things.