Fluent-NHibernate: How to a create a many-to-many relationship with a unique contraint

Question

I want to create a many to many relationship, but I want to have in the new table(MessageReceivers) a unique contraint on both columns (AdvanceMessageId,UserId):

mapping.HasManyToMany(x => x.Receivers)
       .WithParentKeyColumn("AdvanceMessageId")
       .WithChildKeyColumn("UserId")
       .Cascade.All()
       .LazyLoad()
       .WithTableName("MessageReceivers");

Thanks for help

Answer 1

Old post... but in case someone else arrives here looking for the answer:

You need to add .AsSet() to the HasManyToMany mapping definintion.

---

i.e.

mapping.HasManyToMany(x => x.Users)
       .WithTableName("MessageReceivers")
       .WithParentKeyColumn("UserId")
       .WithChildKeyColumn("AdvanceMessageId")
       .Inverse().AsSet();

This will setup an unique, composite primary key constraint on the link table that uses both columns. (clustered index)

The down side is AsSet() cannont be used with collection properties of type IList, so no for loops without casting.

I have been using ICollection and instantiating them as HashSet for my applications and it works well.

---

More info on collection management with Fluent Nhibernate:

List: Ordered collection of entities, duplicate allowed. Use a .net IList in code. The index column will need to be mapped in NHibernate.

Set: Unordered collection of unique entities, duplicates not allowed. Use Iesi.Collection.ISet in code. It is important to override GetHashCode and Equals to indicate the business definition of duplicate. Can be sorted by defining a orderby or by defining a comparer resulting in a SortedSet result.

Bag: Unordered list of entities, duplicates allowed. Use a .net IList in code. The index column of the list is not mapped and not honored by NHibernate.

Answer 2

You should also map the inverse side of the relationship like

mapping.HasManyToMany(x => x.Users)
       .WithTableName("MessageReceivers")
       .WithParentKeyColumn("UserId")
       .WithChildKeyColumn("AdvanceMessageId")
       .Inverse();

In newest Fluent NHibernate you will have to change

• WithTableName -> Table

• WithParentKeyColumn -> ParentKeyColumn

• WithChildKeyColumn -> ChildKeyColumn