Fluent NHibernate M2M Mapping ([One] to [Many - Many] to [One])

Question

i have searched the "whole" internetz for this question, and its a damn hard one to search for as its rather complicated. Try searching for "Fluent NHibernate Many to Many with a bridge table with extra columns" etc...

Okay, to make it easier to explain ill define some tables i can refer to. Table: User, Table: Function, Table: User_Has_Function.

One User can have many Functions, and a Function can have many Users, this is linked in the bridge table User_Has_Function. The bridge table has extra columns which is only relevant to the relationship.

Well anyways iv found that FNH doesn't have any automatic solution to this, basically you have to use a one to many relation from User to User_Has_Function and many to one from User_Has_Function to Function, hence "[One] to [Many - Many] to [One]".

I have solved it like in this link http://sessionfactory.blogspot.com/2010/12/many-to-many-relationships-with.html just with FNH class mapping instead of xml obviously.

But im not satisfied with the solution, do i really have to do all this manually work to make this function properly? Also as it is now it inserts duplicates in the bridge table.

In my head i'm doing something wrong, cause i cant imagine there is no support for this. Just use SaveAndUpdate(), no duplicates are inserted and when i remove an entity the relation is removed as well, if no relations are left remove the entity itself etc.

Okay here are my entities and mappings, I am VERY new to Fluent NHibernate so don't yell to much if i have done something very wrong. :)

Entities:

public class XUser
{
    public virtual int Id { get; set; }
    ...
    public virtual IList<XUserHasXFunction> XUserHasXFunctions { get; set; }

    public XUser()
    {
        XUserHasXFunctions = new List<XUserHasXFunction>();
    }

    public virtual void AddXFunction(XFunction xFunction, int isActive)
    {
        var xUserHasXFunction = new XUserHasXFunction()
                                    {
                                        XUser = this,
                                        XFunction = xFunction,
                                        DeployedDate = DateTime.Now
                                    };
        XUserHasXFunctions.Add(xUserHasXFunction);
        xFunction.XUserHasXFunctions.Add(xUserHasXFunction);
    }

    public virtual void RemoveXFunction(XFunction xFunction)
    {
        var xUserHasXFunction = XUserHasXFunctions.Single(x => x.XFunction == xFunction);
        XUserHasXFunctions.Remove(xUserHasXFunction);
        xFunction.XUserHasXFunctions.Remove(xUserHasXFunction);
    }
}

public class XFunction
{
    public virtual int Id { get; set; }
    ...
    public virtual IList<XUserHasXFunction> XUserHasXFunctions { get; set; }

    public XFunction()
    {
        XUserHasXFunctions = new List<XUserHasXFunction>();
    }

    public virtual void AddXUser(XUser xUser, int isActive)
    {
        var xUserHasXFunction = new XUserHasXFunction()
                                    {
                                        XUser = xUser,
                                        XFunction = this,
                                        DeployedDate = DateTime.Now
                                    };
        XUserHasXFunctions.Add(xUserHasXFunction);
        xUser.XUserHasXFunctions.Add(xUserHasXFunction);
    }

    public virtual void RemoveXUser(XUser xUser)
    {
        var xUserHasXFunction = XUserHasXFunctions.Single(x => x.XUser == xUser);
        XUserHasXFunctions.Remove(xUserHasXFunction);
        xUser.XUserHasXFunctions.Remove(xUserHasXFunction);
    }
}

public class XUserHasXFunction
{
    public virtual int Id { get; set; }
    public virtual XUser XUser { get; set; }
    public virtual XFunction XFunction { get; set; }
    public virtual DateTime DeployedDate { get; set; }
}

Mappings:

public class XUserMap : ClassMap<XUser>
{
    public XUserMap()
    {
        Id(x => x.Id, "ID").GeneratedBy.Sequence("SEQ").Column("ID");
        Table("XUSER");
        ...
        HasMany(x => x.XUserHasXFunctions).Cascade.All();
    }
}

public class XFunctionMap : ClassMap<XFunction>
{
    public XFunctionMap()
    {
        Id(x => x.Id, "ID").GeneratedBy.Sequence("SEQ").Column("ID");
        Table("XFUNCTION");
        ...
        HasMany(x => x.XUserHasXFunctions).Cascade.All();
    }
}

public class XUserHasXFunctionMap : ClassMap<XUserHasXFunction>
{
    public XUserHasXFunctionMap()
    {
        Id(x => x.Id, "ID").GeneratedBy.Sequence("SEQ").Column("ID");
        Table("USER_HAS_FUNCTION");
        Map(x => x.DeployedDate, "DEPLOYED_DATE");

        References(x => x.XUser).ForeignKey("XUSER_ID").Cascade.SaveUpdate();
        References(x => x.XFunction).ForeignKey("XFUNCTION_ID").Cascade.SaveUpdate();
    }
}

Answer 1

I don't understand the "do i really have to do all this manual work" part. What "all this manual work"? There is nothing special there. The mapping is simple and the c# code doesn't have to do anything with persistency, it's plain old OO design.

If you get duplicated rows, there is something wrong with your mapping. It might be because of a inverse collection which had not been mapped as inverse.

If you don't need to navigate from Function to User, it's very easy. Either map the relation as entity, as described in the blog, or even easier, map it as a composite element.

(Sorry, I don't know Fluent)

<bag name="Functions" table="User_Has_Function">
  <key column="UserId" />
  <composite-element>
    <many-to-one class="Function"/>
  </composite-element>
</bag>

---

Edit:

From the comments:

The manual work I am talking about is the manual getting and checking to remove and add relations from a user or function.

Are you talking about the required Add and Remove methods, which maintain the consistency of the relations? This is plain OO design. If you hadn't NHibernate, you would have to write it exactly the same (given the same class model).

delete a user from a function make it cascade all the way to user and so forth...

No. Delete-cascading happens when an object is deleted. When you delete a user, you should cascade the user_has_function. From there, you may or may not cascade the functions. The same in the other direction. There is also the concept of "cascade-all-delete-orphans". It means that additionally to regular cascading, an object is deleted automatically when it is removed from the collection. This is not cascading. It is a kind of very basic garbage collection. If you want to make use of this in your case, you should not apply it to both the user->user_has_function collection and the function->user_has_function collection, because it would try to delete the object twice.

Don't forget to map both collections inverse. If you don't, you may get duplicated entries.

Make sure that the three mappings (the user->user_has_function collection, the function->user_has_function and the user_has_function class mapping) are using the same table name and foreign key names.

You don't need to mess around with composite keys.

Answer 2

I ended up doing something similar a while ago with user, group, user_group and ended up having to use a hacky method of having both objects exist on both sides and also manually choose between save or update.

I don't think there is a NICE way to do what you want, and I agree it is something that from a database point of view is fairly logical to do, but from a modelling point of view is a pain.

As I also assume you are having to use a composite key for your user_has_function table to make sure that you can have multiple functions for multiple users. Which I think most people try to avoid and end up using surrogate keys or some other approach.

I know this isn't an answer, but I never found a real answer to the same question when I posted it.

Here is a similar question I posted a while back:

Nhibernate composite key question

Answer 3

I ended up using an ISet instead of having the relations in ILists. ISet does not allow duplicates, but IList does. To use ISet you have to override the Equals and GetHashCode methods for the object stored in the ISet.

I cascade from XUser and XFunction and not the other way around, ended up that every record in all 3 tables were deleted when i deleted one entity because of cascading.

Here is how i solved it.

Entities:

public class XUser
{
    public virtual int Id { get; set; }
    ...
    public virtual ISet<XUserHasXFunction> XUserHasXFunctions { get; set; }

    public XUser()
    {
        XUserHasXFunctions = new HashedSet<XUserHasXFunction>();
    }

    public virtual void AddXFunction(XFunction xFunction, int isActive)
    {
        var xUserHasXFunction = new XUserHasXFunction()
                                    {
                                        XUser = this,
                                        XFunction = xFunction,
                                        IsActive = isActive,
                                        DeployedDate = DateTime.Now
                                    };
        if (XUserHasXFunctions.Contains(xUserHasXFunction) && xFunction.XUserHasXFunctions.Contains(xUserHasXFunction))
        {
            return;
        }
        XUserHasXFunctions.Add(xUserHasXFunction);
        xFunction.XUserHasXFunctions.Add(xUserHasXFunction);
    }

    public virtual void RemoveXFunction(XFunction xFunction)
    {
        var xUserHasXFunction = XUserHasXFunctions.Single(x => x.XFunction == xFunction);
        XUserHasXFunctions.Remove(xUserHasXFunction);
        xFunction.XUserHasXFunctions.Remove(xUserHasXFunction);
    }
}

public class XFunction
{
    public virtual int Id { get; set; }
    ...
    public virtual ISet<XUserHasXFunction> XUserHasXFunctions { get; set; }

    public XFunction()
    {
        XUserHasXFunctions = new HashedSet<XUserHasXFunction>();
    }

    public virtual void AddXUser(XUser xUser, int isActive)
    {
        var xUserHasXFunction = new XUserHasXFunction()
                                    {
                                        XUser = xUser,
                                        XFunction = this,
                                        IsActive = isActive,
                                        DeployedDate = DateTime.Now
                                    };
        if (XUserHasXFunctions.Contains(xUserHasXFunction) && xUser.XUserHasXFunctions.Contains(xUserHasXFunction))
        {
            return;
        }
        XUserHasXFunctions.Add(xUserHasXFunction);
        xUser.XUserHasXFunctions.Add(xUserHasXFunction);
    }

    public virtual void RemoveXUser(XUser xUser)
    {
        var xUserHasXFunction = XUserHasXFunctions.Single(x => x.XUser == xUser);
        XUserHasXFunctions.Remove(xUserHasXFunction);
        xUser.XUserHasXFunctions.Remove(xUserHasXFunction);
    }
}

public class XUserHasXFunction
{
    public virtual int Id { get; set; }
    ...
    public virtual DateTime DeployedDate { get; set; }

    public override bool Equals(object obj)
    {
        if (obj == null)
            return false;
        var t = obj as XUserHasXFunction;
        if (t == null)
            return false;
        return XUser == t.XUser && XFunction == t.XFunction;
    }

    public override int GetHashCode()
    {
        return (XUser.Id + "|" + XFunction.Id).GetHashCode();
    }
}

Mappings:

public class XUserMap : ClassMap<XUser>
{
    public XUserMap()
    {
        Id(x => x.Id, "ID").GeneratedBy.Sequence("SEQ").Column("ID");
        Table("XUSER");
        ...
        HasMany(x => x.XUserHasXFunctions).KeyColumn("XUSER_ID").Cascade.All();
    }
}

public class XFunctionMap : ClassMap<XFunction>
{
    public XFunctionMap()
    {
        Id(x => x.Id, "ID").GeneratedBy.Sequence("SEQ").Column("ID");
        Table("XFUNCTION");
        ...
        HasMany(x => x.XUserHasXFunctions)KeyColumn("XFUNCTION_ID").Cascade.All();
    }
}

public class XUserHasXFunctionMap : ClassMap<XUserHasXFunction>
{
    public XUserHasXFunctionMap()
    {
        Id(x => x.Id, "ID").GeneratedBy.Sequence("SEQ").Column("ID");
        Table("XUSER_HAS_XFUNCTION");
        ...
        Map(x => x.DeployedDate, "DEPLOYED_DATE");

        References(x => x.XUser).Column("XUSER_ID");
        References(x => x.XFunction).Column("XFUNCTION_ID");
    }
}

Usage:

To add relations.
xFunction.AddXUser(xUser, isActive); //visa versa if you like to add a function to a user...
dao.Store(xFunction); //to actually add the relation in the db

now to remove relation
xFunction.RemoveXUser(xUser); //Realtion is removed but neither of the objects xFunction or xUser
dao.Store(xFunction); //...same

to remove a user and its relations.
dao.delete(xUser); //but the xFunction object it was connected to is not removed
//if you want the xFunction object to be removed you have to do that manually.