Nhibernate QueryOver collation without hard coded column name

Question

So I have the following sql

SELECT * FROM table Where Name COLLATE LATIN1_GENERAL_CI_AI LIKE 'myText%'

which I want to implement using QueryOver

At the minute I have:

whereRestriction.Add(Expression.Sql("Name COLLATE LATIN1_GENERAL_CI_AI LIKE ?", String.Format("{0}%", subStringMatch), HibernateUtil.String));

which works fine, but with two issues. Firstly it's sqlserver specific and secondly the database column 'Name' is hardcoded.

Has anyone any suggestions to get around these two problems, or at the very least the hardcoded db column Name?

Answer 1

I've implemented it this way. Not sure if there is any better way...

I. The like expression, profiting from the existing Like expression

public class LikeCollationExpression : LikeExpression
{
    const string CollationDefinition = " COLLATE {0} ";
    const string Latin_CI_AI = "LATIN1_GENERAL_CI_AI";

    // just a set of constructors
    public LikeCollationExpression(string propertyName, string value, char? escapeChar, bool ignoreCase) : base(propertyName, value, escapeChar, ignoreCase) { }
    public LikeCollationExpression(IProjection projection, string value, MatchMode matchMode) : base(projection, value, matchMode) { }
    public LikeCollationExpression(string propertyName, string value) : base(propertyName, value) { }
    public LikeCollationExpression(string propertyName, string value, MatchMode matchMode) : base(propertyName, value, matchMode) { }
    public LikeCollationExpression(string propertyName, string value, MatchMode matchMode, char? escapeChar, bool ignoreCase) : base(propertyName, value, matchMode, escapeChar, ignoreCase) { }

    // here we call the base and append the COLLATE
    public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary<string, IFilter> enabledFilters)
    {
        // base LIKE
        var result = base.ToSqlString(criteria, criteriaQuery, enabledFilters);

        var sqlStringBuilder = new SqlStringBuilder(result);

        // extend it with collate
        sqlStringBuilder.Add(string.Format(CollationDefinition, Latin_CI_AI ));

        return sqlStringBuilder.ToSqlString();
    }
}

II. the custom extension method

public static class QueryOverExt
{
    // here: WhereLikeCiAi() 
    public static IQueryOver<TRoot, TSubType> WhereLikeCiAi<TRoot, TSubType>(
        this IQueryOver<TRoot, TSubType> query
        , Expression<Func<TSubType, object>> expression
        , string value
        , MatchMode matchMode)
    {
        var name = ExpressionProcessor.FindMemberExpression(expression.Body);
        query
            .UnderlyingCriteria
            .Add
            (
                new LikeCollationExpression(name, value, matchMode)
            );
        return query;
    }
}

III. the usage anyhwere in the QueryOverAPI

...
query.WhereLikeCiAi(c => c.Name, "searchedString", MatchMode.Anywhere);