How to use Full Text Search for any property with QueryOver API

Question

I'm trying to use the SQL function CONSTAINS to filter some data on QueryOver API.

The main issue is i can't use SqlFunction in where clause, it does not compile, because a ICriterion is needed.

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 to a TRUE constant, but when the query is executed it generates syntax error, because CONSTAINS function can't be used with 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 a boolean sql function directly in where expression on QueryOver API?

Answer 1

This is my finding for letting QueryOver support 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 a IProjection as a ICriterion I created my own implementation based on SimpleExpression class from 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();
    }
}