Deleting a many-to-many relationship results in a delete and an insert queries

Question

Hello all,

I have an entity called Strategy, and another one called Team. Both are mapped using a Many-To-Many relationship:

public class Team : BaseEntity<Team>
{
    private readonly IList<Strategy> allStrategies = new List<Strategy>();

    public void AddStrategy(Strategy strategy)
    {
        allStrategies.Add(strategy);
        strategy.AddTeam(this);
    }

    public void RemoveStrategy(Strategy strategy)
    {
        allStrategies.Remove(strategy);
        strategy.RemoveTeam(this);
    }
}

public class TeamMap : ClassMap<Team>
{
    public TeamMap()
    {
        HasManyToMany<Strategy>(Reveal.Member<Team>("allStrategies"))
            .Cascade.SaveUpdate()
            .Access.CamelCaseField()
            .Table("TeamStrategies");
    }
}

public class StrategyMap : ClassMap<Strategy>
{
    public StrategyMap()
    {
        HasManyToMany<Team>(Reveal.Member<Strategy>("allTeams"))
            .Inverse()
            .Cascade.SaveUpdate()
            .Access.CamelCaseField()
            .Table("TeamStrategies");
    }
}

Here is the unit-test which produces a strange behaviour from NH:

[Fact]
public void Test()
{
    using (var session = Kernel.Get<ISession>())
    {
        var team = new Team("JAO", Sex.Male);
        var repository = new TeamsRepository(session);
        repository.Save(team);

        var sA1 = new Strategy("Horn", StrategyType.Attack);
        var sA2 = new Strategy("Shirt", StrategyType.Attack);

        session.Save(sA1);
        session.Save(sA2);

        session.Flush();

        team.AddStrategy(sA1);
        team.AddStrategy(sA2);

        repository.SaveOrUpdate(team);

        session.Flush();

        team.RemoveStrategy(sA1);

        repository.SaveOrUpdate(team);

        session.Flush();

        Assert.Equal(2, session.Query<Strategy>().Count());
        Assert.Equal(1, session.Query<Team>().Count());
    }
}

Here are the queries NH produces on the last session.Flush():

NHibernate: DELETE FROM TeamStrategies WHERE Team_id = @p0;@p0 = 1 [Type: Int32 (0)]

NHibernate: INSERT INTO TeamStrategies (Team_id, Strategy_id) VALUES (@p0, @p1);@p0 = 1 [Type: Int32 (0)], @p1 = 2 [Type: Int32 (0)]

Why does NH delete all the rows of TeamStrategies, before inserting those which didn't have been deleted?

How can I make it do a single DELETE with the WHERE clause containing a filter on both the TeamId and the StrategyId?

I've read, while googling about my problem, that this may be related to the Equals and GetHashCode functions.

If it can help, I got them from here.

I've tried to change the value of the cascading, on both sides, but I still couldn't have something working :'(

Can anyone help? Or maybe is this the normal behavior? But this would be odd...

Thanks in advance

Mike

Answer 1

I believe you will need to map the collections as a <set> instead of a <bag> to get the functionality you're after.