NHibernate Bidirectional Many-to-Many Mapping List / Bag

Question

I am struggling with a bi-directional many-to-many mapping where the order is important on one side, but not the other.

I have two classes: Program and Student.

A program has many students and the sequential order is important.

Program A

1. John
2. Sally
3. Seth

Program B

1. Alex
2. Seth
3. Amy
4. John

A student has many programs, but the order is not important here.

John * Program A * Program B

Sally

• Program A

Seth

• Program A
• Program B

Alex

• Program B

Amy

• Program B

So, it appears that I would have a bidirectional many-to-many association between programs and students where I could do things like this...

var john = GetJohn();
var programCount = john.Programs.Count; // 2

var programB = GetProgramB();
var studentCount = programB.Students.Count; // 4
var secondStudent = programB.Students[1]; // Seth

I cannot figure out how to get the mapping to work. I keep getting PK violation errors.

I have tried the following...

PROGRAM MAPPING

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
    <class name="Program" table="Programs" lazy="false">
        <id name="ID" column="ID" type="Int32" unsaved-value="0">
            <generator class="identity" />
        </id>
        <property name="Title" column="Title" type="String" length="200" />
        <list name="Students" table="ProgramAssignments" lazy="true" cascade="all">
            <key column="ProgramID" />
            <index column="SequenceIndex" type="Int32" />
            <many-to-many column="StudentID" class="Student" />
        </list>
    </class>
</hibernate-mapping>

STUDENT MAPPING

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
    <class name="Student" table="Students" lazy="false">
        <id name="ID" column="ID" type="Int32" unsaved-value="0">
            <generator class="identity" />
        </id>
        <property name="Title" column="Title" type="String" length="200" />
        <bag name="Programs" table="ProgramAssignments" lazy="true">
            <key column="StudentID" />
            <many-to-many column="ProgramID" class="Program" />
        </bag>
    </class>
</hibernate-mapping>

My association table is very simple...

ProgramID int
StudentID int
SequenceIndex int

The PK is the ProgramID + StudentID.

I really want the association to be managed from the program side because the order of the students in each program is important. But, I really would like to be able to access the programs for a specific student via mystudent.Programs. I have tried many variations of the mapping, including setting inverse=true on the program list, trying different cascade options, etc. Nothing seems to work.

Help! Thanks!

Answer 1

The issue could be hidden in the fact, that the <list> mapping supports multiple assignment of one instance (so there could be student Seth assigned twice, with different SequenceIndex - violating the PK error)

But I would like to give you a hint. Try to change the approach and introduce the intermediate object. See the NHibernate documentation: Chapter 24. Best Practices. Quick summary:

Don't use exotic association mappings.

Good usecases for a real many-to-many associations are rare. Most of the time you need additional information stored in the "link table". In this case, it is much better to use two one-to-many associations to an intermediate link class. In fact, we think that most associations are one-to-many and many-to-one, you should be careful when using any other association style and ask yourself if it is really neccessary.

And I would say, that this is (could be) the case, because you really need the Order ...