NHibernate one-to-one mapping where second table data can be null.

Question

I have an existing database with the table Transactions in it. I have added a new table called TransactionSequence where each transaction will ultimately have only one record. We are using the sequence table to count transactions for a given account. I have mapped this as a one-to-one mapping where TransactionSequence has a primary key of TransactionId.

The constraint is that there is an instead of trigger on the transaction table does not allow updates of cancelled or posted transactions.

So, when the sequence is calculated and the transaction is saved, NHibernate tries to send an update on the transaction like 'UPDATE Transaction SET TransactionId = ? WHERE TransactionId = ?'. But this fails because of the trigger. How can I configure my mapping so that NHibernate will not try to update the Transaction table when a new TransactionSequence table is inserted?

Transaction mapping:

<class name="Transaction" table="Transaction" dynamic-update="true" select-before-update="true">
    <id name="Id" column="ID">
        <generator class="native" />
    </id>

 <property name="TransactionTypeId" access="field.camelcase-underscore" />
 <property name="TransactionStatusId" column="DebitDebitStatus" access="field.camelcase-underscore" />

    <one-to-one name="Sequence" class="TransactionSequence" fetch="join"
                 lazy="false" constrained="false">      
    </one-to-one>
</class>

And the sequence mapping:

<class name="TransactionSequence" table="TransactionSequence" dynamic-update="true">
    <id name="TransactionId" column="TransactionID" type="Int32">
        <generator class="foreign">
            <param name="property">Transaction</param>
        </generator>
    </id>

    <version name="Version" column="Version" unsaved-value="-1" access="field.camelcase-underscore" />

 <property name="SequenceNumber" not-null="true" />

    <one-to-one name="Transaction" 
                class="Transaction" 
                constrained="true" 
                foreign-key="fk_Transaction_Sequence" />

</class>

Any help would be greatly appreciated...

Answer 1

One to one mapping in nhibernate doesn't work the way you think it does. It's designed so that you have two classes, which when persisted to their corresponding tables have the same primary keys.

However you can make it work, but it's not pretty. I'll show you how then offer up some alternatives:

In your Transaction hbml:

<one-to-one name="Sequence" class="TransactionSequence" property-ref="Transaction"/>

In your Sequence html:

<many-to-one name="Transaction" class="Transaction" column="fk_Transaction_Sequence" />

This should do what you want it to do. Note the property-ref.

The next question you're going to post on is going to ask how you get lazy loading on one-to-one associations. The answer is, you can't... well you can, but it probably won't work. The problem is that you have your foreign key on the sequence table, which means that nhibernate has to hit the database to see if the target exists. Then you can try playing around with constrained="true/false" to see if you can persuade it to lazily load the one-to-one association.

All in all, it's going to result in a total waste of your time.

I suggest either:

1. Have two many-to-one associations.
2. Have a many-to-one association with a collection on the other end.

This will save you a lot of headaches in the long run.

Answer 2

Turns out that for my situation a <join table> mapping worked best. I just had to make sure that I made the properties that came from the second table were nullable types, or it would do an insert on save even if nothing had changed. Since I did not need lazy loading for the second table, this works great. I am sure that I could have gotten paired many-to-one mappings to work, but it was not intuitive and seems more complicated than the join table option, however <join table> is only available in NHibernate 2.0 and up.