.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:

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];



			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
    public class NHibernateTest
		public void Search_For_Pants_Using_DatachedCriteria_With_ContainsExpressioin()
			using (new SessionScope())
				string keyword = "pants";

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


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


				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 */))

Blog at WordPress.com.