Creating custom column mapping for enumerable types

Since the KitchenPC database adapter has to work with various database systems out of the box, it must use data types common to all dialects for column mappings.  For example, the IngredientForms model has a UnitType property of type Units.  Units is an enum in C#, but will be mapped to a string in SQL.

However, what if we want to use a custom type on our backend as well?  For example, Postgres allows enumerable types to be defined, which could be useful here.

For this reason, KitchenPC comes with an easy way to define NHibernate conventions for enumerable types, which allow the user to override this default mapping.  Let’s start by first defining an ENUM in Postgres for both Units and UnitType:

CREATE TYPE UnitType AS ENUM ('Unit', 'Volume', 'Weight');
CREATE TYPE Units AS ENUM ('Unit', 'Teaspoon', 'Tablespoon', 'FluidOunce', 'Cup', 'Pint', 'Quart', 'Gallon', 'Gram', 'Ounce', 'Pound');

Now, we have a new data type in Postgres that we can use for columns, function parameters, etc. Next, we need to map the C# UnitType enum to the Postgres UnitType enum, as well as Units with Units. We can do this using the handy EnumMapper class that comes with KitchenPC. It has a static method that builds an NHibernate IPropertyConvention instance, so it can be used fluently in the configuration:

var dbConfig = Configuration<DBContext>.Build
   .Context(DBContext.Configure
      .Adapter(DatabaseAdapter.Configure
         .DatabaseConfiguration(
            PostgreSQLConfiguration.PostgreSQL82
               .ConnectionString(@"Server=localhost;Port=5432;User Id=Website;Password=password;Database=KPCSample")
         )
         .AddConvention(EnumMapper<Units>.Convention)
         .AddConvention(EnumMapper<UnitType>.Convention)
      )
   ).Create();

Note we’re calling AddConvention twice, adding a convention to map Units to Units and UnitType to UnitType. What exactly is this class doing under the covers?

The code is pretty simple:

public class EnumMapper<T> : EnumStringType<T>
{
   public override SqlType SqlType
   {
      get
      {
         return new SqlType(DbType.Object);
      }
   }

   public static IPropertyConvention Convention
   {
      get
      {
         return ConventionBuilder.Property.When(
            c => c.Expect(x => x.Type == typeof (GenericEnumMapper<T>)),
            x =>
            {
               x.CustomType<EnumMapper<T>>();
               x.CustomSqlType((typeof (T).Name));
            });
      }
   }
}

First, we’re inheriting from the EnumStringType base class, which is what NHibernate will use by default to map an enum. However, we’re overriding the SqlType to cause NHibernate to treat this type as an object, not as a string. This will prevent NHibernate from doing silly things like trying to cast SQL parameters to text. Without this, we’d have:

INSERT INTO Foo (SomeEnumColumn) VALUES ('Cups'::text);

Where what we want is:

INSERT INTO Foo (SomeEnumColumn) VALUES ('Cups'); -- No cast

The Convention property builds an NHibernate convention that finds all mapped properties of type T, then sets their CustomType as well as the SQL type. The latter will be used to correctly provision the database using the new type. In other words, the CREATE TABLE commands will now use Units and UnitType.

So, there you have it; two lines you can add to your configuration to change how enums are mapped to the database. Two things to keep in mind. First, this code will not create those enums for you. You’ll need to do this yourself, and before you provision your database using InitializeStore. Second, notice we call CustomSqlType using typeof(T).Name, meaning the type name in your database must match the enum type in C#. If you wanted to change this, you’d have to write your own convention to do so.