I was intrigued by Steve Smith's blog post yesterday about reducing SQL Lookup tables in nHibernate. He gave an example of a WorkOrderStatus class the exposed the actual status as a POCO object that wasn't stored in the database. What really piqued my interest was the following comment:
NHibernate can map this status directly if you create a WorkOrderStatusType class that inherits from NHibernate.Type.PrimitiveType and overrides its methods.
I never knew nHibernate supported this type of feature (never needed it or thought about it). As I'm an avid ActiveRecord user, I decided to see how I would implement a custom nHibernate PrimitiveType and utilize it via ActiveRecord. Turns out it was pretty easy! The full source is available from my GoogleCode page either through SVN or simply a ZIP download.
A quick note before we begin: I didn't find a whole lot of documentation on extending PrimitiveType and implementing your own. I reviewed some nHibernate code and I think I got the general implementation right, but can't be sure it'll work 100% of the time. It was a proof-of-concept project.
I decided I'd use SQLite for this sample since it's perfect for this type of job -- small, compact and no install required. I can poke around the database to check schema and data using the SQLite addon for Firefox.
Instead of stealing Steve's WorkOrderStatus, I decided to go with a schema that has a simple Company object, and that Company object has a CompanyType defined. Instead of defining a lookup table just for company types, I'll create a CompanyType class that derives from NHibernate.Type.PrimitiveType and let nHibernate do the loading/saving.
First, the CompanyType. For this demo, it's a simple object with a Description (string) and a Value (integer). The Value is what is actually saved to the database (note: this isn't the entire class -- just the basics):
I've defined an AllTypes that I'll use to find the matching CompanyType when nHibernate reads the integer from the database. The ctor calls the base class ctor and tells nHibernate what data type this new PrimitiveType is based on (the schema in the database will be an integer). I also overrode ToString() to return the Description property to make debugging easier.
The Company record is pretty simple too. When we get to the CompanyType, we tell ActiveRecord (which works through nHibernate) the column type for the column (our custom PrimitiveType):
Implementing the required methods in CompanyType was pretty easy. I'm not sure when DefaultValue is used, so I just return a CompanyType of Software:
ObjectToSQLString seems to want to convert your PrimitiveType (CompanyType) to a string value that can be used by the database. So we'll convert our Value property to a string:
The PrimitiveType class indicated the actual type of data stored in the database (a 32-bit integer for our CompanyType):
The FromStringValue and two Get overloads both need to do the same thing: Take a representation of the database value and convert it to our PrimitiveType (a CompanyType). For this I created a single method that converts the database integer back to a CompanyType instance using a LINQ query on the AllTypes array:
Next, nHibernate needs to know how to stick a CompanyType into the database. In the Set method, we take our "Value" property and place it in the IDbCommand.Parameters collection:
Finally, nHibernate wants to know that type of data this PrimitiveType is exposing to the outside world (our application):
We've now implemented a PrimitiveType that will allow our application to program against a CompanyType object while the database deals with an integer.
Now we can create Company objects like this:
If you look in the database, the Company table has a field called "CompanyType". It's an integer and the record above will populate the CompanyType with a value of 3.
You can use this object in HQL queries too:
Or, if you prefer, you can use nHibernate's DetachedCriteria:
This was a fun demo project to create. It's always nice to learn something new about a tool you use often (ActiveRecord/nHibernate). This was a good proof-of-concept, but needs a lot more testing and error handling before it could be used in production. You're free to use the code as-is, but there are no guarantees as to its correctness.
If anyone is aware of any errors I've made in implementing a custom PrimitiveType, please feel free to let me know.