Can I customize collation of query results in nHibernate?

Question

In plain old SQL, I can do something like this:

select * from mytable COLLATE Latin1_General_CS_AS

Is there a way to specify the type of collation I want to use for a given query in nHibernate, in HQL or in criteria?

Answer 1

Germán Schuager has managed to specify collation at run time. Take a look here.

var user = session.CreateCriteria(typeof (User))
    .Add(Expression.Sql("Username like ? collate Modern_Spanish_CS_AS", username, NHibernateUtil.String))
    .UniqueResult<User>();

Answer 2

From the same link than rebelliard answer provide, Shuager also supplies a way to define a custom function for doing something similar. This has the advantage of being usable in HQL too.

His custom function implementation was too specific for your question and my own needs, so here is the implementation I have ended with:

/// <summary>
/// Customized dialect for allowing changing collation on <c>like</c> statements.
/// </summary>
public class CustomMsSqlDialect : MsSql2008Dialect
{
    /// <summary>
    /// Default constructor.
    /// </summary>
    public CustomMsSqlDialect()
    {
        RegisterFunction("withcollation",
            new WithCollationFunction());
    }
}

/// <summary>
/// Add collation to string argument.
/// </summary>
[Serializable]
public class WithCollationFunction : SQLFunctionTemplate, IFunctionGrammar
{
    /// <summary>
    /// Default constructor.
    /// </summary>
    public WithCollationFunction()
        : base(NHibernateUtil.String, "?1 collate ?2")
    {
    }

    bool IFunctionGrammar.IsSeparator(string token)
    {
        return false;
    }

    bool IFunctionGrammar.IsKnownArgument(string token)
    {
        return Regex.IsMatch(token, "[A-Z][A-Z0-9_]+_(?:CS|CI)_(?:AS|AI)(?:_KS)?(?:_WS)?", RegexOptions.IgnoreCase | RegexOptions.CultureInvariant);
    }
}

Mind the dialect base class, I have used 2008 dialect, you may wish to change that. Do not forget to change your HQL dialect to your new custom dialect (using "dialect" configuration property of your session-factory for instance).

Example usage in HQL, standard query without collation customization :

from Cat as c
where c.Name like 'fel%'

With custom collation

from Cat as c
where c.Name like withCollation('fel%', French_CI_AI)

Works with Nhib 3.2.