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

Question

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?

Community · Accepted Answer · 2020-06-20 09:12:55Z

4

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 1, 2010 at 13:00

Claudio Redi

68.6k15 gold badges138 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sam Over a year ago

Thanks Claudio - makes sense. The question then is, that my mapping table has a primary key; how do I associate that? It's completely irrelevant to my classes and realistically the "business logic" doesn't need to know about it. Can Hibernate take care of it automatically?

Sam Over a year ago

Additionally, after changing to a set, I get a TypeInitializationException: {"Foreign key (FK1427E785C0DFD3B9:FieldInstance [fieldObjectId])) must have same number of columns as the referenced primary key (FieldInstance [fkObjectInstanceId, fieldObjectId])"}

Sam · Accepted Answer · 2010-12-04 16:41:12Z

0

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

answered Dec 4, 2010 at 16:41

Sam

4,3597 gold badges54 silver badges88 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related