Using CONTAINS from HQL / Criteria API

Question

Hi all,

I'm using NHibernate 2.1.2.4000GA. I'm trying to use SQL Server's CONTAINS function from within HQL and the criteria APIs. This works fine in HQL:

CONTAINS(:value)

However, I need to qualify the table in question. This works fine:

CONTAINS(table.Column, :value)

However, I need to search across all indexed columns in my table. I tried this:

CONTAINS(table.*, :value)

But I get:

NHibernate.Hql.Ast.ANTLR.QuerySyntaxException : Exception of type 'Antlr.Runtime.MissingTokenException' was thrown. near line ... [select table.Id from Entities.Table table where CONTAINS(table.*,:p0) order by table.Id asc]
    at NHibernate.Hql.Ast.ANTLR.ErrorCounter.ThrowQueryException()
    at NHibernate.Hql.Ast.ANTLR.HqlParseEngine.Parse()
    at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.Parse(Boolean isFilter)
    at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.DoCompile(IDictionary`2 replacements, Boolean shallow, String collectionRole)
    at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.Compile(IDictionary`2 replacements, Boolean shallow)
    at NHibernate.Engine.Query.HQLQueryPlan..ctor(String hql, String collectionRole, Boolean shallow, IDictionary`2 enabledFilters, ISessionFactoryImplementor factory)
    at NHibernate.Engine.Query.QueryPlanCache.GetHQLQueryPlan(String queryString, Boolean shallow, IDictionary`2 enabledFilters)
    at NHibernate.Impl.AbstractSessionImpl.GetHQLQueryPlan(String query, Boolean shallow)
    at NHibernate.Impl.AbstractSessionImpl.CreateQuery(String queryString)

So it would seem the HQL parser chokes on the asterisk. I thought of doing this:

CONTAINS(table.Column1, :value) or CONTAINS(table.Column2, :value)

Not only is this inefficient, it can also yields incorrect results depending on the full text predicate in :value.

I tried customizing my dialect as per these instructions, but that doesn't help because you're still left with the same problem: specifying table.* causes the HQL parser to fall over.

I thought of specifying query substitutions:

<property name="query.substitutions">TABLECONTAINS(=CONTAINS(table.*,</property>

And then simply doing:

TABLECONTAINS(:value)

But that does not work. I'm not sure why - judging by the resultant error, the substitution just doesn't take place because "TABLECONTAINS" is still present in the query. Besides, this wouldn't work for all cases because I'd need to know the alias of the table and dynamically substitute it in.

Therefore, I rolled an interception-based approach:

using System;
using NHibernate;
using NHibernate.SqlCommand;

public class ContainsInterceptor : EmptyInterceptor
{
    public override SqlString OnPrepareStatement(SqlString sql)
    {
        var indexOfTableContains = sql.IndexOfCaseInsensitive("TABLECONTAINS(");

        if (indexOfTableContains != -1)
        {
            var sqlPart = sql.ToString();
            var aliasIndex = sqlPart.LastIndexOf("Table ", indexOfTableContains, StringComparison.Ordinal);

            if (aliasIndex == -1)
            {
                return sql;
            }

            aliasIndex += "Table ".Length;
            var alias = sqlPart.Substring(aliasIndex, sqlPart.IndexOf(" ", aliasIndex, StringComparison.Ordinal) - aliasIndex);
            sql = sql.Replace("TABLECONTAINS(", "CONTAINS(" + alias + ".*,");
        }

        return base.OnPrepareStatement(sql);
    }
}

This works and I will now be able to sleep tonight, but I do feel a sudden desire to attend London's impending papal procession and shout out a confession.

Can anyone suggest a better way to achieve this?

Answer 1

I would think that a custom dialect would be the appropriate way to handle this. You can find some guidance in this article. I've used this approach to register SQL Server-specific functions like ISNULL for use in our projects.

Answer 2

Why not use an ISQLQuery instead?

You can still retrieve entities, see http://nhforge.org/doc/nh/en/index.html#querysql-creating