nHibernateの条件付きSQLパラメータ

問題は、nHibernateのCreateSqlQueryは完全なSQL文字列を必要としますが、パラメータを評価するまで文字列を作成できません。唯一の回避策は、条件付きパラメータを評価してSQL文字列を作成し、nHibernateセッションを作成してから、パラメータを再度評価してnHibernateクエリオブジェクトに追加することです。この問題は、同じ評価ロジックが2回書かれることです。必要なのは、すべてを行い、完了時にISQLQueryを出力する単純なFluentAPIです。

Before

public IList<AppointmentScheduleSummary> FillQuantityOld(DateTime? fromDate, DateTime? toDate, string CompanyID)
{
    string sql = "select VA.Userid as ID, E.FirstName + ' ' + E.LastName as Name,VA.Userid,count(*) as Total, getdate() as Date  from V_AppointmentScheduleStat VA, Appointment A, Employee E, Office O where";

    if (fromDate.HasValue)
    {
        sql += "  VA.Date >= '" + fromDate.Value.ToShortDateString() + "' and";

    }

    if (toDate.HasValue)
    {
        sql += "  VA.Date <= '" + toDate.Value.AddDays(1).ToShortDateString() + "' and";
    }

    sql += "  VA.date = A.date  and VA.UserId = E.UserId and O.OfficeNum = A.OfficeNum ";
    sql += " and A.appttypeid is not null";
    sql += " and O.CompanyID='" + CompanyID + "'";
    sql += " group by E.FirstName + ' ' + E.LastName ,VA.Userid  ";

    ISQLQuery query = _NHibernateSessionManager.GetSession().CreateSQLQuery(sql)
     .AddEntity("K", typeof(AppointmentScheduleSummary));
    return query.List<AppointmentScheduleSummary>();
}

After

public IList<AppointmentScheduleSummary> FillQuantity(DateTime? fromDate, DateTime? toDate,string CompanyID)
{
   var query = _NHibernateSessionManager.GetSession()
        .SQLQuery("select VA.Userid as ID, E.FirstName + ' ' + E.LastName as Name,VA.Userid,count(*) as Total, getdate() as Date  from V_AppointmentScheduleStat VA, Appointment A, Employee E, Office O where")

        .If(fromDate.HasValue, "VA.Date >= :fromDate and", parameters =>
        {
            parameters.SetParameter("fromDate", fromDate.Value.ToShortDateString());
        })

        .If(toDate.HasValue, "VA.Date <=:toDate and ", parameters =>
        {
            parameters.SetParameter("toDate", toDate.Value.AddDays(1).ToShortDateString());
            parameters.SetParameterList("", new[] {2, 3, 4,});

        })

        .Sql(" VA.date = A.date and VA.UserId = E.UserId and O.OfficeNum = A.OfficeNum and A.appttypeid is not null and O.CompanyID = :companyId" +
             " group by E.FirstName + ' ' + E.LastName ,VA.Userid")
            .SetParameter("companyId", CompanyID)
        .ToQuery();

     query.AddEntity("K", typeof(AppointmentScheduleSummary));
     return query.List<AppointmentScheduleSummary>();
}

SqlStringBuilder

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using NHibernate;

namespace IT2.DataAccess.Extensions
{
    public class SqlStringBuilder
    {
        private readonly ISession _session;
        private readonly Action<string> _logging;
        readonly StringBuilder _builder = new StringBuilder();
        readonly ParameterBuilder _parameterBuilder;

        /// <summary>
        /// Initializes a new instance of the <see cref="SqlStringBuilder" /> class.
        /// </summary>
        /// <param name="session">The session.</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="logging"></param>
        public SqlStringBuilder(ISession session, string sql, Action<string> logging)
        {
            _session = session;
            _logging = logging;
            _builder.Append(sql);
            Parameters = new Dictionary<string, object>();
            ParameterList = new Dictionary<string, IEnumerable>();
            _parameterBuilder = new ParameterBuilder(this);
        }

        /// <summary>
        /// Gets or sets the parameters.
        /// </summary>
        /// <value>The parameters.</value>
        public IDictionary<string, object> Parameters { get; set; }

        /// <summary>
        /// Gets or sets the parameters.
        /// </summary>
        /// <value>The parameters.</value>
        public IDictionary<string, IEnumerable> ParameterList { get; set; }

        /// <summary>
        /// To the query.
        /// </summary>
        /// <returns>IQuery.</returns>
        public ISQLQuery ToSqlQuery() 
        {
            string sql = _builder.ToString();

            if (_logging != null)
            {
                _logging(sql);
            }

            var query = _session.CreateSQLQuery(sql);

            foreach (var parameter in Parameters)
            {
                query.SetParameter(parameter.Key, parameter.Value);
            }

            foreach (var parameter in ParameterList)
            {
                query.SetParameterList(parameter.Key, parameter.Value);
            }

            return query;
        }

        /// <summary>
        /// To the query.
        /// </summary>
        /// <returns>IQuery.</returns>
        public IQuery ToQuery()
        {
            string sql = _builder.ToString();

            if (_logging != null)
            {
                _logging(sql);
            }

            var query = _session.CreateQuery(sql);

            foreach (var parameter in Parameters)
            {
                query.SetParameter(parameter.Key, parameter.Value);
            }

            foreach (var parameter in ParameterList)
            {
                query.SetParameterList(parameter.Key, parameter.Value);
            }

            return query;
        }

        /// <summary>
        /// Ifs the specified evaluation.
        /// </summary>
        /// <param name="evaluation">if set to <c>true</c> [evaluation].</param>
        /// <param name="sql">The SQL.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder If(bool evaluation, string sql)
        {
            return If(evaluation, sql, null);
        }

        /// <summary>
        /// Conditionals the specified evaluation.
        /// </summary>
        /// <param name="evaluation">if set to <c>true</c> [evaluation].</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns>SqlStringBuilder.</returns>
        public ParameterBuilder If(bool evaluation, string sql, Action<ParameterBuilder> parameters)
        {
            if (evaluation)
            {
                _builder.Append(string.Format(" {0} ", sql));

                if (parameters != null)
                {
                    parameters(_parameterBuilder);
                }
            }

            return _parameterBuilder;
        }

        /// <summary>
        /// Sets the parameters.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="key">The key.</param>
        /// <param name="value">The value.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder SetParameter<T>(string key, T value)
        {
            _parameterBuilder.SetParameter(key, value);
            return _parameterBuilder;
        }

        /// <summary>
        /// Sets the parameter list.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="key">The key.</param>
        /// <param name="value">The value.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder SetParameterList<T>(string key, T value) where T : IEnumerable
        {
            _parameterBuilder.SetParameterList(key, value);
            return _parameterBuilder;
        }

        /// <summary>
        /// SQLs the specified SQL.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <returns>IT2.DataAccess.SqlStringBuilder.</returns>
        public SqlStringBuilder Sql(string sql)
        {
            _builder.Append(string.Format(" {0} ", sql));
            return this;
        }
    }

    public class ParameterBuilder
    {
        private readonly SqlStringBuilder _builder;

        /// <summary>
        /// Initializes a new instance of the <see cref="ParameterBuilder" /> class.
        /// </summary>
        /// <param name="builder">The builder.</param>
        public ParameterBuilder(SqlStringBuilder builder)
        {
            _builder = builder;
        }

        /// <summary>
        /// Parameters the specified key.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="key">The key.</param>
        /// <param name="value">The value.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder SetParameter<T>(string key, T value)
        {
            _builder.Parameters.Add(key, value);
            return this;
        }

        /// <summary>
        /// Parameters the specified key.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="key">The key.</param>
        /// <param name="value">The value.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder SetParameterList<T>(string key, T value) where T : IEnumerable
        {
            _builder.ParameterList.Add(key, value);
            return this;
        }

        /// <summary>
        /// Ifs the specified evaluation.
        /// </summary>
        /// <param name="evaluation">if set to <c>true</c> [evaluation].</param>
        /// <param name="sql">The SQL.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder If(bool evaluation, string sql)
        {
            return _builder.If(evaluation, sql);
        }

        /// <summary>
        /// Conditions the specified evaluation.
        /// </summary>
        /// <param name="evaluation">if set to <c>true</c> [evaluation].</param>
        /// <param name="sql">The SQL.</param>
        /// <param name="parameters">The parameters.</param>
        /// <returns>ParameterBuilder.</returns>
        public ParameterBuilder If(bool evaluation, string sql, Action<ParameterBuilder> parameters)
        {
            return _builder.If(evaluation, sql, parameters);
        }

        /// <summary>
        /// SQLs the specified SQL.
        /// </summary>
        /// <param name="sql">The SQL.</param>
        /// <returns>SqlStringBuilder.</returns>
        public SqlStringBuilder Sql(string sql)
        {
            _builder.Sql(sql);
            return _builder;
        }

        /// <summary>
        /// To the query.
        /// </summary>
        /// <returns>ISQLQuery.</returns>
        public IQuery ToQuery()
        {
            return _builder.ToQuery();
        }

        /// <summary>
        /// To the query.
        /// </summary>
        /// <returns>ISQLQuery.</returns>
        public ISQLQuery ToSqlQuery()
        {
            return _builder.ToSqlQuery();
        }
    }
}