Change Sorting by NHibernate Queries

Question

I have a large ASP.NET application that uses NHibernate (v3.2.0.4000) to access SQL Server 2005 as an internal data store. We have several clients, each client has its own database, but all databases work on one server.

Quick question:
Without installing COLLATION on the database server or the individual databases themselves or overwriting the existing individual NHibernate query code, how can I add custom COLLATION instructions to NHibernate queries solely for ordering purposes before they get into the database?

The longer the question:
Due to the presence of new international customers, we need to support custom sorting for sorting in our application. We cannot do this at the database (or server) level, because we host a separate database for each client on the same server (each client may have different mapping requirements), and there are difficulties with things like TempDB, etc. etc., and several different mappings on the same server (see here and here ).

The result is a difference in how NHibernate is used throughout the code base (for example, a combination of HQL, ICriteria, plain SQL, QueryOver, and Linq), and since many of these queries are very complex and messy, we want to completely avoid modifying any existing NHibernate query code .

SQL Server allows you to invoke a query with a specific sorting that is used during the execution of this query, for example: SELECT * FROM Customer ORDER BY Surname COLLATE Latin1_General_CI_AS

I also know that NHibernate allows you to create an Interceptor that can be added to NHibernate based on a global or session (as shown here ), allowing you to intercept the SQL statement before sending it to SQL Server.

It seems I can write an interceptor class to intercept the SQL expression, but it just allows me to capture the raw SQL expression (strange and wonderful field names and aliases complete with NHibernate). I don’t know if there is a way so that I can purely analyze certain parts of the query (I only need the ORDER BY clause) and change the individual components of the ORDER BY clause (i.e. separate fields) after checking that the fields are based on the text and allow you to add a COLLATE statement to them.

NHibernate provides the NHibernate.SqlCommand.ISqlStringVisitor interface, which seems promising and seems to work on the SqlString written in the OnPrepareStatement method of the OnPrepareStatement class, which can be overridden, however, I am completely new to this part of NHibernate, and this does not help at the moment. since I am writing this, NHibernate is supposedly the ultimate source of information, nhibernate.info , does not work (and, it would seem, for several weeks now)!

Did anyone have to complete such a task? Is it possible to exclude NHibernate requests in a clean and safe way? Is there a completely different approach that would achieve the same (given the same limitations as the inability to change the sorting of the database / server)?

Answer 1

You can do this relatively easily using your own projection. The 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 applicable only to CriteriaAPI and QueryOver:

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

The SQL from this query is similar to:

 SELECT * FROM Item ORDER BY Name COLLATE Latin1_General_CI_AS asc 

I am not sure about HQL or LINQ, as I rarely use them, especially LINQ, which I have never used or viewed from the very first day as a toxic asset, and clearly did not encourage my teammates to use it.

Of course, this approach requires updating existing queries, but I believe that this is not very time-consuming, because it is a reduction in replacing the standard NHibernate order, so by searching and replacing you can replace all occurrences of the order. You can organize the sophisticated CollationProjection method, which knows when to apply the mapping by simply checking the provided criteria and the property name.

To implement custom forecasts, reading the NHibernate source code is a good way to get knowledge, since there is almost no document covering this section.

I don’t think that intercepting IInterceptor.OnPrepareStatement is a good option, but if you want to continue in this direction, you can try rebuilding the SQL string from parts of the original SQL string, sniffing for the ORDER BY clause in the process. I assume that no one has ever done this, so you have a lot of experiments, there will be many odd requests that will bite you by surprise.