many-to-many and foreign-key v/s property-ref

kiran_gawde · **Joined:** Mon Oct 20, 2008 5:22 am **Posts:** 6

NHibernate 2.0.1GA:

Java version of many-to-many mapping has attribute (property-ref), but Nhibernate has foreign-key. Foreign-key doesn't seem to work in following example.

TRADE_ACCOUNT(Primary Key: ACCOUNT_ID)
MASTER_ACCOUNT(Primary Key: ORG_MSTRACCT_ID, Unique Key: MSTR_ACCT_ID)
MSTR_SUB_ACCT (Primary Key: MSTR_ACCT_ID, SUB_ACCT_ID)

Joins needs to be done as below:
TRADE_ACCOUNT.ACCOUNT_ID = MSTR_SUB_ACCT.SUB_ACCT_ID
MASTER_ACCOUNT.MSTR_ACCT_ID = MSTR_SUB_ACCT.MSTR_ACCT_ID

Relavent mapping is as below:

Code:

<class name="TradeAccount" table="TRADE_ACCT" mutable="false">
    <id name="id" column="ACCOUNT_ID">
      <generator class="assigned" /> 
    </id>

    <bag name="masterAccounts" table="MSTR_SUB_ACCT" >
      <key column="SUB_ACCT_ID"/>
      <many-to-many class="MasterAccount" column="MSTR_ACCT_ID" foreign-key="masterAccountId"/>
    </bag>
</class>

<class name="MasterAccount" table="CB_ORG_MSTRACCT">
    <id name="id" column="ORG_MSTRACCT_ID">
      <generator class="identity"/>
    </id>

<property name="masterAccountId" column="MSTR_ACCT_ID"/>

<bag name="subAccounts" table="MSTR_SUB_ACCT">
      <key column="MSTR_ACCT_ID" property-ref="masterAccountId"/>
      <many-to-many class="TradeAccount" column="SUB_ACCT_ID"/>
    </bag>
</class>

Following code is generating wrong Joins:
ICriteria criteria = session.CreateCriteria(typeof (TradeAccount), "ta");
criteria.CreateCriteria("masterAccounts", "ma").Add(Expression.Eq("ma.id", maId));

Joins needs to be done as below:
TRADE_ACCOUNT.ACCOUNT_ID = MSTR_SUB_ACCT.SUB_ACCT_ID and
MASTER_ACCOUNT.MSTR_ACCT_ID = MSTR_SUB_ACCT.MSTR_ACCT_ID

Instead it is doing:
Joins needs to be done as below:
TRADE_ACCOUNT.ACCOUNT_ID = MSTR_SUB_ACCT.SUB_ACCT_ID and
MASTER_ACCOUNT.ORG_MSTRACCT_ID = MSTR_SUB_ACCT.MSTR_ACCT_ID

This seems to be bug in Nhibernate. Please let me know if there is a workaround. Thanks.

wolli · **Posted:** Tue Dec 09, 2008 3:50 am

Afaik the foreign-key attribute is only used for schema generation. You can work around the missing property-ref with modelling the association as an entity and using many-to-one associations to and from that class:

TradeAccount n:1 MasterTradeAccount 1:m MasterAccount

wolli · **Posted:** Tue Dec 09, 2008 3:57 am

Or you can try this approach:

http://www.hibernate.org/118.html#A10

EDIT: Just saw, you already know/found that link :-)

kiran_gawde · **Joined:** Mon Oct 20, 2008 5:22 am **Posts:** 6

I couldn't use composite-element due to the same reason mentioned in following link:
http://www.hibernate.org/118.281.html

I will try to split many-to-many to (many-to-one and one-to-many) and let you know. Thanks!

kiran_gawde · **Joined:** Mon Oct 20, 2008 5:22 am **Posts:** 6

I converted M:N to 1:M and N:1. But now I have performance problem. It seems to be doing multiple queries on CB_ORG_MSTRACCT. One per each subaccount. It doesn't matter which direction I traverse the relationship. BTW, I tried enabling second level cache and adding cache usage to read-only to all the mappings below. It DOES NOT help.

Updated mappings is as below:

Code:

<class name="TradeAccount" table="TRADE_ACCT" mutable="false"> 
<id name="id" column="ACCOUNT_ID"> 
<generator class="assigned" /> 
</id>
    <bag name="masterSubAssociations" table="MSTR_SUB_ACCT"
      <key column="SUB_ACCT_ID"/>
      <one-to-many class="MasterSubAssociation"/>
    </bag>
</class> 

  <class name="MasterSubAssociation" table="MSTR_SUB_ACCT">
    <composite-id>
      <key-property name="masterAccountId" column="MSTR_ACCT_ID"/>
      <key-property name="subAccountId" column="SUB_ACCT_ID"/>
    </composite-id>

    <many-to-one name="masterAccount" class="MasterAccount" column="MSTR_ACCT_ID" property-ref="masterAccountId"/>
    <many-to-one name="subAccount" class="TradeAccount" column="SUB_ACCT_ID"/>
  </class>

<class name="MasterAccount" table="CB_ORG_MSTRACCT"> 
<id name="id" column="ORG_MSTRACCT_ID"> 
<generator class="identity"/> 
</id> 
<property name="masterAccountId" column="MSTR_ACCT_ID"/> 
    <bag name="masterSubAssociations" table="MSTR_SUB_ACCT" fetch="subselect" lazy="false">
      <key column="MSTR_ACCT_ID" property-ref="masterAccountId"/>
      <one-to-many class="MasterSubAssociation"/>
    </bag>
</class> 

wolli · **Posted:** Wed Dec 10, 2008 2:07 am

Try fetch-mode="join" and maybe you have to set "hibernate.max_fetch_depth" in your configuration.

Instead of specifiying a fetch mode in the mapping you should be able to get the same behavior with crit.CreateCriteria("xyz", FetchMode.Join) or in HQL "from MyClass c join fetch c.Bag".

kiran_gawde · **Joined:** Mon Oct 20, 2008 5:22 am **Posts:** 6

I already tried the fetch="join" didn't help.