See your SQL queries when using NHibernate with LINQPad

Tom Chantler -

Summary

When using LINQPad to communicate with a Microsoft SQL Server (MSSQL) database via NHibernate, it's not straightforward to see the actual SQL queries which are being run against the database. It's even more difficult to get that code into an intelligible state that you can simply cut, paste and execute in a separate query window.

This article presents a simple way to fix that. There are two NuGet packages and a GitHub repository:

I've included a sample database project and a LinqPad query file, so you can do the whole thing from soup to nuts (I always think of this film when encountering this phrase) without having any previous experience of LINQPad or NHibernate. You don't even need an existing database.

Background

When I write .NET code that needs to communicate with an MSSQL database, I tend to use either Entity Framework or Dapper.NET. But since No man is an Island [1], over the years I have also used various other tools for this purpose, including NHibernate. Before we go any further, let's just see what we're dealing with.

It's readily apparent that by using a combination of LINQPad and our own NHibernate assemblies we have the potential to reduce development effort considerably by creating a platform that facilitates rapid changes to our ORM code.

Ordinarily, if you want to communicate with a database when you are using LINQPad, you can click Add connection in the left-hand menu and follow the on-screen prompts, either to create a data context automatically, or to use a typed data context from your own assembly.

This latter option is good if you have existing code and you want to make changes and still be able to see exactly what's going on under the hood, without having to recompile your entire solution.

As you can see, there doesn't seem to be an option to do this with NHibernate.

There are two distinct ways you can attempt to solve this problem. You could write a custom data context driver for LINQPad, or you could reference your assemblies directly.

However, if you just add references to your assemblies then you won't ever see anything in the SQL tab in LINQPad. If you set show_sql=true in your NHibernate configuration, then you will see something like this in the Results tab of LINQPad:

NHibernate: INSERT INTO "Author" (Title, FirstName, LastName) VALUES (@p0, @p1, @p2); select last_insert_rowid();@p0 = 'Sir' [Type: String (0)], @p1 = 'Arthur' [Type: String (0)], @p2 = 'Conan Doyle' [Type: String (0)]

That's almost useful, but there's quite a bit of editing to do if you want to run that code anywhere. A better solution is to intercept the output from NHibernate, tidy it up a bit and then put it into the SQL tab in LINQPad.

What you need to do

If you just want to get up and running really quickly with some sample code, then it's as simple as this:

  • Download and install LINQPad from here.

  • Run LINQPad, create a new C# program, press F4, click Add NuGet... and search for LINQPad.NHibernate and add the LINQPad.NHibernate.FormatSQL NuGet package to your query, like this:

This will add a sample LINQPad file, as shown below. Click on it.

It will prompt you to download the LINQPad.NHibernate.FormatSQL.Sample NuGet package:

Choose Yes.

  • Finally press F5 to run the code.

NOTE: If you're running my sample code (which uses SQLite) then you need to heed the warning which is included in that code. Download and extract the relevant files and set the path in your query as shown below. If you aren't using SQLite then you don't need to set this environment variable.

Here's the warning taken from the LINQPad code sample:

SQLite needs a system environment variable to be set in order to work correctly in LINQPad (otherwise it can't find SQLite.Interop.dll). The best way to do this is to download the NuGet package from here: https://www.nuget.org/packages/System.Data.SQLite.Core/1.0.97, extract it
(remember, .nupkg files are just zip files) and point to the relevant framework directory like this:

var path = @"C:\Users\Tom\Downloads\system.data.sqlite.core.1.0.97\build\net45";
System.Environment.SetEnvironmentVariable("PreLoadSQLite_BaseDirectory", path);

The first time it runs successfully it will do something like this:

Native library pre-loader is trying to load native SQLite library "C:\Users\Tom\Downloads\system.data.sqlite.core.1.0.97\build\net45\x86\SQLite.Interop.dll"...

And that's it. Now when you run your query and click on the SQL tab in LINQPad you will see the generated SQL in a format that you can copy and run without modification.

If you already have an NHibernate data context

  • Make sure you are setting show_sql=true and format_sql=false in your NHibernate configuration.

  • Press F4 and add the LINQPad.NHibernate.FormatSQL NuGet package to your query.

  • Wrap your LINQPad query in the following using statement:

using (var nHibernateSqlOutputRedirector = new NHibernateSqlOutputRedirector())
{
    ...
}

Possible Further Work

The SQL generated by LINQPad uses rather strange table and column aliases. It might be worth spending a little bit of time tweaking this to be more intuitive.

Conclusion

LINQPad is a really useful code scratchpad which I use a great deal, but its limited support for NHibernate means you can't easily see the resultant SQL code it generates and runs against your database. By installing this simple NuGet package, you can fix that problem and the generated SQL will be presented in a form which you can copy and run directly against your database without modification. This could be useful in all sorts of ways.

I wrote this over a couple of evenings, it works for me and I present it to you for free. Hopefully it's already worth slightly more than you're paying for it, but if you can see a way it can be improved then please let me know.

  1. John Donne - https://en.wikipedia.org/wiki/Devotions_upon_Emergent_Occasions ↩︎