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.

                             

8 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! ;-)

  • Thanks, very useful.

    It wasn't working for me (.NET 4.6), because in line 3 the sessionProperty variable gets a null value, so i didn't use reflection, and changed first two lines:

    var sessionProperty = typeof(DefaultQueryProvider).GetProperty("Session", BindingFlags.NonPublic | BindingFlags.Instance);
    var session = sessionProperty.GetValue(queryable.Provider, null) as ISession;

    into:

    var prov = queryable.Provider as DefaultQueryProvider;
    var session = prov.Session as ISession;

    I also added the sql parameters in the result. Here the complete code:

    var prov = queryable.Provider as DefaultQueryProvider;
    var session = prov.Session as ISession;
    var sessionImpl = session.GetSessionImplementation();
    var factory = sessionImpl.Factory;
    var nhLinqExpression = new NhLinqExpression(queryable.Expression, factory);
    var translatorFactory = new NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory();
    var translator = translatorFactory.CreateQueryTranslators(nhLinqExpression, null, false, sessionImpl.EnabledFilters, factory).First();
    var sql = translator.SQLString;

    var parameters = nhLinqExpression.ParameterValuesByName;
    if ( (parameters?.Count ?? 0) > 0)
    {
    sql += "\r\n\r\n-- Parameters:\r\n";
    foreach (var par in parameters)
    {
    sql += "-- " + par.Key.ToString() + " - " + par.Value.ToString() + "\r\n";
    }
    }

    return sql;

    Hope this helps.

Add a Comment

As it will appear on the website

Not displayed

Your website