How to use full-text search for any property with QueryOver API

Question

I am trying to use the SQL CONSTAINS function to filter some data in the QueryOver API.

The main problem is that I cannot use SqlFunction in the where clause, it does not compile because ICriterion is required.

 var result = Session.QueryOver<Individual>() .Where(Projections.SqlFunction( "FullTextContains", NHibernateUtil.Boolean, Projections.Property<Individual>(x => x.LastName), Projections.Constant("something"))) .List(); 

I tried to match it with the TRUE constant, but when the query is executed, it generates a syntax error, because the CONSTAINS function CONSTAINS not be used with the equals operator.

 var result = Session.QueryOver<Individual>() .Where(Restrictions.Eq(Projections.SqlFunction( "FullTextContains", NHibernateUtil.Boolean, Projections.Property<Individual>(p => p.LastName), Projections.Constant("something")), true)) .List(); 

How can I use the boolean sql function directly in a QueryOver API expression ?

Answer 1

Here is how I found how to use it:

 var projection = Projections.SqlFunction("FullTextContains", NHibernateUtil.Boolean, Projections.Property<Individual>(x => x.LastName), Projections.Constant("something")); var result = Session.QueryOver<Individual>() .Where(new ProjectionAsCriterion(projection)) .List(); 

To use IProjection as an ICriterion , I create my own implementation based on the SimpleExpression class from the NHibernate project.

 public class ProjectionAsCriterion : AbstractCriterion { private readonly IProjection _projection; public ProjectionAsCriterion(IProjection projection) { _projection = projection; } public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters) { var columnNames = CriterionUtil.GetColumnNamesForSimpleExpression( null, _projection, criteriaQuery, criteria, enabledFilters, this, string.Empty); var sqlBuilder = new SqlStringBuilder(4 * columnNames.Length); for (int i = 0; i < columnNames.Length; i++) { if (i > 0) { sqlBuilder.Add(" and "); } sqlBuilder.Add(columnNames[i]); } return sqlBuilder.ToSqlString(); } public override TypedValue[] GetTypedValues(ICriteria criteria, ICriteriaQuery criteriaQuery) { var typedValues = new List<TypedValue>(); if (_projection != null) { typedValues.AddRange(_projection.GetTypedValues(criteria, criteriaQuery)); } typedValues.Add(GetParameterTypedValue(criteria, criteriaQuery)); return typedValues.ToArray(); } private TypedValue GetParameterTypedValue(ICriteria criteria, ICriteriaQuery criteriaQuery) { return CriterionUtil.GetTypedValues(criteriaQuery, criteria, _projection, null).Single(); } public override IProjection[] GetProjections() { return new[] { _projection }; } public override string ToString() { return _projection.ToString(); } }