NHibernate Many to One / One to One with differing keys

Question

Hey all, I'm kicking the tires on NHibernate and have a conoundrum I have been scratching my head over for a bit now, working with a legacy database with some fairly complex relationships.

ClaimRoot has a primary key of a claimGUID. ClaimRoot has a bag of Claimdetails associated by claimGUID (this works a treat).

The problem is that ClaimRoot also has an optional one to one relationship with ClaimFinancials (not all ClaimRoots have ClaimFinancials, but most do). But the PK for ClaimFinancials is a FormID field. This field exists in the ClaimRoot, but is not the PK.

I've posted a mapping below with extra columns removed to protect the innocent.

  <class name="ClaimRoot" table="tbl_ClaimRoot" schema="DB1.dbo">
    <id name="ClaimGUID">
      <generator class="guid"/>      
    </id>
    <property name="FormID" />
    <property name="LastFormNoteText" />
    <bag name="ClaimDetails" inverse="true">
      <key column="ClaimGUID"/>
      <one-to-many class="ClaimDetails"/>
    </bag>
  </class>

  <class name="ClaimDetails" table="tbl_ClaimDetails" schema="DB2.dbo">
    <id name="RowID">
      <generator class="native"/>
    </id>
    <property name="ClaimGUID" />
    <property name="SeqNo"/>
    <property name="B1A_InsID" />
    <many-to-one name="Root" column="ClaimGUID" foreign-key="ClaimGUID"/>
  </class>


  <class name="ClaimFinancials" table="tbl_ClaimFinancials" schema="DB1.dbo">
    <id name="FormID">
      <generator class="native"/>
    </id>
    <property name="CreatedDate"/>
    <property name="SubmittedDate" />
  </class>

Thanks in advance! -Bob

Answer 1

Assuming the FormID is use only for linking ClaimRoot and ClaimFinancials, it sounds like you want a many-to-one relationship from ClaimRoot to ClaimFinancials. Replace the FormId property on ClaimRoot with a many-to-one.

  <class name="ClaimRoot" table="tbl_ClaimRoot" schema="DB1.dbo">
    ...
    <many-to-one name="ClaimFinancials" column="FormID" />
    ...
  </class>

A many-to-one relationship can be be used even if there is only 'one' on the 'many' side. If you were generating a schema, you can specify unique="true" to generate the constraint in the database. With a legacy database, that won't matter.