NHibernate mapping child collection without foreign key relationship

Question

I have an Account table and a Transaction table without a foreign key reference from the Transaction table to the Account table for "legacy reasons". The NHibernate Account table's mapping XML looks like this:

<class name="Account" table="[tblAccount]" lazy="true">
    <id name="ID" column="ID" type="Int32">
      <generator class="native" />
    </id>
    <version name="NHVersion" column="[NHVersion]" unsaved-value="0" />
    <property name="AccountNumber" column="[AccountNumber]" type="String" not-null="true" length="30" />
    <property name="FormattedAccountNumber" column="[FormattedAccountNumber]" type="String" not-null="true" length="30" />
    <property name="Title" column="[Title]" type="String" not-null="true" length="30" />
    <many-to-one name="AccountCode" column="tblAccountCodeID" class="AccountCode" cascade="none" />
    <one-to-one name="AccountSegment" cascade="all" class="AccountSegment" property-ref="Account" />
    <bag name="Categories" table="tblAccountCategory" lazy="true" inverse="false">
      <key column="tblAccountID" />
      <many-to-many class="Category" column="tblCategoryID" />
    </bag>
</class>

The Transaction table's mapping XML is:

<class name="Transaction" table="[tblTransaction]" lazy="true">
    <id name="ID" column="ID" type="Int32">
      <generator class="native" />
    </id>
    <version name="NHVersion" column="[NHVersion]" unsaved-value="0" />
    <property name="GLAccount" column="[GLAccount]" type="String" not-null="true" length="30" />
    <property name="Description" column="[Description]" type="String" not-null="true" length="60" />
    <property name="Amount" column="[Amount]" type="Decimal" not-null="true" />
</class>

The Transaction table rows for a given account can be queried using the GLAccount column. The following SQL works:

select act.FormattedAccountNumber, act.Title, trn.Date, trn.Amount
from tblAccount act
left join tblTransaction trn on act.AccountNumber = trn.GLAccount
where act.AccountNumber = '011020'

How do I define the mapping for an account's transactions? There doesn't seem to be a way to specify the column names for each table. Perhaps using where? I looked at this question and answer but it doesn't seem to apply to my situation.

Answer 1

There is NHibernate built-in solution for scenarios without primary key:

FROM tblAccount act
LEFT JOIN tblTransaction trn 
 ON act.AccountNumber = trn.GLAccount

That is the property-ref magic.

<class name="Account" table="[tblAccount]" lazy="true">
   ...
   <!-- THE property -->
   <property name="AccountNumber" column="[AccountNumber]" type="String" not-null="true"/>

   <!-- collection referenced by THE property -->
   <bag name="Transactions" lazy="true" inverse="false">
      <!-- key column of a target table -->
      <!-- property-ref of our table -->
      <key column="GLAccount" property-ref="AccountNumber" />
      <one-to-many class="Transaction" />
   </bag>

That is the parent, below is a child (many-to-one using the same property as reference)

<class name="Transaction" table="[tblTransaction]" lazy="true">
    ...
    <property name="GLAccount"  column="[GLAccount]" type="String" not-null="true"/>
    <many-to-one name="Account" column="[GLAccount]" property-ref="AccountNumber"  />
    

5.1.11. many-to-one

...

The property-ref attribute should only be used for mapping legacy data where a foreign key refers to a unique key of the associated table other than the primary key. This is an ugly relational model. For example, suppose the Product class had a unique serial number, that is not the primary key. (The unique attribute controls NHibernate's DDL generation with the SchemaExport tool.)