NHibernate many to many mapping

Question

So I have a situation I'm trying to map with NHibernate. Say we have the following schema:

Question
- QuestionID
- QuestionText
- SiteID

Tag
- TagID
- TagName
- SiteID

QuestionTags
- QuestionID
- TagID

This would work fine for that:

<class name="Question" table="Questions">
      <key column="QuestionID" />
      <property name="QuestionText" not-null="true" />
      <property name="SiteID" not-null="true" />

      <set name="Tags" table="QuestionTags">
        <key column="QuestionID" />
        <many-to-many class="Tag" column="TagID" />
      </set>
</class>

<class name="Tag" table="Tags">
      <key column="TagID" />
      <property name="TagName" not-null="true" />
      <property name="SiteID" not-null="true" />
</class>

However, I want to add a SiteID column to QuestionTags, and add SiteID to each of its foreign keys...

(QuestionTags.QuestionID, QuestionTags.SiteID) -> (Questions.QuestionID, Questions.SiteID)
(QuestionTags.TagID, QuestionTags.SiteID) -> (Tags.TagID, Tags.SiteID)

...so that Questions and Tags with differing SiteIDs cannot be associated. How can I map that situation?

score 0 · Accepted Answer · edited May 23 '17 at 11:55

Is there an external requirement to have the SiteID column on the QuestionTags table? The Question and Tag already have SiteID and the domain model can enforce that a Tag can't be assigned to a Question with a different SiteID. So from an application perspective, you don't need it.

The join/maping table for a many-to-many relationship only supports two columns. Keep in mind that this table does not represent an entity, but only the relationship between entities. If you add any additional data/columns, then it will need to become an entity in its own right with relationships to the other two entities. See this question for an example of an intermediate entity to support another column in the mapping table.

NHibernate many to many mapping

1 Answers1