We have Bar and Foo classes, they have a simple many-to-many relationship:
public partial class Bar
{
public virtual Guid BarId { get; set; } = Guid.Empty;
public virtual IList<Foo> ManyBarsInManyFoos { get; protected set; } = new Collection<Foo>();
}
public partial class Foo
{
public virtual Guid FooId { get; set; } = Guid.Empty;
public virtual IList<Bar> ManyFoosInManyBars { get; protected set; } = new Collection<Bar>();
}
At the beginning, I used bag to map this many-to-many:
<class name="Bar" table="Bars" optimistic-lock="version">
<id name="BarId" unsaved-value="{00000000-0000-0000-0000-000000000000}">
<generator class="guid" />
</id>
<timestamp name="LastChange" />
<bag name="ManyBarsInManyFoos" table="ManyBarsInManyFoos" cascade="save-update" inverse="false">
<key column="ManyBarsInManyFoosBarId" />
<many-to-many column="ManyFoosInManyBarsFooId" class="Many2Many.DomainModel.Foo" />
</bag>
</class>
<class name="Foo" table="Foos" optimistic-lock="version">
<id name="FooId" unsaved-value="{00000000-0000-0000-0000-000000000000}">
<generator class="guid" />
</id>
<timestamp name="LastChange" />
<bag name="ManyFoosInManyBars" table="ManyBarsInManyFoos" cascade="save-update" inverse="false">
<key column="ManyFoosInManyBarsFooId" />
<many-to-many column="ManyBarsInManyFoosBarId" class="Many2Many.DomainModel.Bar" />
</bag>
</class>
That works fine, but we discovered that when removing a single association (a single row in ManyBarsInManyFoos table) NHibernate delete all rows related to that BarId and then re-insert the other rows:
'UPDATE dbo.Bars SET LastChange = @p0 WHERE BarId = @p1 AND LastChange = @p2',N'@p0 datetime,@p1 uniqueidentifier,@p2 datetime',@p0='2018-05-22 17:22:17.760',@p1='F111C29E-41C1-483F-AC28-053A53713A10',@p2='2018-05-22 17:21:44.410'
'UPDATE dbo.Foos SET LastChange = @p0 WHERE FooId = @p1 AND LastChange = @p2',N'@p0 datetime,@p1 uniqueidentifier,@p2 datetime',@p0='2018-05-22 17:22:17.760',@p1='5204C39B-265F-47F7-AA30-2D104A6FCEFB',@p2='2018-05-22 17:21:44.440'
'DELETE FROM dbo.ManyBarsInManyFoos WHERE ManyBarsInManyFoosBarId = @p0',N'@p0 uniqueidentifier',@p0='F111C29E-41C1-483F-AC28-053A53713A10'
'INSERT INTO dbo.ManyBarsInManyFoos (ManyBarsInManyFoosBarId, ManyFoosInManyBarsFooId) VALUES (@p0, @p1)',N'@p0 uniqueidentifier,@p1 uniqueidentifier',@p0='F111C29E-41C1-483F-AC28-053A53713A10',@p1='D1A635A0-360D-4AC1-98EE-EE3CE47BE63C'
'INSERT INTO dbo.ManyBarsInManyFoos (ManyBarsInManyFoosBarId, ManyFoosInManyBarsFooId) VALUES (@p0, @p1)',N'@p0 uniqueidentifier,@p1 uniqueidentifier',@p0='F111C29E-41C1-483F-AC28-053A53713A10',@p1='58EA2B80-C8D9-4708-90D7-FA110816715B'
'DELETE FROM dbo.ManyBarsInManyFoos WHERE ManyFoosInManyBarsFooId = @p0',N'@p0 uniqueidentifier',@p0='5204C39B-265F-47F7-AA30-2D104A6FCEFB'
'INSERT INTO dbo.ManyBarsInManyFoos (ManyFoosInManyBarsFooId, ManyBarsInManyFoosBarId) VALUES (@p0, @p1)',N'@p0 uniqueidentifier,@p1 uniqueidentifier',@p0='5204C39B-265F-47F7-AA30-2D104A6FCEFB',@p1='B67DE063-BFD9-4AE3-BADA-2B3B5669295D'
'INSERT INTO dbo.ManyBarsInManyFoos (ManyFoosInManyBarsFooId, ManyBarsInManyFoosBarId) VALUES (@p0, @p1)',N'@p0 uniqueidentifier,@p1 uniqueidentifier',@p0='5204C39B-265F-47F7-AA30-2D104A6FCEFB',@p1='0DB243BC-3C89-43E0-AD55-C0CD8120E711'
It seems that this behaviour is needed to handle multiple relationships between same objects in the bag, that is not indexed. But we need (due to a trigger or something similar) that existing many-to-many rows in the ManyBarsInManyFoos table are not deleted (except for that really related to the removed relation).
First question: Is there a way to avoid this "massive" delete and tell NHibernate to delete only the involved row?
Thinking that probably this is not possibile, I tried to use list instead of bag:
<list name="ManyBarsInManyFoos" table="ManyBarsInManyFoos" cascade="save-update">
<key column="ManyBarsInManyFoosBarId" />
<index column="Position"/>
<many-to-many column="ManyFoosInManyBarsFooId" class="Many2Many.DomainModel.Foo" />
</list>
-------
<list name="ManyFoosInManyBars" table="ManyBarsInManyFoos" cascade="save-update" inverse="true">
<key column="ManyFoosInManyBarsFooId" />
<index column="Position"/>
<many-to-many column="ManyBarsInManyFoosBarId" class="Many2Many.DomainModel.Bar" />
</list>
but things become a little bit more strange (for me) on removing an existing association:
'UPDATE dbo.Bars SET LastChange = @p0 WHERE BarId = @p1 AND LastChange = @p2',N'@p0 datetime,@p1 uniqueidentifier,@p2 datetime',@p0='2018-05-22 17:56:46.480',@p1='301E92EA-5E37-4948-A0FE-00884D5EB221',@p2='2018-05-22 17:56:32.180'
'DELETE FROM dbo.ManyBarsInManyFoos WHERE ManyBarsInManyFoosBarId = @p0 AND Position = @p1',N'@p0 uniqueidentifier,@p1 int',@p0='301E92EA-5E37-4948-A0FE-00884D5EB221',@p1=2
'UPDATE dbo.ManyBarsInManyFoos SET ManyFoosInManyBarsFooId = @p0 WHERE ManyBarsInManyFoosBarId = @p1 AND Position = @p2',N'@p0 uniqueidentifier,@p1 uniqueidentifier,@p2 int',@p0='CE0C01A3-4F0E-4E93-A33C-22AAE68482D9',@p1='301E92EA-5E37-4948-A0FE-00884D5EB221',@p2=0
'UPDATE dbo.ManyBarsInManyFoos SET ManyFoosInManyBarsFooId = @p0 WHERE ManyBarsInManyFoosBarId = @p1 AND Position = @p2',N'@p0 uniqueidentifier,@p1 uniqueidentifier,@p2 int',@p0='193C5714-FD1C-4C51-AD5F-6B19F262046F',@p1='301E92EA-5E37-4948-A0FE-00884D5EB221',@p2=1
Other rows are not deleted but may be "reused" and "switched" behind the scenes from an object to another. Not a valid solution.
Finally, I tried to use map... Tried, but I was not able to understand how I can create the right mapping tags for this simple relationship.
Therefore, second question: How can I map this many-to-many with map tag? And/or how can we map this many-to-many relationship in an optimized way, having NHibernate to simply delete the single involved row in ManyBarsInManyFoos table, instead of deleting many rows to reinsert those after a while or "reassign" relation rows to different objects?
