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