How can an indexed many-to-many set be mapped in NHibernate?

Question

Consider an entity, Entry, that contains a collection of another entity (many-to-many), Category, such that the Entry can be associated with a given Category no more than once (implying a "set" and constrained by the database) and the ordering of the Category entities within the collection is fixed and is defined by an extra field on the mapping table (implying a "list"). What would be the proper mapping for such a case?

Additional details/specifics about the problem this question is trying to address:

Since a <set> doesn't use an <index>, and we can easily maintain uniqueness of the Category entities in the application layer (as opposed to using NHibernate for this), using <list> seems to make sense since it will automatically handle the update of the ordering value in the extra field on the mapping table (which is the biggest "feature" I'd like to use). Unfortunately, when updating the collection, we encounter constraint violations due to the manner in which NHibernate performs the updates (even when the new collection values wouldn't ultimately cause a constraint violation) since we have a unique constraint on the database between the related objects in the mapping table.

More specifically, consider the following database schema:

CREATE TABLE Category (
  id int IDENTITY PRIMARY KEY,
  name varchar(50)
)
CREATE TABLE Entry (
  id int IDENTITY PRIMARY KEY,
  data varchar(50)
)
CREATE TABLE EntryCategory (
  categoryId int REFERENCES Category (id),
  entryId int REFERENCES Entry (id),
  index int,
  PRIMARY KEY (categoryid, entryId)
)

And the following mappings:

<class name="Category">
  <id name="Id">
    <generator class="native">
  </id>
  <property name="name"/>
</class>
<class name="Entry">
  <id name="Id">
    <generator class="native">
  </id>
  <property name="data"/>
  <list name="Categories" table="EntryCategory">
    <key column="entryID"/>
    <index column="index"/>
    <many-to-many class="Category" column="categoryID"/>
  </list>
</class>

Let's consider the initial database state containing the following data:

    EntryId  CategoryID  Index
    555      12          0
    555      13          1
    555      11          2
    555      2           3

On load of Entry #555, its Categories list will contain the following elements:

    Index  Category ID
    0      12
    1      13
    2      11
    3      2

If we update this list to remove an element (category 13 at index 1) to look like the following:

    Index  CategoryID
    0      12
    1      11
    2      2

NHibernate will first delete the entry with highest index value (since the size has reduced), executing the following:

DELETE FROM EntryCategory WHERE entryId = @p0 AND index = @p1; 
@p0 = '555', @p1 = '3'

After this update the data in the database looks like this:

    EntryId  CategoryID  Index
    555      12          0
    555      13          1
    555      11          2

Next, it attempts to update the values with the right mappings and starts with this updaet statement:

UPDATE EntryCategory SET CategoryID = @p0 WHERE EntryId = @p1 AND Index = @p2; 
@p0 = '11', @p1 = '555', @p2 = '1'

This fails with Violation of PRIMARY KEY constraint since it's attempting to make the data look like this:

    EntryId  CategoryID  Index
    555      12          0
    555      11          1
    555      11          2

It seems that what really needs to happen is that the EntryId and CategoryId are left alone and the Index values are updated instead of leaving the EntryId and Index values alone and updating the CategoryId values. But even in that case, there could be issue if, say, there were also a UNIQUE constraint on EntryID and Index (in addition to the primary key on EntryID and CategoryID).

Answer 1

Have a look at Oren Eini's blog about the <map> and <set> tags particularly the <map> blog. I think that is what you are after.

<map>
<set>

Answer 2

I think that you'll have to loosen up your constraints to get this to work. Like you said, even if NHibernate updated the index instead of the categoryId, "there could be issue if, say, there were also a UNIQUE constraint on EntryID and Index". This is what I would suggest:

-- Looser constraints.  This should work.
CREATE TABLE EntryCategory (
  id int IDENTITY PRIMARY KEY,
  categoryId int REFERENCES Category (id),
  entryId int REFERENCES Entry (id),
  index int
);
CREATE INDEX IX_EntryCategory_category ON EntryCategory (entryId, categoryId);
CREATE INDEX IX_EntryCategory_index ON EntryCategory (entryId, index);

I mean, ideally you would have something like the following - but it would be nearly impossible to run any UPDATE queries on it:

-- Nice tight constraints - but they are TOO tight.
CREATE TABLE EntryCategory (
  categoryId int REFERENCES Category (id),
  entryId int REFERENCES Entry (id),
  index int,
  PRIMARY KEY (entryId, categoryId),
  UNIQUE KEY (entryId, index)
);

Whenever I'm stuck in a situation like this where what I really want is a unique key but for some reason I'm forced to do without it, I settle for an index instead.

---

In case it's helpful, Ayende has an example with a very similar mapping - a many-to-many list. He doesn't talk about the database schema, though.