如何让NHibernate仅生成SQL而不执行它？

Question

如何让NHibernate仅生成SQL而不执行它？

sqlnhibernatefluent-nhibernate

63

我知道如何使用 show_sql 配置选项在运行时将 SQL 记录到 log4net/NLog/trace 窗口。

我正在寻找的是一种方法，可以通过将 Query<T>() 传递给 NHibernate 来检索生成的 SQL。

我已经查看了 Persister 类、驱动程序、不同的拦截器和事件。有很多地方可以查找，即使缩小搜索范围也会非常有帮助。

- hometoast

我想要实现的是一种简易版的性能分析器。我只想知道一个特定的linq查询在测试代码中的评估方式。 - hometoast

4个回答

3

如果你想要查看实际的DbCommand，以便检查cmd.CommandText中的SQL和cmd.Parameters中提供的参数，请使用NHibernate 5.2：

//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

1

以下是如何使用NH 5.2从Hql获取生成的Sql（NH 4.0.4中出现了一个破坏性变化，使得最受欢迎的解决方案中的Hql部分已过时）：

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

1

基于 NHibernate 版本 3.4，Linq 表达式的方法为：

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

有没有办法将 SQL 字符串转换为 HQL 或 QueryOver？ - Franki1986

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gerard · Accepted Answer

您可以通过以下方法获取生成的SQL查询语句，而无需执行：

针对NHibernate.Linq查询：

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;
}

对于 Criteria 查询：

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();
}

对于 QueryOver 查询：

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

对于 Hql 查询：

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;
}