Ordered many-to-many relationship in NHibernate

Question

Let's say I have two classes: Item and ItemCollection, where ItemCollection contains an ordered list of Item objects with an index, i.e. the list is ordered in a way specified by the user.

Let's also say that they have a many-to-many relationship, an ItemCollection can contain many items and an Item can belong to several ItemCollections.

That would, in my head, require three tables in the database. One for Item, one for ItemCollection and one for the ordered mapping. The mapping table would contain three columns:

int ItemID
int ItemCollectionID
int ListIndex

QUESTION: How would you design the ItemCollection class? Should the list of Item objects be a list, dictionary or other? What would the NHibernate mapping look like to get the ListIndex into the picture?

Answer 1

You don't need a class 'Item Collection', rather some other class will have a collection of items. For example, a social networking site, a 'Person' will have a collection of 'friends' - there is no "FriendCollection" class. Or another example, a teacher has a collection of students and a student has a collection of teachers:

If order is important, you need to use a List (bag in NH), not a Set (which has no order, but guarantees uniqueness), and not a map (which requires a [key:value] pair).

<class name="App.Core.Domain.Teacher, App.Core" table="teachers">
    <bag name="StudentsInClass" table="studentsteacherjointable" inverse="false" cascade="all">
      <key column="teacherid"/>
      <many-to-many class="App.Core.Domain.Student, App.Core" column="studentid" order-by="classday desc"/>
    </bag>
</class>


<class name="App.Core.Domain.Student, App.Core" table="students">
    <bag name="Teachers" table="studentsteacherjointable" inverse="false" cascade="all">
      <key column="studentid"/>
      <many-to-many class="App.Core.Domain.Podcasts, App.Core" column="teacherid" order-by="classday desc"/>
    </bag>
</class>

Note the optional order-by on the many-to-many element. This defines a column in the join table to order the results by, e.g They might be ordered by date, or some other value that is stored along side the join table.

Answer 2

If you're using Fluent NHibernate, you'll find an answer in the HasMany mapping in the code for OrderMapping in this article.

For traditional hibernate mapping (XML file), see chapter 21.3 of the NHibernate documentation here.