fluent nhibernate auto increment non key (Id) property

Question

Is it possible to have an integer property of a class auto increment managed by the database but not be a primary key (or Id as NHibernate refers to them)? I'm having trouble finding examples of how to do this. Any help would be appreciated. Thanks.

Answer 1

Two options.

• if the database is 100% responsible for this you just need to tell NHibernate that the property is generated and not to include it in any updates/isnerts. The downside is that NH will need to do an additional select to keep the value fresh in memory.

  < property name="Foo" generated="always" update="false" insert="false" />

• if the database is not responsible and you just want to have this done automatically you can use an interceptor to set the value to 1 on an insert and to increment it by 1 on an update.

http://www.nhforge.org/doc/nh/en/index.html#objectstate-interceptors (11.1 - Interceptors)

You would override OnSave() to find the property and set the initial value and then override OnFlushDirty() to find the property property and increment.

---

Edit:

I'm an idiot, didn't notice you said Fluent NHibernate.

---

Edit #2:

I think you might also be interested in using this column as a versioning?

< version name="Foo" generated="always" />

Answer 2

This works for me:

public class Potato
{
    public virtual Guid Id { get; protected set; }
    public virtual int LegacyId { get; protected set; }
}

public class PotatoMap : ClassMap<Potato>
{
    public PotatoMap()
    {
        Id(x => x.Id).GeneratedBy.GuidComb();
        Map(x => x.LegacyId).CustomSqlType("INT IDENTITY(1,1)").Not.Nullable().ReadOnly().Generated.Insert();
    }
}

Basically, the integer is set to be generated by the database and NHibernate is instructed to retrieve it on inserts.

It is important to note that the mapping is only half of the answer, and it will not work if the column is not created as an IDENTITY. CustomSqlType is added to the mapping with the purpose of creating the proper sql when generating the tables with SchemaExport. This is the generated sql:

 create table [Potato] (
         Id UNIQUEIDENTIFIER not null,
        LegacyId INT IDENTITY(1,1) not null,
        primary key (Id)
     )

On the other side, ReadOnly and Generated.Insert() will tell NHibernate that the value is autogenerated by the database only on inserts, and therefore it has to query the database for the value at every insert.

Note that I only tested this with Sql Server. The custom type will probably change or may not be available in other databases.