This post is part of a small series on .NET ORM tools. You can find the rest of them here.
After messing around with SubSonic, I thought I'd run through the same scenario with NHibernate. A quick disclaimer: both tools a very different, so comparing them is a bit like comparing apples and oranges. As an example, a large part of SubSonic is the generation of DAL classes. There are ways of generating relevant NHibernate artifacts, but they aren't really an integral part of the tool.
Still, I'm not going to let a simple thing like facts get in the way of proceeding. I was interested to see how to perform the same basic scenario as last time around using NHibernate. And as per last time, all of this is really quick and hacky, as it is just to get a little familiarity with the tool rather than to uncover any "best practices" or similar. So here goes...
Scene refresher
I have a table of suppliers, and a table of states (or provinces, territories, prefectures etc.). Both suppliers and states have names, which are stored as strings/varchars, and IDs, which are stored as Guids/uniqueidentifiers. Each supplier can service many states. So we have a simple many-to-many relationship between the two main entities. It looks a bit like this:
I am using Aussie states for my tests, so I have populated the State table with the following names: NSW, VIC, QLD, TAS, SA, WA, ACT, NT.
Setting up NHibernate
I created a new C# class library project and added a reference to NHibernate. I chucked in an app.config for NHibernate's benefit that ended up like this:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="nhibernate"
type="System.Configuration.NameValueSectionHandler, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
/>
</configSections>
<nhibernate>
<add key="hibernate.connection.provider" value="NHibernate.Connection.DriverConnectionProvider" />
<add key="hibernate.dialect" value="NHibernate.Dialect.MsSql2005Dialect" />
<add key="hibernate.connection.driver_class" value="NHibernate.Driver.SqlClientDriver" />
<add key="hibernate.connection.connection_string" value="Data Source=127.0.0.1\SQLEXPRESS;Initial Catalog=SubSonicWorkshop;Integrated Security=True" />
</nhibernate>
</configuration>
Rather than using any NHibernate auto-mapping or code generation, I manually created the classes I wanted. I am using the automatic property accessors in C# 3.0, so if you're still using 2.0 you'll need to manually add a backing store for these.
/* State.cs */
public class State {
public Guid StateId { get; set; }
public String Name { get; set; }
}
/* Supplier.cs */
public class Supplier {
public Guid SupplierId { get; set; }
public String Name { get; set; }
public IList<State> StatesServiced { get; set; }
}
I then need to tell NHibernate how I want to map between these classes and my relational data. You can do this by creating Hibernate Mapping files (.hbm.xml), and setting them to be included as embedded resources in the compiled DLL. Again, I did this manually, but you specify use attributes on your classes to do this auto-magically, or generate the classes, database, and/or mapping files using a variety of tools.
First let's look at the mapping for states (State.hbm.xml):
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="NHibernateWorkshop.Tests"
namespace="NHibernateWorkshop.Tests">
<class name="State" table="State" lazy="false">
<id name="StateId" type="guid">
<generator class="guid" />
</id>
<property name="Name" type="String" />
</class>
</hibernate-mapping>
Here I am telling NHibernate that a State has a StateId, which is the primary key. I am also telling NHibernate that it can generate this ID as a new Guid. I also let it know it can persist the Name property as a String. By default NHibernate will match the property name with the column name, but you can also specify that a property matches to a different column name if you like.
Now for the more interesting entity mapping, Supplier.hbm.xml:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="NHibernateWorkshop.Tests"
namespace="NHibernateWorkshop.Tests">
<class name="Supplier" table="Supplier" lazy="false">
<id name="SupplierId" type="guid">
<generator class="guid" />
</id>
<property name="Name" type="String" />
<bag name="StatesServiced" table="Supplier_StatesServiced">
<key column="SupplierId" />
<many-to-many column="StateId" class="State" />
</bag>
</class>
</hibernate-mapping>
This is very similar to the last mapping, with the exception of the <bag> property. This tells NHibernate that I have a many-to-many relationship between State and Supplier, and that it can navigate this relationship using the Supplier_StatesServiced table and the SupplierId and StateId keys.
Creating a test fixture for messing around
As I mentioned last time, I like working in test fixtures, so I created a new test fixture to run the remainder of the code in this post. Before I start using NHibernate within the fixture, I want to get access to the NHibernate ISession. I guess you could think of ISession as a conversation between NHibernate and the database. I'll do this by exposing a ISessionFactory property (note, there are optimal ways of doing this... this ain't it):
protected static readonly ISessionFactory SessionFactory = initialSessionFactory();
private static ISessionFactory initialSessionFactory() {
Configuration config = new Configuration();
config.AddAssembly("NHibernateWorkshop.Tests");
return config.BuildSessionFactory();
}
I am going to use this property to create sessions as required. The AddAssembly(...) call gets NHibernate to load in all the .hbm.xml mapping files we created earlier, so the sessions we create know how to persist our entities.
Populating the database
Now we can get to work populating our database (I cleaned it out from last time). We are now on par with the SubSonic example. The steps and method signatures are going to be very similar from here on in. So like last time, I'll write a method to encapsulate the process of creating a supplier and mapping the states it services:
private static void createSupplier(String name, String[] statesServiced) {
using (ISession session = SessionFactory.OpenSession())
{
Supplier supplier = new Supplier();
supplier.Name = name;
IList<State> states = session
.CreateCriteria(typeof (State))
.Add(
Expression.In("Name", statesServiced)
)
.List<State>();
supplier.StatesServiced = states;
session.SaveOrUpdate(supplier);
session.Flush();
session.Close();
}
}
Wow. To me all that additional setup now starts to seem worth it. I create a normal object and set the name property. I then create a criteria object that is going to query states, and add a restriction that the state's name must be in the array of statesServiced we passed in. I then assign the results to the supplier object, and ask the NHibernate session to SaveOrUpdate the supplier. (SaveOrUpdate means NHibernate will automatically determine whether it needs to INSERT or UPDATE the relevant database record.) As ISession represents a chat with a database, the whole thing is wrapping in a using (...) { } to ensure any resources used are cleaned up nicely.
I then used the following code to insert the same data as last time:
createSupplier("Dave^2 Quality Tea", new string[] { "NSW", "VIC" });
createSupplier("ORMs'R'Us", new string[] { "NSW" });
createSupplier("Lousy Example", new string[] { "TAS", "VIC" });
createSupplier("Bridge Sellers", new string[] { "QLD" });
Querying the data
Let's get a list of all the suppliers:
[Test]
public void Should_be_able_to_get_all_suppliers() {
using (ISession session = SessionFactory.OpenSession()) {
IList<Supplier> suppliers = session.CreateCriteria(typeof (Supplier)).List<Supplier>();
Assert.That(suppliers.Count, Is.EqualTo(4));
}
}
That's pretty much on a par with the SubSonic equivalent. The test passes. Now let's get all the suppliers with an "s" in their name:
[Test]
public void Should_be_able_to_get_all_suppliers_with_s_in_their_name() {
using (ISession session = SessionFactory.OpenSession()) {
IList<Supplier> suppliers = session
.CreateCriteria(typeof (Supplier))
.Add(Expression.Like("Name", "%s%"))
.List<Supplier>();
Assert.That(suppliers.Count, Is.EqualTo(3));
}
}
Again, pretty simple, and very similar to the SubSonic version. Now to the tricky example. We want to navigate over the supplier-state relationship and get all the suppliers that service NSW. This one wasn't pretty in SubSonic.
[Test]
public void Should_be_able_get_all_suppliers_that_service_NSW() {
using (ISession session = SessionFactory.OpenSession()) {
IList<Supplier> suppliers = session
.CreateCriteria(typeof (Supplier))
.CreateCriteria("StatesServiced")
.Add(Expression.Eq("Name", "NSW"))
.List<Supplier>();
Assert.That(suppliers.Count, Is.EqualTo(2));
}
}
Again, wow. That was too easy. First we create a criteria that is going to return Supplier data. Then we want a sub-criteria that is going to use the StatesServiced property of the Supplier entity (not the table -- we are strictly dealing in domain objects here). We then say we only want the StatesServiced collection to include NSW. So what SQL ends up hitting the database?
SELECT {fields for supplier and state}
FROM Supplier
INNER JOIN Supplier_StatesServiced ON
Supplier.SupplierId=Supplier_StatesServiced.SupplierId
INNER JOIN State ON Supplier_StatesServiced.StateId=State.StateId
WHERE State.Name = @p0
This is the query executed via a call to sp_executesql on SQL Server (slightly modified to better communicate the point, but the actual generated query is still very neat), with "NSW" passed as @p0.
This was very different to the experience with SubSonic, which I couldn't get to sensibly navigate over the many-to-many relationship.
Vague semblance of a conclusion
Ignoring the initial setup and configuration (as this can all be generated, but to be honest it was all fairly quick), I really enjoyed working with NHibernate. While the querying looked a little daunting to me at first glance, after about 3 seconds I found it very intuitive. Sometimes when I was working with SubSonic I found myself scratching my head as to how to get back specific information. Not so with NHibernate, as the query options I needed* were all fairly discoverable, and I never felt like I was going to have to fight the tool to get stuff out.
Again, there are vast differences between the SubSonic and NHibernate approaches, and there a probably situations to which one is better suited than the other. Rob Conery has a good post about the strengths and weaknesses of both tools, as well as LinqToSql.
Here are a couple of chapters from the official documentation that I used to get NHibernate going:
* Luckily I didn't need Projections or similar. That may have started to get a bit hairier.
