There are times, when you just think something should work, and one way or another it just doesn't. I've been having one of those weeks with NHibernate. I have been using MS SQL Server 2008 spatial searching, along with SQL Server Full text search, and using NHibernate for almost all my data access.

The only problem is that NHibernate out of the box doesn't like to play nicely with Spatial or Fulltext. After head scratching, head bashing, and general teeth grinding I figured better to cut my losses and ask some people that actually knew what they were doing.

Firstly I fired a twitter off in the direction of Ayende to get a feel for whether I was doing something wrong - with CreateSqlQuery NHibernate didn't want me to use the Spatial stuff - NHibernate uses a colon : to identify parameters, but in SQL 2008 Microsoft decided to use a double colon :: in it's spatial bits, so for example to declare a point you would use:

geography::STGeomFromText('POINT(51.520223 -0.094693 )', 4326)

That promptly causes NH to stop you from carrying on with a missing parameter. Ayende did indeed confirm it's not right, and after a few helpful suggestions he ran out of quick wins and suggested I might want to take advantage of his commercial support for NHibernate. I'm not adverse to such a solution, but felt this one might get complex, and Ayende's payment structure didn't fit my needs - this is a small project for a small client, and I could work around NH's limitations by using stored procedures (yes, I know, heresy).

After a bit more head scratching I remebered that iMeta also provide NH commecial support now, and having taken a look at their pages, I figured it would work much better for my limited budget.

After a  quick chat with Hadi Hariri, he directed me on to Steve Strong, the man who wrote a great deal of the Linq to NHibernate stuff, and a bit of an expert on the parser.

Steve ripped through my queries with lighning pace, and had me a viable solution that afternoon - now that's what I call customer service. Today Steve has continued to sort out niggles with the problem, and it looks like we may have identified a few bugs in NHibernate Spatial and possibly the parser, which Steve will now push back into the NH trunk for future geenrations to benefit from.

So, althought NHibernate is open source software, and in theory you can fix this stuff yourself, this is just a post to point out that sometimes the best use of your time and effort is in things you can deal with quickly, and sometimes it is worth paying some real specialists to help out - the cost saving could be immense. If you have problems with NHibernate, I encourage you to take a look at the offerings available, especially the iMeta one :)

Just because NHibernate is free - doesn't mean it isn't worth paying for!