.NET Performance Blog

December 4, 2009

Integrating FreeText Search in NHibernate Detached Criteria

Filed under: General — Eric P @ 5:01 am

Sql Server 2005/2008 has very nice FreeText searching capabilities. It is pretty easy to run FreeText query in Sql, but how would one do it in NHibernate.

To be able to use “contains” or “freetext” functions in HQL, the functions need to be registered in an override of MsSql2008Dialect, as is done here:
http://nhforge.org/blogs/nhibernate/archive/2009/03/13/registering-freetext-or-contains-functions-into-a-nhibernate-dialect.aspx

But how would one do it in Criteria or DetachedCriteria. The approach I chose was to implement a new class based on ICriterion, AbstractCriterion. To simplify the process I downloaded latest NHibernate 2.1 and based my class on one of the already existing ICriterion implementations.

You will need NHibernate 2.1 and FreeText functionality enabled on your Sql Server database. Also all the text columns that are searched using FreeText Search, must be indexed in a FreeText catalog.
Here is the code for ContainsExpression class which allows for using Criteria API for simple “CONTAINS” searches:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NHibernate;
using NHibernate.Criterion;
using NHibernate.Engine;
using NHibernate.SqlCommand;
using NHibernate.Util;

namespace  ENTech.Common.Infrastructure
{
	public class ContainsExpression : AbstractCriterion
	{
		private readonly string propertyName;
		private readonly object value;
		private readonly IProjection projection;


		public ContainsExpression(IProjection projection, object value)
		{
			this.projection = projection;
			this.value = value;
		}


		public ContainsExpression(string propertyName, object value)
		{
			this.propertyName = propertyName;
			this.value = value;
		}


		public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery,
		                                      IDictionary<string, IFilter> enabledFilters)
		{
			//TODO: add default capacity
			SqlStringBuilder sqlBuilder = new SqlStringBuilder();
			SqlString[] columnNames =
				CriterionUtil.GetColumnNames(propertyName, projection, criteriaQuery, criteria, enabledFilters);

			SqlString columnName = columnNames[0];

			criteriaQuery.AddUsedTypedValues(GetTypedValues(criteria,criteriaQuery));
			sqlBuilder
				.Add("contains(")
				.Add(columnName)
				.Add(StringHelper.CommaSpace);

			sqlBuilder.AddParameter();
			sqlBuilder.Add(")");
			

			return sqlBuilder.ToSqlString();
		}


		public override TypedValue[] GetTypedValues(ICriteria criteria, ICriteriaQuery criteriaQuery)
		{
			return CriterionUtil.GetTypedValues(criteriaQuery, criteria, projection, propertyName, value.ToString().ToLower());
		}

		public override IProjection[] GetProjections()
		{
			if(projection != null)
			{
				return new IProjection[] { projection };
			}
			return null;
		}


		/// <summary></summary>
		public override string ToString()
		{
			return (projection ?? (object)propertyName) + " contains " + value;
		}
	}
}

The main method in this class is “ToSqlString” which generates Sql from Expression parameters.

Here is the unit test that checks this functionality.
It basically gets a count of all non-deleted Products with name or description that contain “pants” word.

using System;
using Castle.ActiveRecord;
using ENTech.Common.Infrastructure;
using NHibernate.Criterion;
using NUnit.Framework;
using Modules.ProductCatalog;

namespace ENTech.Common.Infrastructure.Tests
{
    [TestFixture]
    public class NHibernateTest
    {
        
		[Test]
		public void Search_For_Pants_Using_DatachedCriteria_With_ContainsExpressioin()
		{
			using (new SessionScope())
			{
				string keyword = "pants";

				var query = DetachedCriteria.For<Product>();

				query.Add(Restrictions.IsNull("DateDeleted"));

				ICriterion criterion = new ContainsExpression("Name", keyword);
				criterion = Restrictions.Or(criterion, new ContainsExpression("Description", keyword));

				query.Add(criterion);

				Console.WriteLine("Total products: " + ActiveRecordMediator.Count(typeof(Product), query));
			}
		}
	}
}

Using NHProf. I can see that the following sql query is generated:

SELECT count(* ) as y0_
FROM   Product this_
WHERE  this_.DateDeleted is null 
       and (contains(this_.Name,'pants' /* @p0 */)
             or contains(this_.Description,'pants' /* @p1 */))

Advertisements

1 Comment »

  1. Hi there, nice article and I have used your code but it contains a little error in the ContainsExpression constructors.

    You should do something with the value like this:

    string v = value.ToString();
    if(v.Contains(‘ ‘) v = “\”” + v + “\””;

    As your code does not allow a match on keywords containing spaces. It probably also needs to escape double quotes as well but I haven’t done that.

    Comment by ramonsmits — March 26, 2010 @ 2:18 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: