How can I have NHibernate only generate the SQL without executing it?

Question

I know how to log the SQL to log4net/NLog/trace window at runtime with the show_sql configuration option.

What I'm looking for is a way to give a Query<T>() to NHibernate retrieve the generated SQL.

I've looked through the Persister class, the Drivers, different Interceptors and Events. There are so many places to look, even narrowing down my search would be of great help.

Jarad

What I'm trying to accomplish is a poor man's profiler of sorts. I just want to know how a particular linq query will evaluate from a piece of test code. – Jarad

Answer 1

You can get the generated sql queries without execution with the following methods:

For the NHibernate.Linq queries:

public String GetGeneratedSql(System.Linq.IQueryable queryable, ISession session)
{
    var sessionImp = (ISessionImplementor) session;
    var nhLinqExpression = new NhLinqExpression(queryable.Expression, sessionImp.Factory);
    var translatorFactory = new ASTQueryTranslatorFactory();
    var translators = translatorFactory.CreateQueryTranslators(nhLinqExpression, null, false, sessionImp.EnabledFilters, sessionImp.Factory);

    return translators[0].SQLString;
}

For Criteria queries:

public String GetGeneratedSql(ICriteria criteria)
{
    var criteriaImpl = (CriteriaImpl) criteria;
    var sessionImpl = (SessionImpl) criteriaImpl.Session;
    var factory = (SessionFactoryImpl) sessionImpl.SessionFactory;
    var implementors = factory.GetImplementors(criteriaImpl.EntityOrClassName);
    var loader = new CriteriaLoader((IOuterJoinLoadable) factory.GetEntityPersister(implementors[0]), factory, criteriaImpl, implementors[0], sessionImpl.EnabledFilters);

    return loader.SqlString.ToString();
}

For QueryOver queries:

public String GetGeneratedSql(IQueryOver queryOver)
{
    return GetGeneratedSql(queryOver.UnderlyingCriteria);
}

For Hql queries:

public String GetGeneratedSql(IQuery query, ISession session)
{
    var sessionImp = (ISessionImplementor)session;
    var translatorFactory = new ASTQueryTranslatorFactory();
    var translators = translatorFactory.CreateQueryTranslators(query.QueryString, null, false, sessionImp.EnabledFilters, sessionImp.Factory);

    return translators[0].SQLString;
}

Pash

Is there a way to do it for HQL queries? – Kozak

Updated my answer with hql queries. – Pash

@Pash I have upvoted your very useful answer. Do you have any idea if this is possible for INSERTs - UPDATEs? See #10787434 Thank you! – Thorium

Very, Very useful answer. For criteria queries the sqlstring contains ? for the positional parameters. I was able to get them with loader.Translator.GetQueryParameters().PositionalParameterValues which I passed back to the caller with an out parameter. – Loma

This is something I've been hunting after for a while. Thank you kindly :) – Banks

Is there any way to enforce GetGeneratedSql(ICriteria criteria) to return query with named parameters instead of placeholders (?)? – Blodgett

Is there any way reverse this ? , I mean generated sql string to QueryOver or entities ? – Hinkle

Is there an update for the use of CreateQueryTranslators for Nhibernate 4.0.4? There was a breaking change to this method and the signature is now different. – Praseodymium

Edited the NHibernate.Linq queries method for the new signature of 'CreateQueryTranslators' in Nhibernate 4.0.4 – Pash

is there any way to create full executable sql command with values? for example in mssql, NHibernate uses "exec sp_executesql" SP with positional parameters – Pommard

Hello from few years later. Again, seems CreateQueryTranslators has changed. The HQL version for GetGeneratedSql cannot use CreateQueryTranslators(query.QueryString as there's string-vs-IQueryExpression mismatch. Do you have by any chance any newer version fo that snippet? – Battledore

@quetzalcoatl, to do it using NH 5.2, I edited the answer – Baeyer

@Sÿl unless you are also 'Roman Artiukhin', I don't see anything you did. If you edited THIS answer, then probably your edit was rejected - this is old answer, from 2012! You don't want to damage it unless there's some actual error there. New answers should be posted as new answers, just like Roman Artiukhin did. – Battledore

@Battledore you're right it must have been rejected, new answer below – Baeyer

Answer 2

For NHibernate 5.2 in case you want to see actual DbCommand prepared for query (so you can check both SQL in cmd.CommandText and supplied parameters in cmd.Parameters):

//For LINQ
public IEnumerable<DbCommand> GetDbCommands<T>(IQueryable<T> query, ISession s)
{
    return GetDbCommands(LinqBatchItem.Create(query), s);
}

//For HQL
public IEnumerable<DbCommand> GetDbCommands(IQuery query, ISession s)
{
    return GetDbCommands(new QueryBatchItem<object>(query), s);
}

//For QueryOver
public IEnumerable<DbCommand> GetDbCommands(IQueryOver query, ISession s)
{
    return GetDbCommands(query.RootCriteria, s);
}

//For Criteria (needs to be called for root criteria)
public IEnumerable<DbCommand> GetDbCommands(ICriteria rootCriteria, ISession s)
{
    return GetDbCommands(new CriteriaBatchItem<object>(query), s);
}

//Adapted from Loader.PrepareQueryCommand
private static IEnumerable<DbCommand> GetDbCommands(IQueryBatchItem item, ISession s)
{
    var si = s.GetSessionImplementation();
    item.Init(si);
    var commands = item.GetCommands();
    foreach (var sqlCommand in commands)
    {
        //If you don't need fully prepared command sqlCommand.Query contains SQL returned by accepted answer
        var sqlString = sqlCommand.Query;
        sqlCommand.ResetParametersIndexesForTheCommand(0);
        var command = si.Batcher.PrepareQueryCommand(System.Data.CommandType.Text, sqlString, sqlCommand.ParameterTypes);
        RowSelection selection = sqlCommand.QueryParameters.RowSelection;
        if (selection != null && selection.Timeout != RowSelection.NoValue)
        {
            command.CommandTimeout = selection.Timeout;
        }

        sqlCommand.Bind(command, si);

        IDriver driver = si.Factory.ConnectionProvider.Driver;
        driver.RemoveUnusedCommandParameters(command, sqlString);
        driver.ExpandQueryParameters(command, sqlString, sqlCommand.ParameterTypes);
        yield return command;
    }
}

Amos

Answer 3

Based on the NHibernate version 3.4 the method for linq expression is:

public String GetGeneratedSql(System.Linq.IQueryable queryable, ISession session)
      {
         var sessionImp = (ISessionImplementor)session;
         var nhLinqExpression = new NhLinqExpression(queryable.Expression,              
                                     sessionImp.Factory);
         var translatorFactory = new ASTQueryTranslatorFactory();
         var translators = translatorFactory.CreateQueryTranslators(nhLinqExpression.Key, nhLinqExpression, null, false,
                                                                sessionImp.EnabledFilters, sessionImp.Factory);

         var sql = translators.First().SQLString;
         var formamttedSql = FormatStyle.Basic.Formatter.Format(sql);
         int i = 0;
         var map = ExpressionParameterVisitor.Visit(queryable.Expression, sessionImp.Factory).ToArray();
         formamttedSql = Regex.Replace(formamttedSql, @"\?", m => map[i++].Key.ToString().Replace('"', '\''));

         return formamttedSql;
      }

Incommode

Is there even a way to convert a sql string to a hql or to a queryover? – Incommode

Answer 4

Here is how to get generated Sql from Hql with NH 5.2 (a breaking change in NH 4.0.4 appeared which makes the Hql part of the top voted solution obsolete):

public string HqlToSql(string hql, ISession session)
{
    var sessionImp = (ISessionImplementor)session;
    var translatorFactory = new ASTQueryTranslatorFactory();
    var translators = translatorFactory.CreateQueryTranslators(new NHibernate.Hql.StringQueryExpression(hql),
         null, false, sessionImp.EnabledFilters, sessionImp.Factory);
    var hqlSqlGenerator = new HqlSqlGenerator(((QueryTranslatorImpl)translators[0]).SqlAST, sessionImp.Factory);
    hqlSqlGenerator.Generate();
    return hqlSqlGenerator.Sql.ToString();
}

Dynamics