NHibernate - How to map composite-id with parent c

i have the following scenario (For better illustration, the example is very simplified) Database Model BAS_COSTCODE refers to BAS_CONTEXT. The key for this table is a composite with COSTCODEID and the CONTEXT_FK. CONTEXT_FK refers to BAS_CONTEXT. To build up a hierarchical tree, a CostCode can have a parent. For this reason there is a reference to the table itselfe.

The schema file for Context is like that:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" schema="dbo" assembly="App.NHibernate.DataObjects" namespace="App.NHibernate.DataObjects">
      <class name="App.NHibernate.DataObjects.Context" table="BAS_CONTEXT" lazy="false" mutable="true" >
        <id name="Id" type="Int16" column="CONTEXTID" >
          <generator class="assigned"/>
        </id>
        <property name="Name" column="NAME" type="String" not-null="true"/>
       </class>
    </hibernate-mapping>

And the schema for CostCode is like that:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" schema="dbo" assembly="App.NHibernate.DataObjects" namespace="App.NHibernate.DataObjects">
  <class name="App.NHibernate.DataObjects.CostCode" table="CON_COSTCODE" mutable="true" >

    <composite-id name="CompositeId" class="CostCodeId" unsaved-value="any">
      <key-property name="Id" column="COSTCODEID" type="String" />
      <key-many-to-one name="Context" column="CONTEXT_FK" class="Context" lazy="proxy"/>
    </composite-id>

    <many-to-one name="ParentCostCode" class="CostCode" lazy="proxy" insert="true" update="true" >
      <column name="PARENTCOSTCODE_FK"/>
      <column name="CONTEXT_FK"/>
    </many-to-one>

    <property name="Name" column="NAME" type="String"/>
  </class>
</hibernate-mapping>

If i create a new CostCode entity and run Commit() i get the following exception: System.IndexOutOfRangeException: "Invalid index 13 for this SqlParameterCollection with Count=13."

I think NHibernate has a problem with the reference ParentCostCode, which refers to a parent CostCode object. NHIbernate assumes to write a value to column PARENTCOSTCODE_FK and to CONTEXT_FK. In the mapping the composite-id points to CONTEXT_FK and the ParentCostCode points to CONTEXT_FK. So they are "sharing" the same column and NHibernate should only write a value to the column PARENTCOSTCODE_FK.

One solution is to add an additional column (maybe PARENTCONTEXT_FK) to table BAS_COSTCODE to represent the Context for the parent object. But i don't want to have an additional column, because the values of CONTEXT_FK and PARENTCONTEXT_FK must have the same value, if there exist a referenceto a parent object. And that would be redundant.

Are there better solutions for that kind of problem? Or i can't prevent a additional column in BAS_COSTCODE?

Many thanks in advance for your answers!

回答1:

We had a similar issue and spent some time researching possible solutions. We could not find a way to do this without having the redundant column. It's a little ugly but you can put some logic into your model classes to enforce consistency of the two values.

回答2:

I think this error is happening because you have the same column mapped twice. CONTEXT_FK

<many-to-one name="ParentCostCode" class="CostCode" lazy="proxy" insert="true" update="true" >
  <column name="PARENTCOSTCODE_FK"/>
  <column name="CONTEXT_FK"/>
</many-to-one>

You may want to try this to resolve it:

<many-to-one name="ParentCostCode" class="CostCode" lazy="proxy" insert="false" update="false">

回答3:

It is straight forward to use a non-composite key.

<id>
  <generator class="hilo">
     <!-- ... -->
  </generator>
</id>

<property name="CostCode" column="COSTCODEID" unique-key="CostCodeContext"/>

<many-to-one name="Context" >
  <column name="CONTEXT_FK" unique-key="CostCodeContext"> 
</many-to-one> 

<many-to-one name="Parent" class="CostCode">
  <column name="PARENT_FK"/>
</many-to-one>