How to check converted sql query in linq to NHibernate?

Question

I am using Linq to NHibernate.

I have a following test case :

[TestMethod]
[DeploymentItem("hibernate.cfg.xml")]
public void Category_Should_GetAllByLinq()
{
  // Arrange
  IRepository<Category> repo = new CategoryRepository();

  //Act
  IList<Category> list = repo.GetListByLinq();

  //Assert
  Assert.IsTrue(list.Count > 0);
}

and also I have following code in CategoryRepository class :

public IList<Category> GetListByLinq()
{
  ISession session = NHibernateHelper.OpenSession();

  // When following statement is executed, How to check converted real sql query?
  var query = from c in session.Linq<Category>()
                        select c;

  return query.ToList();
}

My question is that once linq to nhibernate statement is executed, How to check real converted sql query? any method?

I know I can use SQL profiler, However I'd like to use Mock object in test case so I don't want see it in any Database related method.

Answer 1

Have you tried NHiberate Profiler? It should be what you are looking for.

Answer 2

Good question.

I think the best way to assert the SQL generated would be to use IInterceptor and hook into OnPrepareStatement.

Or if it is ok for you to just see the SQL statements in the Output window during debugging you can just enable show_sql option.

Answer 3

You can use the NHibernate Profiler to do so, see this post for details on how to setup programmatic integration: http://ayende.com/Blog/archive/2009/12/13/uumlberprof-new-feature-programmatic-integration.aspx

Another option, slightly more complicated, is to listen to the NHibernate.SQL logger and process its results. It gives you the information, but you would need a bit of parsing and it won't give you the sessions information