NHibernate Bidirectional Many-to-Many Mapping List / Bag
hibernate, nhibernate, orm
Solution
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 ...
Problem
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 - John - Sally - Seth Program B - Alex - Seth - Amy - 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!