ActiveRecord and a Custom NHibernate PrimitiveType

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.

ActiveRecord Setup

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):

public class CompanyType : NHibernate.Type.PrimitiveType
{
    public static readonly CompanyType Software = new CompanyType() { Description = "Software", Value = 1 };
    public static readonly CompanyType Manufacturing = new CompanyType() { Description = "Manufacturing", Value = 2 };
    public static readonly CompanyType Insurance = new CompanyType() { Description = "Insurance", Value = 3 };
 
    private static readonly CompanyType[] AllTypes = new CompanyType[] { Software, Manufacturing, Insurance };
 
    public string Description { get; set; }
    public int Value { get; set; }
 
    public override string ToString()
    {
        return this.Description;
    }
 
    public CompanyType()
        : base(SqlTypeFactory.Int32)
    {
    }
}

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):

[ActiveRecord]
public class Company : ActiveRecordBase<Company>
{
    [PrimaryKey(Generator = PrimaryKeyType.Identity)]
    public int Id { get; set; }
 
    [Property]
    public string Name { get; set; }
 
    [Property]
    public DateTime InceptionDate { get; set; }
 
    [Property(ColumnType = "ARPrimitiveType.Model.CompanyType, ARPrimitiveType")]
    public CompanyType CompanyType { get; set; }
 
    public override string ToString()
    {
        return this.Name;
    }
}

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:

public override object DefaultValue
{
    get { return CompanyType.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:

public override string ObjectToSQLString(object value, NHibernate.Dialect.Dialect dialect)
{
    CompanyType type = value as CompanyType;
    return type.Value.ToString();
}

The PrimitiveType class indicated the actual type of data stored in the database (a 32-bit integer for our CompanyType):

public override Type PrimitiveClass
{
    get { return typeof(Int32); }
}

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:

public override object FromStringValue(string xml)
{
    return GetCompanyType(xml);
}
 
public override object Get(System.Data.IDataReader rs, string name)
{
    return GetCompanyType(rs[name]);
}
 
public override object Get(System.Data.IDataReader rs, int index)
{
    return GetCompanyType(rs[index]);
}
 
private object GetCompanyType(object val)
{
    if (val == null)
    {
        return DefaultValue;
    }
 
    int v = Int32.Parse(val.ToString());
    return AllTypes.First(x => x.Value == v);
}

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:

public override void Set(System.Data.IDbCommand cmd, object value, int index)
{
    CompanyType type = value as CompanyType;
    DbParameter param = cmd.Parameters[index] as DbParameter;
    param.Value = type.Value;
}

Finally, nHibernate wants to know that type of data this PrimitiveType is exposing to the outside world (our application):

public override Type ReturnedClass
{
    get { return typeof(CompanyType); }
}

We've now implemented a PrimitiveType that will allow our application to program against a CompanyType object while the database deals with an integer.

Sample Code

Now we can create Company objects like this:

Company c = new Company();
c.Name = "ABC Software";
c.InceptionDate = new DateTime(2008, 1, 1);
c.CompanyType = CompanyType.Insurance;
c.Save();

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:

private static void HQLTest()
{
    SimpleQuery<Company> query = new SimpleQuery<Company>(
        "from Company c where c.CompanyType = ?", CompanyType.Manufacturing);
    Company[] results = query.Execute();
}

Or, if you prefer, you can use nHibernate's DetachedCriteria:

private static void CriteriaTest()
{
    DetachedCriteria query = DetachedCriteria.For<Company>("Company")
        .Add(Expression.Eq("CompanyType", CompanyType.Manufacturing));
 
    Company[] results = Company.FindAll(query);
}

Conclusion

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.

No Comments