Thursday, April 14, 2011

Letting the database pick the primary key in nhibernate

How do I just simply allow MySQL to assign a primary key to an inserted object with nhibernate? It seems I would want to set the generator as a type "identity", but the documentation states that using this "..require[s] two SQL queries to insert a new object." Why would it do that? Is there some way to get this functioning like a normal insert sql statement?

From stackoverflow
  • The reason that it requires two queries is that with an identity the value of the column is not defined until the row is inserted. Therefore it requires a select after insert to get the column value for the inserted object. This is pretty standard and I wouldn't let it stop me from using autogenerated keys as my primary key. The other option is to pre-generate the key -- say a GUID for the new object before persisting it to the database. For the most part I don't really see an advantage to this unless there are other mitigating circumstances, such as having to merge data from separate databases where autogenerated keys might collide.

  • There's an obvious advantage: Letting NHibernate use Guids or Hilo as the id generator will enable an extremely cool feature in NHibernate: batching. Just configure NHibernate to use batching (for like, say 1000 statements), and your inserts will suddenly be extremely fast.

    Fabio has a post about the various availabe generators here - extremely useful reading if you are using NHibernate (or if you know someone who thinks NHibernate performs badly).

0 comments:

Post a Comment