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;
		}
	}
}

In my previous post I discussed some ideas I have for a DotNetNuke module development framework on top of the standard DotNetNuke library. It should provide clear guidance in writing custom DNN modules using the Model View Presenter pattern and supports transparent use of NHibernate and Fluent NHibernate as an alternative to DNN’s DAL+.

One of the requirements where that it should automatically register basic services, repositories, views and presenters in a dependency container using Convention over Configuration and that the framework itself should be container independent (using ie. CommonServiceLocator).

While scanning trough DotNetNuke’s source I noticed it provides a clean abstraction for a component (DI) container: DotNetNuke.ComponentModel.IContainer and DotNetNuke.ComponentModel.AbstractContainer.

The default implementation DNN provides (SimpleContainer) is not sufficient for the things I wanted to do with it (auto registration, custom lifetime management, etc.). So instead of using CommonServiceLocator in my framework, I went with writing a custom implementation of IContainer utilizing the Castle Windsor container.

Following is an example implementation, feel free to use the code in your own project:

You should know how to change the default container DotNetNuke uses. If not, then you probably won’t need it and can continue reading just for fun ?

As you can see this is called CastleSimpleContainer. In DotNetNukeMVP a more extended implementation is provided to allow a common way for more flexible registration of components.

    1 // --------------------------------------------------------------------------------------------------------------------- 

    2 // <copyright file="CastleSimpleContainer.cs" company="RawSoft">

    3 //   Copyright (c) RawSoft.  All rights reserved.

    4 // </copyright>

    5 // <summary>

    6 //   Defines the CastleSimpleContainer type.

    7 // </summary>

    8 // ---------------------------------------------------------------------------------------------------------------------

    9 namespace DotNetNukeMVP.Castle

   10 {

   11     using System;

   12     using System.Collections;

   13     using System.Collections.Generic;

   14 

   15     using DotNetNuke.ComponentModel;

   16 

   17     using global::Castle.Core;

   18     using global::Castle.Windsor;

   19 

   20     public class CastleSimpleContainer : AbstractContainer

   21     {

   22         private readonly string containerName;

   23 

   24         private readonly IWindsorContainer container;

   25 

   26         private readonly IDictionary<string, IDictionary> componentDependencies = new Dictionary<string, IDictionary>();

   27 

   28         public CastleSimpleContainer()

   29             : this(new WindsorContainer())

   30         {

   31         }

   32 

   33         public CastleSimpleContainer(IWindsorContainer container)

   34             : this(string.Format("Container_{0}", Guid.NewGuid()), container)

   35         {

   36         }

   37 

   38         public CastleSimpleContainer(string name, IWindsorContainer container)

   39         {

   40             this.containerName = name;

   41             this.container = container;

   42         }

   43 

   44         public IWindsorContainer InnerContainer

   45         {

   46             get

   47             {

   48                 return this.container;

   49             }

   50         }

   51 

   52         public override string Name

   53         {

   54             get

   55             {

   56                 return this.containerName;

   57             }

   58         }

   59 

   60         public override void RegisterComponentSettings(string name, IDictionary dependencies)

   61         {

   62             componentDependencies[name] = dependencies;

   63         }

   64 

   65         public override object GetComponent(string name)

   66         {

   67             if (!this.container.Kernel.HasComponent(name))

   68             {

   69                 return null;

   70             }

   71 

   72             try

   73             {

   74                 return this.container.Resolve(name);

   75             }

   76             catch

   77             {

   78             }

   79 

   80             return null;

   81         }

   82 

   83         public override object GetComponent(string name, Type contractType)

   84         {

   85             if (!this.container.Kernel.HasComponent(contractType))

   86             {

   87                 return null;

   88             }

   89 

   90             try

   91             {

   92                 return this.container.Resolve(name, contractType);

   93             }

   94             catch

   95             {

   96             }

   97 

   98             return null;

   99         }

  100 

  101         public override IDictionary GetComponentSettings(string name)

  102         {

  103             if (componentDependencies.ContainsKey(name))

  104             {

  105                 return componentDependencies[name];

  106             }

  107 

  108             return null;

  109         }

  110 

  111         public override void RegisterComponent(string name, Type contractType, Type componentType, ComponentLifeStyleType lifestyle)

  112         {

  113             this.container.AddComponentLifeStyle(name, contractType, componentType, GetLifeStyleType(lifestyle));

  114         }

  115 

  116         public override void RegisterComponentInstance(string name, Type contractType, object instance)

  117         {

  118             this.container.Kernel.AddComponentInstance(name, contractType, instance);

  119         }

  120 

  121         public override object GetComponent(Type contractType)

  122         {

  123             try

  124             {

  125                 return this.container.Resolve(contractType);

  126             }

  127             catch

  128             {

  129             }

  130 

  131             return null;

  132         }

  133 

  134         private static LifestyleType GetLifeStyleType(ComponentLifeStyleType lifeStyleType)

  135         {

  136             LifestyleType scope = lifeStyleType == ComponentLifeStyleType.Singleton

  137                                         ? LifestyleType.Singleton

  138                                         : LifestyleType.Transient;

  139             return scope;

  140         }

  141     }

  142 }