How to Know Query Generated by Fluent Nhibernate

How to know query generated by Fluent NHibernate

If you want the SQL to be in log4net, make sure you set the logger in your configuration section.

I put the NHibernate package at "INFO" to reduce the noise and the NHibernate.SQL to all so I can log all SQL statements.

  
 <logger name="NHibernate">
   <level value="INFO" />
 </logger>
  <logger name="NHibernate.SQL">
    <level value="ALL" />
  </logger>

How do I view the SQL that is generated by nHibernate?

You can put something like this in your app.config/web.config file :

in the configSections node :

<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net"/>

in the configuration node :

<log4net>
  <appender name="NHibernateFileLog" type="log4net.Appender.FileAppender">
    <file value="logs/nhibernate.txt" />
    <appendToFile value="false" />
    <layout type="log4net.Layout.PatternLayout">
      <conversionPattern value="%d{HH:mm:ss.fff} [%t] %-5p %c - %m%n"  />
    </layout>
  </appender>
  <logger name="NHibernate.SQL" additivity="false">
    <level value="DEBUG"/>
    <appender-ref ref="NHibernateFileLog"/>
  </logger>
</log4net>

And don't forget to call

log4net.Config.XmlConfigurator.Configure();

at the startup of your application, or to put

[assembly: log4net.Config.XmlConfigurator(Watch=true)]

in the assemblyinfo.cs

In the configuration settings, set the "show_sql" property to true.

How to configure Fluent NHibernate to output queries to Trace or Debug instead of Console?

I can see from forum and blog posts everywhere that lots of others before me have looked for a way to get the SQL statements as they're being prepared for execution. The answer typically is something along the lines of "you can't", or "you shouldn't".

Whether I should or not, that's what I wanted.

After hours of searching, investigation and failed attempts, and finally I came up with this.

Write up an interceptor:

using NHibernate;
using System.Diagnostics;

public class SqlStatementInterceptor : EmptyInterceptor
{
    public override NHibernate.SqlCommand.SqlString OnPrepareStatement(NHibernate.SqlCommand.SqlString sql)
    {
        Trace.WriteLine(sql.ToString());
        return sql;
    }
}

Of course, you don't have to Trace.WriteLine() here, you could write it to a log file, or whatever else you need.

In your connection manager, hook up your Interceptor like so:

protected virtual void Configure(FluentConfiguration config)
{
    config.ExposeConfiguration(x =>
                                   {
                                       x.SetInterceptor(new SqlStatementInterceptor());
                                   });
}

It's not that complicated. From my perspective, certainly easier than trying to get all this XML pushed through Fluent to NHibernate - since Fluent abstracts the XML file away.

Keep in mind, you can only have a single Interceptor - so you may need to integrate this feature with your existing Interceptor, if you already have one. On that note, you might want to give it a broader name - e.g. MyAppInterceptor, so as not to imply a specific purpose, because you may want to add other features to it later.

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

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

Fluent NHibernate does not create IN part of WHERE clause

Use Any:

 return session.Query<MyObject>().Where(x => array.Any(y => y == x.CompareVal)).ToList();

Your repository pattern (using plain Func) automatically materializes your query to list, if you want something to be deferredly executed, use IQueryable, don't use Func only

Something to note - I have a Generic Repository class that all of
these calls are funneled through. The Query method is as follows:

public IList<T> Query(Func<T, bool> criteria)
{
  using (var session = SessionProvider.SessionFactory.OpenSession())
  {
    return session.Query<T>().Where(criteria).ToList();
  }
}

Your repository just mimic what is already provided out of the box by NHibernate

Fluent NHibernate One-to-Many Mapping Results in Bad Query

The JOIN is not suitable in this scenario. It will produce SELECT resulting in more rows, then the Parent table has. I.e: each child of a parent will append new row.

The appropriate solution here is to keep these selects separated. Firstly select the parent, then load all children for all parents (just selected parents). NHibernate does have nice solution for this:

• 19.1.5. Using batch fetching
• Fluent version described here: How to Eager Load Associations without duplication in NHibernate?

This would work a bit similar as you experienced. Firstly select parent, then go for child(ren). The biggest difference is, that instead of 1 + N (going for each children collection separately) we now go for more children in batches. so it could be 1 + 2

Examples of the batch size mapping

1) Colleciton

HasMany<Child>(x => x.Children)
  .BatchSize(25);

2) Entity itself

public ChildMap()
{
    Id(x => x....
    ...
    BatchSize(25);

There are many other advantages, where the PAGING is one of the most profitable. Because we are working with a flat (not multiplied) Parent, we can apply .Take() and .Skip()

Finally, fi we would like to find some Parents based on their children, we can use in this scenario Subqueries: 15.8. Detached queries and subqueries

---

Related Topics