Additional identity column with NHibernate

As always, business requirement went over existing code base to ask for a minor change that wasn’t compatible with our existing infrastructure: all surrogate keys are unique identifiers (Guid) and all infrastructure was built around that. The business people asked to add to an existing domain entity a natural key - integer identity number (auto increment field), to serve as a reference number.

We had two choices:

• to generate the identity from business code
• to let database manage it for us.

The second approach it preferable for us because it’s the database’s job to generate the keys.

Out of the box NHibernate doesn’t support such a thing. Answers to my question in NHibernate Users discussion group led to the next solution.

Given the class:

public class Entity
{
    public int Id { get; set; }
    public int Id2 { get; set; }
}

First “Id” is the primary key, the second is the key that we want to map to identity column. To have that done we should do two things:

• Map “Id2” property as “generated”
• Use “database-object” to drop the column generated by NHibernate & create new one, with IDENTITY set on.

The complete mapping:

<hibernate-mapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    namespace="NHibernate.Playground"
    assembly="NHibernate.Playground"
    default-lazy="false" xmlns="urn:nhibernate-mapping-2.2">
  <class name="Entity">
    <id name="Id">
      <generator class="increment" />
    </id>
    <property name="Id2" type="int" generated="always" insert="false" />
  </class>
  <database-object>
    <create>
      ALTER TABLE Entity DROP COLUMN Id2
      ALTER TABLE Entity ADD Id2 INT IDENTITY
    </create>
    <drop>
      ALTER TABLE Entity DROP COLUMN Id2
    </drop>
  </database-object>
</hibernate-mapping>

I like the flexibility offered by NHibernate. Even if you don’t have required feature, you can always use naked ADO.NET or raw SQL.