NHibernate Pitfalls: Table Per Concrete Type and Native Id Generators

Tuesday, July 5, 2011

.NET NHibernate ORM

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

If you use the Table Per Concrete Type inheritance mapping strategy (<union-subclass/>) you must be aware that the ids you are going to use on each table must always be different, never equal; that is, there cannot be any records on each of the inheritance tables with the same id. The reason is quite easy to understand: if you query NHibernate by the common base class, it must perform a UNION of all the inheritance tables, and will only return the single record that has the desired id (if any). If two records have the same id, NHibernate will return an error:

        1: Animal animal = session.Get<Animal>(100);    //is it a Dog or a Cat?

The solutions are:

Using an id generator strategy that guarantees that ids are different all the time (the HiLo or GUID ones are good candidates);
Using the same sequence generator for all the inheritance tables (in DB engines that support sequences, such as Oracle and PostgreSQL).

You can never use SQL Server and MySQL IDENTITY for this.