NHibernate navigators mapped to the part of a composite key problem - legacy database usage

Question

We have a legacy database that we cannot change. And we are trying to move to the NHibernate instead of old DataAccess layer which is a garbage and is too slow.

it has tables like these:

GPI table has (PU_ID, PAR_ID, Data, Data2) columns
BLOCK table has (GA_ID, Data, PAR_ID) columns
COMPANY table has (PU_ID, Data) columns

I had created these mappings for the tables above:

GPI

<class name="GroupPartnerInterest" table="[GPI]">
    <composite-id >
        <key-property name="GroupId" column="PAR_ID" />
        <key-property name="CompanyId" column="PU_ID" />
    </composite-id>
    <property name="data" column="Data"/>
    <property name="data2" column="Data2"/>
    <many-to-one name="Company" fetch="select" cascade="none">
        <column name="PU_ID"/>
    </many-to-one>
    <set name="Blocks" cascade="none" inverse="true" fetch="select">
        <key property-ref="GroupId">
            <column name="PAR_ID"/>
        </key>
        <one-to-many class="Block"/>
    </set>
</class>

BLOCK

<class name="Block" table="[BLOCK]" >
    <id name="BlockId" column="GA_ID" >
        <generator class="assigned"/>
    </id>
    <property name="data" column="Data"/>
    <property name="GroupId" column="PAR_ID"/>
    <set name="GroupPartnerInterests" cascade="all-delete-orphan" fetch="select">
        <key property-ref="GroupId">
            <column name="PAR_ID"/>
        </key>
        <one-to-many class="GroupPartnerInterest"/>
    </set>
</class>

COMPANY

<class name="Company" table="[COMPANY]">
    <id name="CompanyId" column="PU_ID">
        <generator class="assigned"/>
    </id>
    <property name="data" column="Data"/>
    <set name="GroupPartnerInterests" cascade="none" inverse="true" fetch="select">
        <key>
            <column name="PU_ID"/>
        </key>
        <one-to-many class="GroupPartnerInterest"/>
    </set>
</class>

The classes are very simple and plain. All implement Equals and GetHashCode methods.

Here is the list of navigators that work:

• GroupPartnerInterest.Company - works great
• Company.GroupPartnerInterests - works great
• GroupPartnerInterest.Company - works great

And these two fail:

• Block.GroupPartnerInterests:

I have a unit test:

[TestMethod]
public void TestGroupPartnerInterests()
{
    using ( ISession session = SessionFactory.OpenSession() )
    {
        IList<Block> blocks = session.CreateCriteria( typeof( Block ) )
            .SetMaxResults( 5 ).List<Block>();

        foreach ( var block in blocks )
        {
            TestContext.WriteLine( "Block #{0}", block.BlockId );

            if ( block.GroupPartnerInterests != null )
            {
                foreach ( GroupPartnerInterest gpi in block.GroupPartnerInterests )
                {
                    TestContext.WriteLine( "Company '{0}':", gpi.Company.CompanyId );
                }
            }
        }
    }
}

If I comment out Blocks navigation mapping in GPI mapping test works and outputs some data:

Block #1
Company 'LALA':
Company 'LALA SA':
Block #2
Company 'BG PO':
Company 'LIMPOPO':
Block #3
Company 'HAHA':
Company 'Other partner(s)':
Block #4

But the test fails with the following error:

NHibernate.LazyInitializationException: Initializing[Model.EntityClasses.Block#999]-failed to lazily initialize a collection of role: Model.EntityClasses.Block.GroupPartnerInterests, no session or session was closed.

The '999' is existing PAR_ID - data is consistent: there are two blocks with this PAR_ID and a few records in GPI.

Why it closes session at some point?

• GroupPartnerInterest.Blocks:

The unit test is almost the same as I mentioned above, just different properties are used. An error is below:

NHibernate.MappingException: NHibernate.MappingException: property not found: GroupId on entity Model.EntityClasses.GroupPartnerInterest.

If I remove "property-ref=GroupId" from the element of Blocks navigator in the GPI mapping, I will get the following exception:

NHibernate.FKUnmatchingColumnsException: NHibernate.FKUnmatchingColumnsException: Foreign key (FKA3966498349694F:[BLOCK] [PAR_ID])) must have same number of columns as the referenced primary key ([GPI] [PAR_ID, PU_ID]).

Is there any way to map Blocks to GPI so the GroupPartnerInterest.Blocks navigator will work?

Thanks, Alex

Answer 1

The problem is the following:

• If you have a entity with a composite id, all references to it must maintain the composite id, so there must be two foreign keys.
• Blocks in GroupPartnerInterest is a set, so the foreign key is in Blocks, pointing to GroupPartnerInterest. It would require two foreign keys, which are not available.
• property-ref is to replace the primary key with some other property. It is therefore a property of the table on the one-side of the relation, which is GroupPartnerInterest, but there is no GroupId.
• You could probably use property-ref for GroupPartnerInterest.Blocks (because the two foreign keys are missing, to make Block.PAR_ID point to GPI.PAR_ID), but I would think twice about it.

I can't give you a working solution here. I don't use composite keys and this is more complex. But there are some more thoughts:

• I would try to avoid the composite key. If it is not possible, write a class which represents the composite key. This makes it much easier to handle it.
• I would try to avoid relations that are not based on the primary key. There may be reasons to do otherwise, NH supports them, I just think that they cause troubles.

---

Why is the session closed? I don't know, I would take a look at the stack trace. It the exception really thrown from within the using block? Or is it thrown from a TestCleanup method?