NHibernate navigators associated with part of a complex key issue - using an outdated database

Question

We have an outdated database that we cannot change. And we are trying to switch to NHibernate instead of the old DataAccess layer, which is garbage and works too slowly.

he has such tables:

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

I 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> 

COMPANIES

 <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> 

Classes are very simple and simple. All implement the Equals and GetHashCode methods.

Here is a list of navigators that work:

• GroupPartnerInterest.Company - Works Great
• Company .GroupPartnerInterests - Works Great
• GroupPartnerInterest.Company - Works Great

And these two errors:

• 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 that the Blocks navigation mapping in the GPI mapping test works and outputs some data:

Block No. 1
Company "LALA":
Company "LALA SA":
Block number 2
Company "BG PO":
Company "LIMPOPO":
Block No. 3
Company "HAHA":
Company "Other partner (s)":
Block No. 4

But the test fails with the following error:

NHibernate.LazyInitializationException: Initializing [Model.EntityClasses.Block # 999] - Initially unsuccessful role collection: Model.EntityClasses.Block.GroupPartnerInterests, session or session is not closed.

"999" PAR_ID already exists - the data is compatible: there are two blocks with this PAR_ID and several entries in the GPI.

Why does he close the session at some point?

• GroupPartnerInterest.Blocks:

unit test is almost the same as I mentioned above, only different properties are used. The following is the error:

NHibernate.MappingException: NHibernate.MappingException: Property not found: GroupId on Model.EntityClasses.GroupPartnerInterest.

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

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

Is there a way to map blocks to the GPI so that the GroupPartnerInterest.Blocks navigator works?

Thanks Alex

Answer 1

The problem is this:

• If you have an object with a composite id, all references to it must support a composite identifier, so there must be two foreign keys.
• Blocks in GroupPartnerInterest is a collection, so the foreign key is in Blocks , pointing to GroupPartnerInterest . This will require two foreign keys that are not available.
• property-ref is to replace the primary key with some other property. Therefore, this is a table property on one side of the relationship, which is GroupPartnerInterest , but no GroupId .
• You might have used property-ref for GroupPartnerInterest.Blocks (since there are no two foreign keys for Block.PAR_ID point to GPI.PAR_ID ), but I would have thought about it twice.

I cannot give you a working solution here. I do not use compound keys, and this is more complicated. But there are a few more thoughts:

• I would try to avoid a complex key. If this is not possible, write a class representing the composite key. This facilitates its processing.
• I would try to avoid relationships that are not based on a primary key. There may be reasons to do otherwise, NH supports them, I just think they cause problems.

---

Why is the session closed? I don’t know, I would look at the stack trace. Is this exception thrown from the use block? Or is it thrown from the TestCleanup method?