Nhibernate getting SQL from HQL

In a previous post I put up some code on how to retrieve SQL from HQL. At the time I did not need to use it, however, now I do need to use it and came across some limitations. The code does not take into account any query parameters.
The project I am working on at the moment allows users to build their own queries using an interface similar to Ms Access. The query builder then converts the UI elements into an HQL statement. The statement could contain group by's and distinct commands which make it quite hard to do paging. I did come across this solution, however; it does not allow me to take advantage of the query caching ability of Nhibernate so that you get the total count once and then always get the next page of data.

The simplest solution to get the total count was to execute a SQL count query with a subquery.

Select count(*) from (.... the main query.....)

To get the main query I needed to convert the HQL into SQL. After hunting around the following was the best I could do with the restrictions I had. This is just the gist of the code after simplifying it so that it doesn't wrap too much.

public class HqlToSqlQueryConverter : QueryImpl
{
  public static HqlToSqlQueryConverter Create(IQuery query, ISession session, IDictionary<String, object> queryParameters)
  {
    if (!(query is QueryImpl))
 throw new InvalidOperationException("This class only return SQL for HQL");

    var sessionImp = session.GetSessionImplementation();
    var queryExpression = new StringQueryExpression(query.QueryString);
    var plan = sessionImp.Factory.QueryPlanCache.GetHQLQueryPlan(queryExpression, false, new Dictionary<string, IFilter>());

    var translationQuery = new HqlToSqlQueryConverter(query.QueryString, sessionImp, plan.ParameterMetadata);

    //Copy the parameters across
    foreach (String name in query.NamedParameters)
    {
      translationQuery.SetParameter(name, queryParameters[name]);
    }
    return translationQuery;
  }

  public String GetSQL()
  {
    var namedParams = this.NamedParams; //returns a copy
    var queryExpression = ExpandParameters(namedParams);
    var queryParameters = GetQueryParameters(namedParams);

    IQueryPlan plan = new QueryExpressionPlan(queryExpression, false, this.Session.EnabledFilters, this.Session.Factory);

    var sqlCommand = plan.Translators[0].Loader.CreateSqlCommand(queryParameters, this.Session);
    var dbCommand = this.Session.Batcher.PrepareQueryCommand(CommandType.Text, sqlCommand.Query, sqlCommand.ParameterTypes);
    String sql = dbCommand.CommandText;
    return sql;
  }
}

This is how I am using it:

      IQuery query = session.CreateQuery(queryString)
                            .SetFirstResult(pageNumber)
                            .SetMaxResults(maxResults)

                            .SetCacheable(true)
                            .Future<object[]>();

      var translationQuery = HqlToSqlQueryConverter.Create(query, session, parameters);
      String sql = translationQuery.GetSQL();

      int posOrderBy = sql.IndexOf("order by");
      if (posOrderBy > 0)
        sql = sql.Substring(0, posOrderBy);


      var count = session.CreateSQLQuery("Select Count(*) as totalCount from (" + sql + ") x")
                        .AddScalar("totalCount", NHibernateUtil.Int32)
                        .SetCacheable(cacheable)
                        .FutureValue<int>();
  
var results = query.List<object[]>();

This only works because I am using Future<>() and the parameters then get sent for both queries together.

Nhibernate SQL scalar query + SetCacheable

Nhibernate throws a cast error when CreateSQLQuery() is used with SetCacheable() and when trying to return a scalar value. The fix for this problem is to use the AddScalar() method. Thanks to this google group message.

    var sql = "Select count(*) as totalCount from myTables";
    var count = session.CreateSQLQuery(sql)
                   .AddScalar("totalCount", NHibernateUtil.Int32)                      .SetCacheable(true) 
                   .FutureValue<int>();