Getting the SQL from a LINQ Query in NHibernate

In case you ever want to have a look at the generated SQL before it is actually executed, you can use this extension method:

   1: public static String ToSql(this IQueryable queryable)
   2: {
   3:     var sessionProperty = typeof(DefaultQueryProvider).GetProperty("Session", BindingFlags.NonPublic | BindingFlags.Instance);
   4:     var session = sessionProperty.GetValue(queryable.Provider, null) as ISession;
   5:     var sessionImpl = session.GetSessionImplementation();
   6:     var factory = sessionImpl.Factory;
   7:     var nhLinqExpression = new NhLinqExpression(queryable.Expression, factory);
   8:     var translatorFactory = new ASTQueryTranslatorFactory();
   9:     var translator = translatorFactory.CreateQueryTranslators(nhLinqExpression, null, false, sessionImpl.EnabledFilters, factory).First();
  10:     //in case you want the parameters as well
  11:     //var parameters = nhLinqExpression.ParameterValuesByName.ToDictionary(x => x.Key, x => x.Value.Item1);
  12:  
  13:     return translator.SQLString;
  14: }

Just call it on an IQueryable or IQueryable<T> instance, such as the one you got from ISession.Query<T>:

   1: var query = session.Query<Product>().Where(p => p.Price > 100);
   2: var sql = query.ToSql();

Parameters such as 100 will be located in the nhLinqExpression.ParameterValuesByName collection.

                             

7 Comments

  • Offtopic: I have no idea how to contact you so I' am writing it here. Can you please fix your RSS. I am getting double posts since a month or so. 10x

  • Hi, mynkow! Thanks, I'll see what I can do

  • @mynkow: My RSS feed appears to be OK. Can you still reproduce the problem?

  • What version of NHibernate is this for? I'm using 3.3.1.4000 and my ASTQueryTranslatorFactory.CreateQueryTranslators() methods all take in a *string* as the first parameter, not an NhLinqExpression.

  • Hi, Brian!
    Well, this works with 4, and used to work before that... maybe 3.4?
    There are two overloads of CreateQueryTranslators:

    public IQueryTranslator[] CreateQueryTranslators(IQueryExpression queryExpression, string collectionRole, bool shallow, IDictionary<string, IFilter> filters, ISessionFactoryImplementor factory);
    public IQueryTranslator[] CreateQueryTranslators(string queryString, string collectionRole, bool shallow, IDictionary<string, IFilter> filters, ISessionFactoryImplementor factory);

  • Thanks, Ricardo! I'm in the process of transitioning from Criteria API to LINQ to NHibernate, and this little method has been very helpful.

    Always easier when you can take a peek under the hood.

  • Hi, Andy!
    Glad you liked! Keep dropping by! ;-)

Add a Comment

As it will appear on the website

Not displayed

Your website