Hi

I have a very strange behavior of NHibernate. If I delete an object in NHibernate, reset the ID to 0 (so NHibernate thinks this is a new Object) and save it again, NHibernate sometimes uses the old primary key for foreign keys on the objects children. This is a log of NHibernate (ShowSQL = True).

--- DELETE TRANCACTION
<SNIP>
NHibernate: DELETE FROM K WHERE K_ID = @p0; @p0 = '304'
NHibernate: DELETE FROM S WHERE S_ID = @p0; @p0 = '291'

=> NO ERRORS


--- UNDO / SAVE TRANSACTION
NHibernate: INSERT INTO S (Ord, Bez, Dat, Bem, SS_ID, TS_ID) VALUES (@p0, @p1, @p2, @p3, @p4, @p5); select SCOPE_IDENTITY(); @p0 = '2', @p1 = 'A_OSB_DP', @p2 = '16.01.2007 00:00:00', @p3 = '', @p4 = '5', @p5 = '16'
NHibernate: INSERT INTO K (VarPos, SS_ID, KB_ID, S_ID) VALUES (@p0, @p1, @p2, @p3); select SCOPE_IDENTITY(); @p0 = '1', @p1 = '5', @p2 = '69', @p3 = '863'
NHibernate: INSERT INTO KBEA (E_ID, SS_ID, K_ID) VALUES (@p0, @p1, @p2); select SCOPE_IDENTITY(); @p0 = '703', @p1 = '5', @p2 = '304'
NHibernate.ADOException: could not insert: [KBEA][SQL: INSERT INTO KBEA (E_ID, SS_ID, K_ID) VALUES (?, ?, ?)] ---> System.Data.SqlClient.SqlException: Die INSERT-Anweisung steht in Konflikt mit der FOREIGN KEY-Einschränkung "R_138". Der Konflikt trat in der "VDB"-Datenbank, Tabelle "K", column 'K_ID' auf.

(I hope I changed all real tables names to symbolic names correctly. I use a German development environment but the SQLException only says that there is a foreign key violation because the K_ID 304 was not found in table K)

As you can see the S_ID was changed within the object model correctly: @p3 = '863'. But the foreign key of KBEA to K was not changed correctly and references still the old is "304". But if I debug the Object itself it references to the real new id e.g. 395.

I use the same code and mapping for K to S and KBEA to K. I also use the same mechanism to Uninit the objects which means a Session.Delete() and Object.PrimaryKey = 0.

So I have two questions:

* How do I find out, what the SELECT SCOPE_IDENTITY() return to NHibernate?
* Why does NHibernate use the old ID as foreign key, although the object model references to the new one?


Thanks for your help in advance,

Thomas

* See what NHibernate assignes to the ID property of your entity.
* Use a Network sniffer.
* Use a profiling tool for your database.


I don't know it really, but it may be because of a cache. NHibernate caches it's objects during a session and because the object reference didn't change NHibernate thinks that the IDs also didn't change.

Why don't you create new entities instead of setting the ID of an existing entity to 0?

Hi

I worked the whole day with this problem and I found out several things:

1. The SQL Server profiling tool only shows the sql statement and not the results. No chance.

2. The ID in the object model is set correctly to the new ID (see later). The sql statement is concatenated in a wrong way as said before.

3. I modified NHibernate (since today I am using 2.0.0) to log the returned ID, if the 'ShowSql' parameter is set to true. I changed in 'EntityIdentityInsertAction' the Execute() method:



The if part is new to this method.

4. NHibernate 2.0.0 changed the Settings.IsShowSqlEnabled setter to internal and I changed it again to public ;)



I wrote an undo manager which works like the "Unit Of Work Patter" of Martin Fowler to get all changes to the object model to realize an undo of operations. If an object is deleted I remove this object from the persistent objects with removing the child in the parents list of childs. So the parent does not know his child any longer but to realize an undo the child still knows his parent. This ensures that the deleted objects can be restored into the old parents list. This works fine but if I do not have a parents list (because every object in my domain can have only one parent) and the parent does not know that he has a one-to-many relation nhibernate uses the old ID. if I have a 'normal' inverse relation this work fine.
Due to the fact that we use auto generated primary keys in SQL Server 2005 it is the database part to generate valid keys and I am not able to change this behaviour. I already tried to re-attach the object with using their original id ;) but this did not work.

Does anyone know how to delete the cache of the object to force nhibernate to use the new ID of the parent?
[/quote]

I still don't get it ;-)

So you remove a K object and a S object from the database, set their IDs to 0 and remove them from a child collection? Where is that collection? Is it in KBEA?

Why is something inserted into KBEA if that's only a undo-operation but nothing was deleted from KBEA?

You are right. I did not gave you the delete statement of KBEA because it way long before the both given delete statement (just another problem that NHibernate tries to think and reorders my statements). This is the relation between S, K and KBEA (and GK & E to comple the chaos):

S -- 0..n --> K (no inverse relation, only K refers to S as many-to-one. Hint: K as a relation to GK, which is a inverse relation)

S -- 0..n --> E (no inverse relation, only E refers to S [one E refers to two S ;) ])

E -- 0..n --> KBEA (no inverse relation, KBEA has a reference to E)

GK -- 0..n --> K (just to complete this relations. This is not the point of my problem)

K -- 0..n --> KBEA (inverse relation, K has a child collection with many KBEA)

Now some pseudo code which describes the delete routines:

S.Delete() {
Delete all K, which refer to S;
Delete all E, which refer to S;
Delete This;
}

K.Delete() {
Delete all KBEA which are in K;
Remove This from GK.K_List;
Delete This;
}

KBEA.Delete() {
Remove This from K.KBEA_List;
Delete This;
}


If I undo this stuff, I retrieve the list (from my UnitManagers stack) of all operations which are done during the last transanction. Then I reverse all this operation, set the ID to 0 and add all items back into their parents list. And this seems to be the problem. Sometimes NHibernate recognizes that the primary key changed (see K which refers to S) and sometimes NHibernate does not (see K and KBEA).

So your database table KBEA has a relation to K (K_ID), but your object KBEA doesn't have a relation to the object K? Only K has a collection of KBEAs?

If that's correct, you shouldn't mark that collection as inverse.

I solved my problem yesterday afternoon. My Undo-Transaction-Manager had two problems:

1st: It deletes objects more than one time. This is not a problem if you only delete this but if you invert the operations (delete-> insert, insert -> delete) this will not work. NHibernate creates an object without creating all partents. I changed my transaction manager only to delete are create an object once

2nd: The main problem if this thread where the old foreign keys which are used. This was all because of lazy loading after I turned this off, it works perfectly (together with problem 1 solved). Lazy Loading caches the foreign keys of related objects.

As conclusion: Don't be lazy :)