Combining NHibernate, NH Spatial and SQL Server 2008 Fulltext Queries

Following Kevin Pang’s request on my last post regarding my problems getting NHibernate, NH Spatial and SQL Server Fulltext queries to work, I am finally getting around to giving some tips and pointers, and the code that Steve Strong of iMeta knocked up for me.

I needed to get these things working together as I was writing a recruitment website, and they had two basic requirements, the ability to search in candidates CVs and job descriptions for keywords, and the ability to search for those things within a certain distance – so an obvious search would be “find me all the C# developers within 10 miles of Central London”

Step One – NHibernate

Firstly, I’m not dumb, and at the beginning of the project I had decided to use NHibernate for all data access. The site didn’t have much of a domain model, but what it did have was ideally suited to a relational DB and simple persistence and querying by an ORM like NHibernate. What it also had going in it’s favour was the 2nd level caching capabilities of NHibernate, which would significantly reduce the workload on a site that was expected to grow quickly (or at least the owners hoped it would)

Win for NHibernate there – the icing on the cake was the excellent NHProfiler which would prove to make development a breeze over other options, and which also proved to make my querying a lot smarter and more bug free than my first horrid attempts.

Step Two – Spatial Searching

MS SQL Server 2008 introduced Spatial searching to it’s list of features – in fact it’s not just for searching, and not just by distance either, it can do a host of clever geographical type calculations and querying.

My first tests were done with “old fashioned” T-SQL and within a short while I had a reasonable distance search working, roughly speaking it looked like:

SET @from = geography::STGeomFromText('POINT(-0.094693 51.520223)', 4326); -- EC2M (London

SELECT * FROM JobSlot j
WHERE j.Location.STDistance(@from)<=(@distance * 1609.344)

Step Three – Fulltext Searching

Long ago, back in the age before the dawn of time, MS SQL Server had Fulltext searching, via the FREETEXT and CONTAINS syntax. Give it a keyword, or a list of them, and it will dig inside your text to find just what you want.

To use it on the site I was writing, we needed to index the content of CVs (resumes to your foreigners!) and some of those were in docx format – so my first step was to download the Microsoft Office 2007 iFilters. SQL Server uses the (now) age old technology of iFilters to extract the text from documents, and this filter pack allows it to look inside Office 2007 documents – if you want to index any other weird document formats, or even your own document format, there is probably an iFilter available, or you can write your own. With the iFilters installed, SQL Server could now index all of my content just fine.

SELECT *
FROM Candidate
WHERE FREETEXT(*, N'c#')

Combining It All

So far, so good. SQL Server can do pretty much all I need it to do, and combining those two queries is simplicity in TSQL, but as I was using NHibernate for all my data access, I wanted to try an get it all working there too.

NHibernate has support for the Spatial features of SQL Server 2008, along with other spatial databases, via the NHSpatial stuff in the NH Contrib project at http://nhcontrib.svn.sourceforge.net/svnroot/nhcontrib/

A download and compile of this will have you making HQL queries to do similar things to my TSQL spatial query above, in fact, the HQL query is remarkably similar to the TSQL:

select j from JobSlot j where NHSP.Distance(j.Loc, :point) <= (:distance * 1609.344)

Then I needed to get the Fulltext part working, after some chatting with Steve Strong, he came up with the following code:

            WKTReader wtk = new WKTReader();
            IGeometry point = wtk.Read("POINT( -0.123356 51.524142 )");
            point.SRID = 4326;

            string query =
                @"select j from JobSlot j
                  from freetexttable('JobSlot', :keywords) ftt
                  where NHSP.Distance(j.Loc, :point) <= (:distance * 1609.344)";

            var results = session.CreateQuery(query)
                .SetParameter("point", point, SpatialDialect.GeometryTypeOf(session))
                .SetParameter("distance", 1)
                .SetParameter("keywords", "hello world")
                .SetFirstResult(5)
                .SetMaxResults(10)
                .List();

And to get this to work we need 2 more classes, though the FreeText class is more an example of how to create an ISqlFunction for NHibernate:

	public class MyDialect : MsSql2008GeographyDialect
	{
		public MyDialect()
		{
			RegisterFunction("freetext", new FreeText());
			RegisterFunction("freetexttable", new StandardSQLFunction("freetexttable", null));
			RegisterFunction("contains", new StandardSQLFunction("contains", null));
		}
	}

	public class FreeText : ISQLFunction
	{
		public bool HasArguments
		{
			get { return true; }
		}

		public bool HasParenthesesIfNoArguments
		{
			get { return true; }
		}

		public virtual SqlString Render(IList args, ISessionFactoryImplementor factory)
		{
			SqlStringBuilder builder = new SqlStringBuilder();
			builder.Add("freetext(");

			if (args.Count == 1)
			{
				builder.Add("*, ");
				builder.AddObject(args[0]);
			}
			else
			{
				builder.AddObject(args[0]);
				builder.Add(", ");
				builder.AddObject(args[1]);
			}

			builder.Add(")");

			return builder.ToSqlString();
		}

		public virtual IType ReturnType(IType columnType, IMapping mapping)
		{
			return null;
		}
	}

Why I’m Not Using The Above Code

Yep, in my usual fashion I go through explaining all of that, just to say, I’m not doing it that way.

Eventually, I went back to another approach, that of using TSQL dynamically constructed in my code. I did this for a number of reasons, but primarily as it was easier for me to tune the query and add more options and variants in old fashioned SQL than in HQL by adding more extensions on to NHibernate. It also left the code base in a much easier to follow and extend state for the developers who would be maintaining the project – with the amount of investigation it took me to get this far, I figured extending it could be a nightmare for my successors.

The benefits I got from NHibernate I still retained, as this is a search function it was returning DTOs anyway, not entities, and these were just as efficient to read with a DataReader as with NHibernate (in fact a lot easier). When one of the results from the search is selected, the item is retrieved by NHibernate and we are back in ORM land.

It was also little effort to cache my SQL query results, so I also had all that NH was offering me in that respect too – a simple hash of the query parameters allowed me to cache the results object.

So there ya go, how to use Spatial and Fulltext with NHibernate, and why I didn’t do it! :)


Posted 11-22-2009 10:42 PM by Jak Charlton

[Advertisement]

Comments

prada shoes wrote re: Combining NHibernate, NH Spatial and SQL Server 2008 Fulltext Queries
on 01-08-2010 1:55 PM

I am glad to make a comment on this blog. Hope you can comment on my blog too.. thanks

prada shoes

About The CodeBetter.Com Blog Network
CodeBetter.Com FAQ

Our Mission

Advertisers should contact Brendan

Subscribe
Google Reader or Homepage

del.icio.us CodeBetter.com Latest Items
Add to My Yahoo!
Subscribe with Bloglines
Subscribe in NewsGator Online
Subscribe with myFeedster
Add to My AOL
Furl CodeBetter.com Latest Items
Subscribe in Rojo

Member Projects
DimeCasts.Net - Derik Whittaker

Friends of Devlicio.us
Red-Gate Tools For SQL and .NET

NDepend

SlickEdit
 
SmartInspect .NET Logging
NGEDIT: ViEmu and Codekana
LiteAccounting.Com
DevExpress
Fixx
NHibernate Profiler
Unfuddle
Balsamiq Mockups
Scrumy
JetBrains - ReSharper
Umbraco
NServiceBus
RavenDb
Web Sequence Diagrams
Ducksboard<-- NEW Friend!

 



Site Copyright © 2007 CodeBetter.Com
Content Copyright Individual Bloggers

 

Community Server (Commercial Edition)