Friday, April 18, 2008

LINQ to NHibernate in LINQPad

LINQPad is like Query Analyzer for LINQ queries. Out of the box it does LINQ to SQL, LINQ to Objects, and LINQ to XML. Wouldn't it be nice if it did LINQ to NHibernate as well? Here's how. The setup process is a bit tedious, but you only need to do it once.

Get a working copy of LINQ to NHibernate

If you haven't done so already, use Subversion to check out LINQ to NHibernate from and build it. If you run NHibernate.Linq.Tests.exe, the MbUnit AutoRunner will run through all the tests and display an HTML report of the test results. I get 134 passing tests and 31 failing tests which is to be expected since not all of the LINQ features have been implemented yet, but if you check the commit logs you will see this is actively being worked on. Note that you will need a standard Northwind database (get it here if you don't already have it), you will need to create a new database named Test, and you may need to modify the connection strings in App.config to match your setup.

Add assembly references

Once you've got LINQ to NHibernate working, open LINQPad and press F4 to bring up Advanced Query Properties. Add references to NHibernate.dll, NHibernate.Linq.dll, and the assemblies containing your entities and your data context. In this example, those would be Northwind.Entities.dll and NHibernate.Linq.Tests.exe respectively. Note that when you click Browse to add an assembly reference, you will need to enter *.* in the File Name textbox and press enter to change the file type filter from *.dll to all file types so you can add the Tests.exe reference.

Import namespaces

While you're in the Advanced Query Properties dialog, go to the Additional Namespace Imports tab and enter namespace imports for NHibernate.Cfg and NHibernate.Linq.Tests.Entities. You may want to click the "Set as default for new queries" button in the lower left so you don't need to set up these assembly and namespace references the next time you start LINQPad. Alternatively, when you save a LINQPad .linq query file it will save these references and reload them the next time you open the .linq query file. This can be handy for switching between different databases and data contexts.

Resolve connection string config issues

If you provide NHibernate with connection strings from App.config, LINQPad will not be able to automatically pick these up so you will need to tweak your hibernate.cfg.xml file to contain the actual connection string instead of a named connection string reference. This involves renaming "connection.connection_string_name" to "connection.connection_string" and changing the value to the connection string found in your App.config. If you don't want to mess up your real hibernate.cfg.xml file, make a copy and modify the copy.

Bootstrap the NHibernate data context

In LINQPad, change the query type to "C# Statement(s)" and paste the following code, modifying the path and name of your hibernate.cfg.xml file as necessary:

var cfg = new Configuration().Configure(@"C:\NHibernate.Linq\NHibernate.Linq.Tests\bin\Debug\hibernate.cfg.xml");
var factory = cfg.BuildSessionFactory();
var db = new NorthwindContext(factory.OpenSession());

var q =
from c in db.Customers
where c.City ==
orderby c.CustomerID
select new { c.CustomerID, c.CompanyName };

Press F5 or Ctrl+E to run this and you should see the following result:


The SQL generated by NHibernate is displayed above the results due to the show_sql=true line in hibernate.cfg.xml. Be sure to save this "query template" as a LINQPad .linq file so you don't have to go through this process again!


Garret said...

Hey, we're hiring some .NET developers to work on a pretty neat project up here in Alaska.

Medical devices, nifty software all around, good group of developers... it's pretty awesome.

Sorry for spamming your blog, but you should let folks know!

Oran Dennison said...

Off-topic! -1 Troll. Oh wait, that was my boss. ;-)

Yes it's true, we're hiring, and I do think this is the most interesting .NET job in Alaska. I should note that this is commercial product development so our standards are pretty high, but that's part of what makes it fun!

Anonymous said...

do you know where the nhibernate.linq moved to?

that link doesn't have it

Oran Dennison said...

Thanks for noticing the broken link. NHibernate.Linq was recently moved into the NHibernate Contrib project. You can check out the latest source here.

I have also updated the post with the new link.

Anonymous said...

thanks for the help setting this up. one thing i noticed right away after trying it out is that if you have nhibernate mapped to lazy load child collections expect the entire object graph to be traversed :(

would love to hear if there is a way to stop that from happening!

Joe Albahari said...

To stop LINQPad traversing the whole object graph, implement LINQPad.ICustomMemberProvider

Note that you don't have to reference LINQPad for this to work - the interface is "duck-typed".