Hi
I have several (five) many-to-many sets in one class. I found convenient to use single "intermediate" table for this instead of five separate tables:
Code:
<set name="set1" lazy="true" table="link_table" >
<key column="IdParent" />
<many-to-many class="Child1" column="IdChild1" />
</set>
<set name="set2" lazy="true" table="link_table" >
<key column="IdParent" />
<many-to-many class="Child2" column="IdChild2" />
</set>
(...)
So instead of creating link_table1, link_table2, ..... with two columns each, I created only one link_table with columns: IdParent (not nullable), IdChild1, .... , IdChild5 (all idchild nullable)
Such a solution was performing very vell until .... I discovered that removing all the elements from one Set clears the other sets too. In generated sql I see why - until collection not becomes empty, NHibernate uses DELETE with two paramaters in WHERE to remove an item (IdParent and IdChild1 for example - "DELETE FROM link_table WHERE IdParent=x AND IdChild1=y"). When collection becomes empty the issued DELETE is "DELETE FROM link_table WHERE IdParent=x" and deletes all the links.
My question is: is there any configuration parameter or known workaround to use my solution safely (in other words to force DELETE to use two parameters always)? I would like to avoid creating separate intermadiate table for every many-to-many relation.