self-reflexive n:m relation using composite keys in nhibernate

Question

I have a legacy database with 3 tables like this:

_{(source: bilder-hochladen.net)}

The Items table contains all the Items in a Plan. The Structure table defines the relation between the items. A parent item is defined by company, year, planId and parentItem of table structure mapping to company, year, planId and id of table item.
A child item is defined by company, year, planId and childItem of table structure mapping to company, year, planId and id of table item.

I am searching for a way to do a n:m mapping in nhibernate using either hbm or fluent mappings.

I came up with:

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping namespace="Project.Model" assembly="Project" xmlns="urn:nhibernate-mapping-2.2">
  <class name="Item" lazy="true" table="`item`" schema="`dbo`">
    <composite-id>
        <key-property name="Company" column="`company`" />
        <key-property name="Year" column="`year`" />
        <key-property name="Planid" column="`planid`" />
        <key-property name="ItemId" column="`id`" />
    </composite-id>  
    <!-- Some other properties -->

    <set name="Parents" table="`structure`" fetch="select">
        <key>
            <column name="`company`" />
            <column name="`year`" />
            <column name="`planid`" />
            <column name="`parentItem`" />
        </key>
        <many-to-many class="Item">        
            <column name="`company`" />
            <column name="`year`" />
            <column name="`planid`" />
            <column name="`id`" />
        </many-to-many>
    </set>

    <set name="Childs" table="`structure`" fetch="select">
        <key>
            <column name="`company`" />
            <column name="`year`" />
            <column name="`planid`" />
            <column name="`childItem`" />
        </key>
        <many-to-many class="Item">        
            <column name="`company`" />
            <column name="`year`" />
            <column name="`planid`" />
            <column name="`id`" />
        </many-to-many>
    </set>

    <many-to-one name="Plan" class="Plan" not-null="true" fetch="select">
        <column name="`company`" />
        <column name="`planid`" />
    </many-to-one>
  </class>
</hibernate-mapping>

The error however is: Repeated column in mapping … - so I'm stuck. Any suggestions?

Answer 1

Well I managed to solve this one. The key was to use key-many-to-one (KeyReference in FluentNHibernate) and not to make n:m mapping (because it does not work!):

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping namespace="Project.Model" assembly="Project" xmlns="urn:nhibernate-mapping-2.2">
<class name="Item" lazy="true" table="`item`" schema="`dbo`">
  <composite-id>
    <key-many-to-one name="Plan" class="Plan">
      <column name="Company" column="`company`" />
      <column name="Planid" column="`planid`" />
    </key-many-to-one>
    <key-property name="Year" column="`year`" />
    <key-property name="ItemId" column="`id`" />
  </composite-id>  
  <!-- Some other properties -->

  <set name="Parents" table="`structure`" fetch="select">
    <key>
        <column name="`company`" />
        <column name="`year`" />
        <column name="`planid`" />
        <column name="`parentItem`" />
    </key>
    <many-to-many class="Item">        
        <column name="`company`" />
        <column name="`year`" />
        <column name="`planid`" />
        <column name="`id`" />
    </many-to-many>
  </set>

  <set name="Childs" table="`structure`" fetch="select">
    <key>
        <column name="`company`" />
        <column name="`year`" />
        <column name="`planid`" />
        <column name="`childItem`" />
    </key>
    <many-to-many class="Item">        
        <column name="`company`" />
        <column name="`year`" />
        <column name="`planid`" />
        <column name="`id`" />
    </many-to-many>
  </set>
  </class>

  <class name="Structure" lazy="true" table="`item`" schema="`dbo`">
    <composite-id>
  <key-many-to-one name="Parent" class="Item">
    <column name="company" />
    <column name="year" />
    <column name="planid" />
    <column name="parentItem" />
  </key-many-to-one>
  <key-many-to-one name="Child" class="Item">
    <column name="company" />
    <column name="year" />
    <column name="planid" />
    <column name="childItem" />
  </key-many-to-one>
  <key-property name="StructureId" column="structureId" />
</composite-id>      
</class>

</hibernate-mapping>

This looks somewhat weird, esp. the part where company, year and planid are twice in the key of Structure. But It works…