Preventing delete/insert when modifying many-to-many relationship with NHibernate

Question

In my domain model a User is associated with many Places - the relationship is modelled via a UserPlace class and a mapping (with FluentNHibernate) as follows (the User has a collection of UserPlace called Neighbourhood:

public class UserMap : ClassMap<User>
{
    HasMany(x => x.Neighbourhood)
      .Component(c =>
        {
          c.Map(x => x.IsDefault).Not.Nullable();
          c.Map(x => x.Selected).Not.Nullable().Default("0");
          c.References(x => x.Place).Fetch.Join();
        }
      ).Not.LazyLoad().Cascade.SaveUpdate();
}

Whenever I modify any UserPlace entity belonging to a user and then persist the user to the db, all UserPlace rows for that user are deleted and then re-inserted.

I assume this is because NHibernate doesn't know how to uniquely identify one of these rows from another. In other words, the component in my mapping doesn't have a key as such.

A primary key can be formed by combining the User_id and Place_id columns in the table that stores the relationship between these two entities. How can I set up this key using Fluent? And will this solve the delete-and-re-insert behaviour that I'm seeing?

EDIT: I asked on NHUsers about this and Fabio Maulo suggested using an IdBag. This isn't supported in Fluent NHibernate as far as I can tell - and components don't allow identifiers. How else can I map this many-to-many relationship and prevent the delete-all-reinsert-all issue?

EDIT 2: Here are the tables that NH generate based on my mappings

CREATE TABLE [dbo].[User](
[Id] [uniqueidentifier] NOT NULL,
--- a bunch of unimportant fields
CONSTRAINT [PK__User] PRIMARY KEY CLUSTERED ([Id] ASC))

CREATE TABLE [dbo].[Neighbourhood](
[User_id] [uniqueidentifier] NOT NULL,
[IsDefault] [bit] NOT NULL,
[Place_id] [int] NOT NULL,
[Selected] [bit] NOT NULL)

CREATE TABLE [dbo].[Place](
[Id] [int] IDENTITY(1000,1) NOT NULL,
--- a bunch of unimportant fields
CONSTRAINT [PK_Place] PRIMARY KEY CLUSTERED ([Id] ASC))

There is a FK relationship between User.Id and Neighbourhood.User_Id and between Neighbourhood.Place_id and Place.Id

It may help to show the sql used to create the tables in question. I'm having a hard time visualizing the tables. It sounds like you need a composite id in the mapping of a UserPlace. It may be easier to provide you with an alternate mapping if I could see the tables. — Cole W, Apr 24 '11 at 23:16
Thanks for your comment. I have updated the question with table definitions (I cut out non-important columns for brevity) — Øyvind, Apr 25 '11 at 13:33

Cole W · Accepted Answer · 2011-04-25T14:13:52.937

You may be able to map this relationship like this to avoid the scenario you are running into:

public class UserMap : ClassMap<User>
{
    HasMany(x => x.Neighbourhood)
      .KeyColumn("User_id")
      .Not.LazyLoad().Cascade.SaveUpdate();
}

public class NeighborHoodMap : ClassMap<NeighborHood>
{
    Table("Neighbourhood");
        CompositeId()
            .KeyReference(x => x.User, "User_id")
            .KeyReference(x => x.Place, "Place_id");

    Map(x => x.IsDefault).Not.Nullable();
    Map(x => x.Selected).Not.Nullable().Default("0");
}

Neighborhood class would look something like this then:

public class NeighborHood
{
    public virtual User User { get; set; }
    public virtual Place Place { get; set; }
    public virtual bool IsDefault { get; set; }
    public virtual bool Selected { get; set; }

    public override bool Equals(object obj)
    {
       //check here to make sure these objects are equal (user_id and place_id are the same)    
    }

    public override int GetHashCode()
    {
        return User.Id.GetHashCode() ^ Place.Id.GetHashCode();
    }
}

Kerr-ching! Works like a charm. I let ReSharper implement Equals() for me (will go over that to check it's good, later) and it works just like it should. Thanks very, very much! — Øyvind, Apr 25 '11 at 14:30

score 0 · Answer 2 · answered Apr 24 '11 at 06:32

0

I'm not familiar with Fluent, but as a workaround, you could define a primary key on the UserPlace table, so NH can track the changes.

Just an idea.

answered Apr 24 '11 at 06:32

TheITGuy

722
4
15

Preventing delete/insert when modifying many-to-many relationship with NHibernate

2 Answers2