Today I received a strange “null identifier†error when saving an object to db with nhibernate. Immediatly I realize that this error is caused by a Trigger INSTEAD OF INSERT, that apperars to broke the SCOPE_IDENTITY() function. Since NHibernate uses SCOPE_IDENTITY() to retrieve the id of the generated object, when you use INSTEAD OF INSERT Trigger the SCOPE_IDENTITY() returns null, and nhibernate could not know the value of the inserted record.
I first tried to specify with <sql.insert> the query to use, to make use of @@identity, but it does not work. After some time I discovered a issue that was fixed only in the trunk. My only solution was removing the trigger (fortunately I can do it) because nhibernate does not permits me to use <sql-insert> with entities that use identity to generate ids.
Another good reason not to use identity with nhibernate, but my project is a legacy one where a lot of operations are massive ones performed by stored procedure.
alk.
April 14th, 2009 at 4:40 pm
“Another good reason not to use identity with nhibernate.” Can you expand on this? Everywhere I read I see people saying “Assigned Id’s are the spawn of the devil” with regards to NHibernate.
April 15th, 2009 at 4:53 am
Well, the problem is the following, if Id is generated by the db with an identity column when you call Session.Save nhibernate needs to insert the data into the table to retrieve the id. If you set autoflush to none the data gets inserted in save. This does not means that you need to use “assigned” as id, you can simply use a guid and the standard guid generator, or you can use other generators supported in nhibernate.
Assigned means that “you” assign id to the entity thus nhibernate cannot use saveorupdate and other problems will arise.
alk.