Jak Charlton - Insane World : C#

Combining NHibernate, NH Spatial and SQL Server 2008 Fulltext Queries

Jak Charlton — Sun, 22 Nov 2009 22:42:00 GMT

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! :)

Nasty "Gotcha" with ASP.NET MVC and Views

Jak Charlton — Thu, 04 Sep 2008 08:27:00 GMT

We just spent far too long on what turned out to be a stupid problem ... we kept getting an error telling us that the view did not inherit from System.Web.UI.Page ... unfortunately the guy who was working on the particular view and controller could not think of a specific change he had made to cause this error, and looking at the code didn't reveal one either.

The problem was actually quite subtle, and quite stupid ...

A short while before the developer had decided to change the view from being an ".aspx" to being a ".ascx", so removed the view from the solution, added a new view that was a UserControl, and carried on working. What he hadn't done was to actually delete the .aspx file, and even if he had done that, the other developers would have got this weird error too unless they pulled a clean copy of the solution, or manually deleted the files from their disk. (clarification: El Guapo pointed out I was being ambiguous here ... if the file is *deleted* from the *solution* then it will go from disk and from SCM, if if is removed from the solution and then deleted it will remain in other developer's directories *if* a VS addin like Ankh is being used, as the Update will operate against the solution and referenced files, not the folder structure)

When ASP.NET MVC is told to render a view, by using RenderPartial("MyView") or return ("MyView") for example, it goes off merrily and scans the file system for a file matching "MyView.ascx" or "MyView.aspx" (presuming the default ViewEngine) - however if the view exists on disk in both forms, it is a bit of luck as to which actually gets returned and is rendered. Removing the old file fromn the solution is not enough, it still exists on disk - delete the old one and all is well. Hopefully this is a fairly infrequent problem, but it was a real pain to figure out.

So if you get an odd problem in ASP.NET MVC relating to Views ... just make sure you haven't got old files in your directory structure!

Logging with Castle Windsor, the Logging Facility and log4net

Jak Charlton — Wed, 18 Jun 2008 18:58:00 GMT

A discussion on the altdotnet list just came up around logging. After some various suggestions, I asked what was wrong with using optional dependencies and the built in logging facility in Castle Windsor. It seemed the most obvious answer

Of course, I completely forgot that when I first tried to figure this one out, it had me a little stumped and I took a while to find a good example of it. So here is how it happens.

Optional Dependencies in Windsor

When you ask the Windsor container for an instance of a class, it goes off and merrily tries to resolve all of the dependencies your class has. Firstly the constructor parameters, Windsor must find a constructor that it can provide all of the dependencies for - constructor parameters represent non-optional dependencies, they must be able to be fulfilled or Windsor will not give you an instance of the class back. You can have multiple constructors of course, and in this case Windsor will try and find the best match it can.

This lead to one of the early suggestions on the thread, putting the instance of the Logger as a parameter on the class constructor. To me this is a horrible idea, logging, along with things like security and auditing are cross cutting concerns, the class really shouldn't have to care about them - their behaviour lives elsewhere.

But Windsor has a really simple solution to this - optional dependencies. While Windsor must be able to fulfill at least one constructor signature, it will also try and resolve any public properties it can find on the class. These public properties may or may not be set by Windsor, if it has a suitable match for a property it will set it, otherwise it will ignore it.

So the first part of the solution to the problem is to put a public property on the class with the logger interface - now Windsor will populate this property if it has a match in the container.

The Logging Facility

Next is the magic bit, Castle already has simple integration for logging in the form of the Logging Facility.

While you could just put your own class, that implemented your own logging, and passed this onto log4net, by using the Castle faciltiy you save a lot of messing around, and get a great deal of flexibility. The Castle facility supports:

log4net (requires Castle.Services.Logging.Log4netIntegration.dll)
NLog (requires Castle.Services.Logging.NLogIntegration.dll)
ConsoleLogger
DiagnosticsLogger
StreamLogger
WebLogger (TraceContext)
NullLogger (used as placeholder)

Of course in the thread, log4net was the target of choice, but Castle lets you switch this simply, and maintain a single interface.

How To Tie it Together

1) Any class you are going to resolve from the Windsor container that you want to have logging on, add a public property of ILogger:

public class MyClass
{
   private ILogger logger;

   public ILogger Logger
   {
      get
      {
         if (logger == null) logger = NullLogger.Instance;
         return logger;
      }
      set { logger = value; }
   }
}

A key thing here is that we use the NullLogger as the default value - if the facility wasn't put into the container, or if the class was not instantiated via Windsor, then this will mean that logging just doesn't happen, rather than it creating an exception. The NullLogger just swallows the message and carries on.

2) Setup the Logging Facility in the Windsor container

In the XML configuration:

<?xml version="1.0" encoding="utf-8" ?> 
<configuration>

<facility 
    id="logging"
    type="Castle.Facilities.Logging.LoggingFacility, Castle.Facilities.Logging"
    loggingApi="null|console|diagnostics|web|nlog|log4net|custom"
    customLoggerFactory="type name that implements ILoggerFactory"
    configFile="optional config file location"    />

</configuration>

Or in the code intialization:

   container.AddFacility(
     "logging", 
     new LoggingFacility(LoggerImplementation.Console));

3) Use the Logger instance from the class:

   Logger.Debug("Null shipment returned");

That is all there is to it, any object that is resolved via Windsor will now be inspected for ILogger, and if found it will get an instance of the chosen logger. A simple solution, and a perfect example of why an IoC container can simplify your code.

Always One Step Ahead - Unit Test for Missing Attributes

Jak Charlton — Tue, 06 May 2008 08:38:00 GMT

Oren always amazes me - I think he is psychic! Whenever I have a problem, I almost always find Oren blogged about it a few days before - and today's bugbear is a perfect example.

I started the day off with a really weird timeout exception in WCF. The code was just fine apparently, and I spent a very long time debugging code I was pretty sure was working. The error (in typical framework style) was not able to be caught, debugged, or otherwise detected - it just "happened" somewhere in the WCF call.

To cut a long story short ... it turned out an attribute was missing on a DTO class, and instead of WCF giving me a meaningful error, it just gave me a worthless message. One addition of [DataContract] later and all was working fine again.

So, it just happens that Oren (in association with Glenn) had done something similar, and so taking a leaf out of their book, here is a really basic unit test to check that the DTO classes have the right attributes. Some discussion on Oren's blog discussed whether unit tests were the right place for this kind of checking (as it is pretty much static code analysis), and in some ways I can see their point - however, frankly, a unit test is just as valid a way of testing the same thing, fits nicely into CI, and is in a common format to all the other checks.

[Test]
public void Ensure_DTO_classes_have_WCF_attributes()
{
  foreach (var type in typeof(ClassInDTOAssembly).Assembly.GetTypes())
  {
    if (type.IsInterface || type.IsAbstract)
        continue;
    if (type.Namespace.StartsWith("Interfaces.DTO"))
    {
       var attributes = type.GetCustomAttributes(
             typeof (DataContractAttribute), true);
       if (attributes.Length == 0)
           Assert.Fail("Unable to identify [DataContract] attribute on type " 
                  + type.Name + " in DTO namespace");
       else
          foreach (var memberInfo in type.GetMembers())
            {
              if (memberInfo != null)
              {
                 var memberAttributes = 
                   memberInfo.GetCustomAttributes(typeof (DataMemberAttribute), true);
                 if (memberAttributes.Length == 0)
                   Assert.Fail("Unable to identify [DataMember] attribute on member " 
                       + memberInfo.Name + " on type "
                       + type.Name + " in DTO namespace");
              }
           }
        }
    }
}

Switching to a Different User Inside a Unit Test

Jak Charlton — Tue, 15 Apr 2008 17:51:00 GMT

Update: Re-published from old blog

I found a need yesterday to test some code in particular security contexts. After some (as always) prompt guidance from the altdotnet mailing list, I had the (as it turns out) all too simple solution. xUnit includes an attribute for this, but as I'm using NUnit and just wanted a clean way to do this without writing an extension, I figured I would just play around with it a bit.

One quick bit of refactoring later, and we have a class to handle this for us:

 1:  public class SecurityContextSwitcher : IDisposable

 2:  {

 3:  private readonly IPrincipal originalPrincipal;

4:

 5:  public SecurityContextSwitcher(string username, string[] roles)

 6:  {

 7:  originalPrincipal = Thread.CurrentPrincipal;

 8:  var identity = new GenericIdentity(username);

 9:  var principal = new GenericPrincipal(identity, roles);

 10:  Thread.CurrentPrincipal = principal;

 11:  }

12:

 13:  public void Dispose()

 14:  {

 15:  Thread.CurrentPrincipal = originalPrincipal;

 16:  }

 17:  }

This class allows us to test quite simply with the following syntax:

 1:  [Test]

 2:  public void Handler_will_allow_access_to_anyone_who_is_in_Users_role()

 3:  {

 4:  using (new SecurityContextSwitcher("Test", new[] {"Users"}))

 5:  {

 6:  var request = new GetRequest {Id = new Guid()};

 7:  handlerUnderTest.Handle(request);

 8:  }

 9:  }

10:

 11:  [PrincipalPermissionAttribute(SecurityAction.Demand, Role = "Users")]

 12:  public override void Handle(GetRequest message)

 13:  {

 14:  ...

 15:  }

Windsor Container Registration of Generic Component via C# Code

Jak Charlton — Sun, 13 Apr 2008 17:56:00 GMT

Update: Re-published from old blog

I've done it before, but my mind went totally blank yesterday ... due to some typically quick responses from the Castle Users Google Group ... here is the code to do it for future generations to avoid my same silly mistakes ...

 1: WindsorContainer container = new WindsorContainer();

 2: Type eventBrokerServiceType = typeof (IEventBroker<>);

 3: Type brokerType = typeof (EventBroker<>);

 4: container.AddComponent("key", eventBrokerServiceType, brokerType);

 5: IEventBroker<EventArgs> eventBroker =

 6:  container.Resolve<IEventBroker<EventArgs>>();

 7: Assert.IsNotNull(eventBroker);