Modify collation on NHibernate queries

Question

I have a large ASP.NET application that uses NHibernate (v3.2.0.4000) to access SQL Server 2005 as the back-end data-store. We have multiple customers, with each customer having their own database, but all databases running on the same server.

The quick question:
Without setting COLLATION on the database server, or the individual databases themselves, or re-writing existing individual NHibernate query code, how can I add custom COLLATION statements to NHibernate queries, purely for ordering purposes, before they hit the database?

The longer question:
Due to having some new international customers, we need to support custom collation for sorting within our application. We can't do this on a database (or server) level as we host a separate database for each customer on the same server (with each customer potentially having different collation requirements) and there are complications with things like TempDB etc. and multiple differing collations on the same server (See here and here).

As a result of variance in how NHibernate is used throughout the codebase (i.e. a combination of HQL, ICriteria, plain SQL, QueryOver and Linq), and because many of these queries are very complicated and messy, we want to completely avoid altering any of the existing NHibernate query code.

SQL Server will allow invoking a query with a specific collation applied at the run-time of that query, like so: SELECT * FROM Customer ORDER BY Surname COLLATE Latin1_General_CI_AS

I'm also aware that NHibernate allows the creation of an Interceptor which can be added to NHibernate on either a global or session-specific basis (As demonstrated here) allowing the interception of the SQL statement prior to it being sent to SQL Server.

It seems I can write an interceptor class to intercept the SQL Statement, however, that simply allows me to capture the raw SQL Statement (complete with NHibernate's weird and wonderful field and alias names). I don't know if there's any way that I can cleanly parse specific parts of the query (I only need the ORDER BY clause) and alter the individual component parts of the ORDER BY clause (i.e. the individual fields) after checking that the fields are text-based and will allow the COLLATE statement to be appended to them.

NHibernate exposes a NHibernate.SqlCommand.ISqlStringVisitor interface which sounds promising and seems to operate on the SqlString captured in the OnPrepareStatement method of the EmptyInterceptor class which can be overridden, however, I'm completely unfamiliar with this part of NHibernate, and it doesn't help that currently, as I write this, NHibernate's supposedly definitive source of information, nhibernate.info, is down (and seemingly has been for weeks)!

Has anyone had to perform such a task? Is it possible to de-constuct NHibernate queries in a clean and type-safe way? Is there an entirely different approach that would achieve the same thing (given the same constraints around being unable to alter the database/server collation)?

Answer 1

You can do that relatively easy using a custom projection. Following class is a good starting point:

public class OrderByWithCollate : SimpleProjection
{
    public string ColumnName { get; private set; }
    public string Collation { get; private set; }

    public OrderByWithCollate(string columnName, string collation)
    {
        ColumnName = columnName;
        Collation = collation;
    }

    public override SqlString ToGroupSqlString(
        ICriteria criteria, 
        ICriteriaQuery criteriaQuery, 
        IDictionary<string, IFilter> enabledFilters)
    {
        throw new NotImplementedException();   
    }

    public override SqlString ToSqlString(
        ICriteria criteria, 
        int position, 
        ICriteriaQuery criteriaQuery, 
        IDictionary<string, IFilter> enabledFilters)
    {
        return new SqlStringBuilder()
            .Add(ColumnName)
            .Add(" COLLATE ").Add(Collation)
            .Add(" as __collate_").ToSqlString();
    }

    public override IType[] GetTypes(ICriteria criteria, ICriteriaQuery criteriaQuery)
    {
        return new IType[] {NHibernateUtil.String};
    }

    public override bool IsGrouped { get { return false; } }

    public override bool IsAggregate { get { return false; } }
}

However, this approach is obviously only applicable to CriteriaAPI and QueryOver:

session.QueryOver<Item>()
    .OrderBy(new OrderByWithCollate("Name", "Latin1_General_CI_AS")).Asc
    .List();

The SQL from that query is similar to:

SELECT * FROM Item ORDER BY Name COLLATE Latin1_General_CI_AS asc

I'm not sure about HQL or LINQ as I'm rarely use them, especially LINQ, which I have never used and considered it from day one as a toxic asset, and explicitly discouraged my teammates from using it.

Of course, this approach requires to update existing queries, but I think it's not very time consuming to apply because this is a drop in replacement for standard NHibernate ordering, so by search and replace you can replace all occurrences of ordering. You can arrange a complex CollationProjection that knows when to apply collation just by checking the supplied criteria and the property name.

For implementing custom projections, reading NHibernate source code is a good way to obtains knowledge, as there is almost no document covering this topic.

I don't think intercepting IInterceptor.OnPrepareStatement is a good option, but if you want to proceed on this direction, then you can try rebuild the SQL string from the parts of the original SQL string, sniffing for ORDER BY clause in process. I guess that no one ever did it, so you are in for a lot of experiments, there will be a lot of odd queries that bite you by surprise.