Translating NHibernate LINQ query to SQL

September 1, 2020September 1, 2020 Gunnar Peipman 367 Views 42 Comments

When working with stateless sessions in NHibernate we need some way to see generated SQL but we cannot use simple tricks as custom NHibernate interceptor to log SQL queries. As I’m using mostly NHibernate LINQ these days I was interested in how to get SQL out from LINQ query without actually executing it. Here’s my solution.

ToSql() extension method

I wrote simple ToSql() extension method for IQueryable generated by NHibernate LINQ. It also writes out parameters.

public static class NHibernateExtensions
{
    public static string ToSql(this IQueryable query)
    {
        var provider = query.Provider as DefaultQueryProvider;
        if(provider == null)
        {
            return null;
        }
 
        var sessionImpl = provider.Session;
        var factory = sessionImpl.Factory;
        var linqExpression = new NhLinqExpression(query.Expression, factory);
        var translatorFactory = new ASTQueryTranslatorFactory();
        var translator = translatorFactory.CreateQueryTranslators(linqExpression, null, false, sessionImpl.EnabledFilters, factory).First();
        var parameters = linqExpression.ParameterValuesByName.ToDictionary(x => x.Key, x => x.Value.Item1);
 
        var result = translator.SQLString;
        foreach (var parameter in parameters)
        {
            result += "\r\n" + parameter.Key + ": " + parameter.Value;
        }
 
        return result.Trim();
    }
}

ToSql() extension method uses only classes needed to translate LINQ expression tree to SQL.

Using ToSql() extension method

The following piece of code shows how to use ToSql() extension method. To find out more about my IDataMapper interface that mimics DbContext of Entity Framework Core, please read my blog post NHibernate on ASP.NET Core.

var projectQuery = _dataMapper.AsNoTracking()
                              .Projects
                              .Fetch(c => c.Customer)
                              .Fetch(c => c.Mediator)
                              .Where(p => p.Id == id);
 
var projectSql = projectQuery.ToSql();
var projects = await projectQuery.FirstOrDefaultAsync();

When projectSql variable is evaluated the query is not executed. It is only translated to SQL. Query is executed when projects variable is evaluated as then data is actually asked from projectQuery.

Here’s the result of ToSql() extension method.

select 
    project0_.Id as id1_2_0_, 
    customer1_.Id as id1_1_1_, 
    customer2_.Id as id1_1_2_, 
    project0_.Title as title2_2_0_, 
    project0_.ProjectKey as projectkey3_2_0_, 
    project0_.IsActive as isactive4_2_0_, 
    project0_.CustomerId as customerid5_2_0_, 
    project0_.MediatorId as mediatorid6_2_0_, 
    project0_.Deadline as deadline7_2_0_, 
    project0_.Description as description8_2_0_, 
    customer1_.Name as name2_1_1_, 
    customer2_.Name as name2_1_2_ 
from 
    Projects project0_ 
    left outer join Customers customer1_ on 
        project0_.CustomerId=customer1_.Id 
    left outer join Customers customer2_ on 
        project0_.MediatorId=customer2_.Id 
where 
    project0_.Id=?
 
p1: d9571e6b-fa73-4553-9c90-57aa7c050298

NB! ToSql() extension method doesn’t give formatted SQL. It gives it like it is for executing. You can use Poor Man’s T-SQL Formatter service to format queries returned by ToSql() extension method.

Wrapping up

Translating NHibernate LINQ expression tree to SQL without executing query against database server is trick we can use to investigate generated SQL when writing complex queries. I’m using ToSql() extension method also when optimizing slow queries to find out what was actually generated by NHibernate. For more serious work I recomment using advanced tools to monitor NHibernate.

Gunnar Peipman

Gunnar Peipman is ASP.NET, Azure and SharePoint fan, Estonian Microsoft user group leader, blogger, conference speaker, teacher, and tech maniac. Since 2008 he is Microsoft MVP specialized on ASP.NET.