UnionQueryBuilder for NHibernate

UnionQueryBuilder.cs

public class UnionQueryBuilder : IUnionQueryBuilder
{
	public IQuery BuildQuery(UnionDefinition definition)
	{
		Dialect dialect = GetDialect();

		var builder = new SqlStringBuilder();

		builder.Add("select ")
			.Add("u.Id")
			.Add(",")
			.Add("u.EntityType ")
			.Add(" from ( ");

		for (int i = 0; i < definition.Selects.Length; i++)
		{
			Select select = definition.Selects[i];
			if (i > 0) builder.Add("\r\nunion all\r\n");
			BuildSelectQuery(builder, i, dialect, definition, select);
		}

		builder.Add(" ) u ");

		BuildWhere(builder, definition, dialect);

		BuildOrderBy(builder, definition, dialect);

		SqlString queryBeforeLimit = builder.ToSqlString();

		int parameterCountBeforeSkipAndTake = queryBeforeLimit.GetParameterCount();

		int skipParameterIndex = parameterCountBeforeSkipAndTake + 1;

		int takeParameterIndex = parameterCountBeforeSkipAndTake;

		if (dialect is MySQLDialect && definition.Skip > 0)
		{
			// hack for MySql as it doesn't have the notion of positional parameters, they are just ordered ? parameters
			// so GetLimitString only works if limit parameter is 1 higher then offset parameter index
			skipParameterIndex = takeParameterIndex;
			takeParameterIndex++;
		}

		SqlString withTakeAndLimit = dialect.GetLimitString(queryBeforeLimit, definition.Skip, definition.Take, skipParameterIndex, takeParameterIndex);

		ISQLQuery query = UnitOfWork.CurrentSession.CreateSQLQuery(withTakeAndLimit.ToString());

		if (dialect.SupportsVariableLimit && definition.Skip > 0)
		{
			query.SetParameter(skipParameterIndex, definition.Skip);				
		}

		int limit = (dialect.UseMaxForLimit) ? definition.Skip + definition.Take : definition.Take;


		if (dialect.BindLimitParametersFirst)
		{
			query.SetParameter(0, limit);
		}
		else if (dialect.SupportsVariableLimit)
		{
			query.SetParameter(takeParameterIndex, limit);
		}

		if (definition.Where != null)
		{
			int offset = dialect.BindLimitParametersFirst ? 1 : 0;

			for (int i = 0; i < definition.Where.Count; i++)
			{
				IWhereFilter where = definition.Where[i];

				foreach (object parameter in where.GetValuesToApply())
				{
					query.SetParameter(offset++, parameter);
				}
			}
		}

		return query;
	}

	public IQuery BuildCountQuery(UnionDefinition definition)
	{
		Dialect dialect = GetDialect();

		var builder = new SqlStringBuilder();

		builder.Add("select count(*)")
			.Add(" from ( ");

		for (int i = 0; i < definition.Selects.Length; i++)
		{
			Select select = definition.Selects[i];
			if (i > 0) builder.Add("\r\nunion all\r\n");
			BuildSelectQuery(builder, i, dialect, definition, select);
		}

		builder.Add(" ) u ");

		BuildWhere(builder, definition, dialect);

		ISQLQuery query = UnitOfWork.CurrentSession.CreateSQLQuery(builder.ToSqlString().ToString());

		if (definition.Where != null)
		{
			int offset = 0;

			for (int i = 0; i < definition.Where.Count; i++)
			{
				IWhereFilter where = definition.Where[i];

				foreach (object parameter in where.GetValuesToApply())
				{
					query.SetParameter(offset++, parameter);
				}
			}
		}

		return query;
	}

	Dialect GetDialect()
	{
		var factoryImpl = (SessionFactoryImpl) UnitOfWork.CurrentSession.SessionFactory;

		return factoryImpl.Dialect;
	}

	void BuildOrderBy(SqlStringBuilder builder, UnionDefinition definition, Dialect dialect)
	{
		if (definition.Orders != null)
		{
			builder.Add("\r\n order by ");
			for (int i = 0; i < definition.Orders.Length; i++)
			{
				if (i > 0) builder.Add(", ");

				OrderBy order = definition.Orders[i];

				builder.Add(Requote(dialect, order.Column)).Add(" ").Add((order.Direction == SortDirection.Ascending) ? "ASC" : "DESC");
			}

			builder.Add(" ");
		}
	}

	void BuildWhere(SqlStringBuilder builder, UnionDefinition definition, Dialect dialect)
	{
		if (definition.Where != null && definition.Where.Count != 0)
		{
			builder.Add(" where ");
			for (int i = 0; i < definition.Where.Count; i++)
			{
				IWhereFilter where = definition.Where[i];
				if (i > 0) builder.Add(" and ");
				builder.Add(where.ToSqlString(dialect));
			}
		}
	}

	string Requote(Dialect dialect, string name)
	{
		return name.Replace('[', dialect.OpenQuote).Replace(']', dialect.CloseQuote);
	}

	void BuildSelectQuery(SqlStringBuilder builder, int index, Dialect dialect, UnionDefinition definition, Select select)
	{
		string tableAlias = "t_" + index;

		builder
			.Add("select ")
			.Add(tableAlias).Add(".Id,")
			.Add(index.ToString()).Add(" as EntityType");

		foreach (string column in definition.AdditionalColumns)
		{
			builder.Add(",");
			ColumnAlias alias = select.GetAlias(column);

			if (alias != null)
			{
				if (alias.AlwaysNull)
				{
					builder.Add("null").Add(" as ").Add(Requote(dialect, column));
				}
				else
				{
					if (alias.Source != null)
					{
						builder.Add(Requote(dialect, alias.Source)).Add(" as ").Add(Requote(dialect, column));
					}
					else
					{
						builder.Add(Requote(dialect, tableAlias + "." + column)).Add(" as ").Add(Requote(dialect, column));
					}
				}
			}
			else
			{
				builder.Add(Requote(dialect, tableAlias + "." + column)).Add(" as ").Add(Requote(dialect, column));
			}
		}

		builder.Add(" from ").Add(select.Table).Add(" ").Add(tableAlias).Add(" ");

		if (select.Joins != null)
		{
			foreach (Join join in select.Joins)
			{
				JoinFragment fragment = dialect.CreateOuterJoinFragment();
				string ownerColumn = join.OwnerColumn.Contains(".") ? join.OwnerColumn : tableAlias + "." + join.OwnerColumn;
				fragment.AddJoin(join.Table, join.Alias, new[] {ownerColumn}, new[] {join.TargetColumn}, join.JoinType);
				builder.Add(fragment.ToFromFragmentString);
			}
		}
	}
}