62

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.

hometoast
  • 11,522
  • 5
  • 41
  • 58
  • 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. – hometoast May 22 '12 at 15:00

4 Answers4

118

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;
}
Gerard
  • 2,461
  • 2
  • 26
  • 34
  • Is there a way to do it for HQL queries? – Katie Kilian May 22 '12 at 21:56
  • Updated my answer with hql queries. – Gerard May 22 '12 at 22:03
  • 1
    @Gerard I have upvoted your very useful answer. Do you have any idea if this is possible for INSERTs - UPDATEs? See http://stackoverflow.com/questions/10786934/how-can-i-get-nhibernate-to-give-me-the-sql-it-would-generate-for-an-insert-up Thank you! – Alvaro Rodriguez May 28 '12 at 15:19
  • 1
    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. – Jason Freitas Apr 25 '13 at 12:21
  • This is something I've been hunting after for a while. Thank you kindly :) – Ran Sagy May 20 '13 at 04:51
  • 14
    Is there any way to enforce `GetGeneratedSql(ICriteria criteria)` to return query with named parameters instead of placeholders (`?`)? – Michał Powaga Oct 14 '14 at 13:03
  • 2
    Is there any way reverse this ? , I mean generated sql string to QueryOver or entities ? – Oğuzhan Soykan Apr 17 '15 at 14:58
  • 2
    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. – NexAddo Feb 17 '16 at 17:19
  • Edited the NHibernate.Linq queries method for the new signature of 'CreateQueryTranslators' in Nhibernate 4.0.4 – Gerard Feb 17 '16 at 20:46
  • 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 – Homayoun Behzadian May 03 '18 at 09:57
  • 1
    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? – quetzalcoatl Sep 06 '18 at 16:16
  • @quetzalcoatl, to do it using NH 5.2, I edited the answer – Sÿl Apr 04 '19 at 11:43
  • @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. – quetzalcoatl Apr 04 '19 at 17:50
  • @quetzalcoatl you're right it must have been rejected, new answer below – Sÿl Apr 05 '19 at 19:49
3

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;
    }
}
Roman Artiukhin
  • 2,200
  • 1
  • 9
  • 19
1

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;
      }
Franki1986
  • 1,320
  • 1
  • 15
  • 40
1

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();
}
Sÿl
  • 585
  • 6
  • 8