A better SQLCriterion for NHibernate

I was using the NHibernate Criteria API lately, and needed some SQL like this:

WHERE root.Quantity > child.Quantity + ?

As far as I know, you cannot add calculations using the Criteria API, so you would have to use a SQLCriterion (or HQL, but i cannot use it in this case). Downside of SQLCriterion is, is that SQLCriterion uses column names, not property names.

This leads to all kinds of problems when working with associations. It is possible to add '{alias}' to your query for the root entity alias. But this isn't possible for other (child) relations. I ended up writing a better SqlCriterion, which replaces {propertyName} and {alias.PropertyName} with the correct column names.

The tests:

namespace RawSoft.NHibernateEx.Tests.Criterion
{
	using System.Reflection;
	using Model;
	using NHibernate;
	using NHibernate.Cfg;
	using NHibernate.Engine;
	using NHibernate.Impl;
	using NHibernate.Loader.Criteria;
	using NHibernateEx.Criterion;
	using NUnit.Framework;

	[TestFixture]
	public class BetterSqlCriterionFixture
	{
		protected ISessionFactory factory;

		[SetUp]
		public virtual void SetUp()
		{
			Configuration cfg = new Configuration();
			cfg = cfg.Configure("hibernate.cfg.xml");
			Assembly dm = Assembly.GetAssembly(typeof(Foo));
			cfg.AddAssembly(dm);

			factory = cfg.BuildSessionFactory();
		}

		[Test]
		public void Replaces_propertynames_with_columnnames()
		{
			using (ISession session = factory.OpenSession())
			{
				var criteria = (CriteriaImpl)session.CreateCriteria();
				var criteriaQuery = new CriteriaQueryTranslator(
					(ISessionFactoryImplementor)factory,
					criteria, criteria.EntityOrClassName, "sql_alias");

				var crit = new BetterSqlCriterion("{Quantity} > {Quantity2} + 3");

				var where = crit.ToSqlString(criteria, criteriaQuery, null).ToString();
				Assert.That(where, Is.EqualTo("sql_alias.Quantity > sql_alias.Quantity2 + 3"));
			}
		}

		[Test]
		public void Replaces_aliased_propertynames_with_columnnames()
		{
			using (ISession session = factory.OpenSession())
			{
				var criteria = (CriteriaImpl)session.CreateCriteria();
				criteria.CreateAlias("Bar", "b");
				var criteriaQuery = new CriteriaQueryTranslator(
					(ISessionFactoryImplementor)factory,
					criteria, criteria.EntityOrClassName, "sql_alias");

				var crit = new BetterSqlCriterion("{Quantity} > {b.Quantity} + 1");
                
				var where = crit.ToSqlString(criteria, criteriaQuery, null).ToString();
				Assert.That(where, Is.EqualTo("sql_alias.Quantity > b1_.Quantity + 1"));
			}
		}

		[Test]
		[ExpectedException(typeof(QueryException))]
		public void non_existing_property_throws_exception()
		{
			using (ISession session = factory.OpenSession())
			{
				var criteria = (CriteriaImpl)session.CreateCriteria();
				var criteriaQuery = new CriteriaQueryTranslator(
					(ISessionFactoryImplementor)factory,
					criteria, criteria.EntityOrClassName, "sql_alias");

				var crit = new BetterSqlCriterion("{NonExisting} > {Quantity} + 1");

				// should throw
				crit.ToSqlString(criteria, criteriaQuery, null).ToString();
			}
		}
	}
}

The better SqlCriterion:

namespace RawSoft.NHibernateEx.Criterion
{
	using System;
	using System.Collections.Generic;
	using System.Text.RegularExpressions;
	using NHibernate;
	using NHibernate.Criterion;
	using NHibernate.Engine;
	using NHibernate.SqlCommand;
	using NHibernate.Type;
	using NHibernate.Util;

	[Serializable]
	public class BetterSqlCriterion : AbstractCriterion
	{
		private string _sql;
		private readonly TypedValue[] _typedValues;

		public BetterSqlCriterion(string sql)
			: this(sql, ArrayHelper.EmptyObjectArray, ArrayHelper.EmptyTypeArray)
		{
		}

		public BetterSqlCriterion(string sql, object value, IType type)
			: this(sql, new[] { value }, new[] { type })
		{
		}

		public BetterSqlCriterion(string sql, object[] values, IType[] types)
		{
			_sql = sql;
			_typedValues = new TypedValue[values.Length];
			for (int i = 0; i < _typedValues.Length; i++)
			{
				_typedValues[i] = new TypedValue(types[i], values[i], EntityMode.Poco);
			}
		}

		/// 
		/// Render a SqlString for the expression.
		/// 
		/// 
		/// A SqlString that contains a valid Sql fragment.
		/// 
		public override SqlString ToSqlString(ICriteria criteria, ICriteriaQuery criteriaQuery, IDictionary enabledFilters)
		{
			criteriaQuery.AddUsedTypedValues(GetTypedValues(criteria, criteriaQuery));

			var columns = Regex.Matches(_sql, @"\{(.*?)\}");

			foreach (Match column in columns)
			{
				var columnName = column.Groups[1].Value;

				var columnNames = CriterionUtil.GetColumnNames(columnName, null, criteriaQuery, criteria, null);
				if (columnNames.Length != 1)
				{
					throw new HibernateException(string.Format("You can only alias single-column properties: {0}", column.Value));
				}
				_sql = _sql.Replace(column.Value, columnNames[0].ToString());
			}

			return SqlString.Parse(_sql);
		}

		/// 
		/// Return typed values for all parameters in the rendered SQL fragment
		/// 
		/// 
		/// An array of TypedValues for the Expression.
		/// 
		public override TypedValue[] GetTypedValues(ICriteria criteria, ICriteriaQuery criteriaQuery)
		{
			return _typedValues;
		}

		/// 
		/// Return all projections used in this criterion
		/// 
		/// 
		/// An array of IProjection used by the Expression.
		/// 
		public override IProjection[] GetProjections()
		{
			return null;
		}

		/// 
		/// Gets a string representation of the .  
		/// 
		/// 
		/// A String that shows the contents of the .
		/// 
		/// 
		/// This is not a well formed Sql fragment.  It is useful for logging what the 
		///             looks like.
		/// 
		public override string ToString()
		{
			return this._sql;
		}
	}
}