NHibernate - When adding an object to a many-to-many collection, existing objects are removed and reinserted

I'm using a many-to-many mapping table which maps fields to objects.

Relevant DB structure...

Objects: Table

(pk) objectId

[ the rest is irrelevant ]

Fields: Table

(pk) fieldId

[ the rest is irrelevant ]

ObjectFields: Table

(pk) fieldObjectid (fk) fkObjectId -> Objects (fk) fkFieldId -> Fields

My mapping looks like this:

<bag name="Fields" table="ObjectFields" lazy="true" cascade="all">
  <key column="fkObjectId"/>
  <many-to-many class="Field" column="fkFieldId" />
</bag>

Now, all collection operations work as you would expect - retrieving, adding and deleting. However, there is a very odd thing happening. If I add an object to the "Fields" collection, NHibernate deletes what is already there and reinserts it.

Here is my log4net dump: DEBUG NHibernate.SQL [(null)] - SELECT this_.objectId as objectId6_0_, this_.Name as Name6_0_, this_.Description as Descript3_6_0_, this_.RootElement as RootElem4_6_0_, this_.ChildElement as ChildEle5_6_0_, this_.ImageUrl as ImageUrl6_0_, this_.hasChildren as hasChild7_6_0_, this_.CreateStamp as CreateSt8_6_0_ FROM Objects this_ WHERE this_.objectId = @p0;@p0 = 5 [Type: Int32 (0)]

DEBUG NHibernate.SQL [(null)] - SELECT fields0_.fkObjectId as fkObjectId1_, fields0_.fkFieldId as fkFieldId1_, inventoryf1_.fieldId as fieldId4_0_, inventoryf1_.fieldName as fieldName4_0_, inventoryf1_.fieldType as fieldType4_0_, inventoryf1_.Required as Required4_0_ FROM ObjectFields fields0_ left outer join Fields inventoryf1_ on fields0_.fkFieldId=inventoryf1_.fieldId WHERE fields0_.fkObjectId=@p0;@p0 = 5 [Type: Int32 (0)]

DEBUG NHibernate.SQL [(null)] - SELECT this_.fieldId as fieldId4_0_, this_.fieldName as fieldName4_0_, this_.fieldType as fieldType4_0_, this_.Required as Required4_0_ FROM Fields this_ WHERE this_.fieldId = @p0;@p0 = 2 [Type: Int32 (0)]

DEBUG NHibernate.SQL [(null)] - DELETE FROM ObjectFields WHERE fkObjectId = @p0;@p0 = 5 [Type: Int32 (0)]

DEBUG NHibernate.SQL [(null)] - INSERT INTO ObjectFields (fkObjectId, fkFieldId) VALUES (@p0, @p1);@p0 = 5 [Type: Int32 (0)], @p1 = 1 [Type: 开发者_运维知识库Int32 (0)]

DEBUG NHibernate.SQL [(null)] - INSERT INTO ObjectFields (fkObjectId, fkFieldId) VALUES (@p0, @p1);@p0 = 5 [Type: Int32 (0)], @p1 = 2 [Type: Int32 (0)]

As you can see, it's issuing delete statements and then reinserting.

Any ideas how to prevent this?

In short, bags behave like that, you should use another type of collection. Here you have a good explanation from NHibernate doc but I remomend you to read the whole chapter (17.5. Understanding Collection performance

All indexed collections (maps, lists, arrays) have a primary key consisting of the and index columns. In this case collection updates are usually extremely efficient - the primary key may be efficiently indexed and a particular row may be efficiently located when NHibernate tries to update or delete it.

Sets have a primary key consisting of key and element columns. This may be less efficient for some types of collection element, particularly composite elements or large text or binary fields; the database may not be able to index a complex primary key as efficently. On the other hand, for one to many or many to many associations, particularly in the case of synthetic identifiers, it is likely to be just as efficient. (Side-note: if you want SchemaExport to actually create the primary key of a set for you, you must declare all columns as not-null="true".)

idbag mappings define a surrogate key, so they are always very efficient to update. In fact, they are the best case.

Bags are the worst case. Since a bag permits duplicate element values and has no index column, no primary key may be defined. NHibernate has no way of distinguishing between duplicate rows. NHibernate resolves this problem by completely removing (in a single DELETE) and recreating the collection whenever it changes. This might be very inefficient.

I found an ID bag worked better for me in my situation, but the other answer from Claudio was good.