NHibernate Pitfalls: Identity Identifiers

This is part of a series of posts about NHibernate Pitfalls. See the entire collection here.

IDENTITY columns are well known and used by SQL Server and MySQL users (where it is called AUTO_INCREMENT), among others. It is very useful, because it generates identifiers for us at the database level, so we don’t need to care about them. It is also probably one of the most used identifier generation patterns in NHibernate.

In the context of NHibernate, using IDENTITY has some drawbacks:

• For single entity persistence, an IDENTITY value is fetched from the database as soon as the entity is marked for saving, not just upon flushing the session or committing the transaction; because IDENTITY doesn’t care about transactions (units of work), even if we rollback the current transaction, the obtained identifier will be lost, this is by design;
• We cannot use batching (like in a set with cascading), because after inserting each record, NHibernate needs to issue a SELECT statement in order to find out what the generated identifier was, so that it can hydrate the entity;
• To make things worse, SCOPE_IDENTITY() fails under some circunstances, and, of course, this may also affect NHibernate.