How to get and save individual joined values from multiple tables in NHibernate?

Question

I can't get too specific as far as what we're doing, but here goes:

We have Reps that are clients of ours and they can each only work with one Firm, but each Firm has their own way of identifying their reps, however all Reps are stored in our Rep table.

The PK in our Rep table is RepID, however because of their IDs assigned to them by their respective firm, we also have a varchar RepNumber.

Enter the table/entity RepAddress. The table has a FK to Rep via RepID, however due to legacy systems the class RepAddress has to have RepNumber.

What I'm trying to accomplish is being able to save/update a RepAddress by setting the RepNumber and getting NHibernate to get/update the RepID in the RepAddress table from the Rep table based on RepNumber.

Here's my failed attempt at the RepAddress mapping:

<class name="RepAddress">
    <id name="AddressID">
      <generator class="native" />
    </id>
    <property name="AddressType" column="AddressTypeID" />
    <property name="AttentionLine" />
    <property name="CareOfLine" />
    <property name="AddressLine1" />
    <property name="AddressLine2" />
    <property name="AddressLine3" />
    <property name="City" />
    <property name="ZipCode" />
    <property name="State" column="StateTypeID" />
    <property name="Province" column="ProvinceTypeID" />
    <property name="Country" column="CountryTypeID" />
    <property name="LastModifiedDate" />
    <property name="SessionActivityID" />

    <join table="Rep">
      <key column="RepID"/>
      <property name="RepNumber" />
    </join>
</class>

Thanks for any help in advanced! Let me know if you need any more information.

Answer 1

Your RepAddress class should have a reference to a Rep instance (look up many-to-one in the docs)

Since RepNumber is not the PK, you need a query to get the Rep instance. Then you just assign it.

If you had the RepID, you'd be able to do the following, which does not need a DB roundtrip:

repAddress.Rep = session.Load<Rep>(repId);