Hi,

We are facing issue with "Many to one" association. here we need to map with non-primary key column on parent table (which could be the source of the problem).

Following are our two tables and their mapping files.

Parent table mapping:
================
<id name="Id" column="primkey">
<generator class="identity" />
</id>
<property name="Code" column="code" />
<property name="CodeType" column="codetype" />
<property name="CodeName" column="codename" />
<property name="RecDesc" column="recdesc" />

<set name="SalesrepCodes" lazy="true">
<key column="mktregion" foreign-key="mktregion" />
<one-to-many class="SalesRep" />
</set>

Note: In the able table primkey is the actual primary key. But internally (i.e problematically) we have to consider "CodeName" as primary key column. CodeName is of data type char(2). This codeName column is used as foreign key (not created physically) in child table below

Child table:
========

<id name="Id" column="primkey">
<generator class="identity" />
</id>
<!--<version name="EntityVersion" column="recver" />-->
<property name="SalesRep" column="salesrep" />
<property name="MarketingRegionCode" column="mktregion" />
<property name="SalesRepName" column="repname" />
<property name="Phone" column="phone" />

<many-to-one name="MarketingRegionObject" lazy="proxy"
class="MarketingRegion" column="mktregion" fetch="join"
property-ref="CodeName" not-null="true"
cascade="all" />

Note: In the above table mktregion is the column that contains parent table codename values.

So, basically we are trying map many to one join using non-primary key column. our database has developed many years ago and we have to follow the same structure unfortunately.

Now our problem is below.

1. We are able load salesrep object with no issues. The corresponding marketing region of sales rep object is loading perfectly fine. But when we are trying load marketing region object (parent table), it is not loading list of associated sales rep objects (child table rows). When we observe output window of VS2008, it is comparing with primkey value of parent table instead of non-primary key column that we have mapped. Following is the sql statement nhibernate is trying to execute. 67 is the primary key column value in parent table.

FROM salesrep salesrepco0_ WHERE salesrepco0_.mktregion=?;p0 = 67

The correct statement should be

FROM salesrep salesrepco0_ WHERE salesrepco0_.mktregion=?;p0 = 'NW' (instead of primary key value it should pass non-primary key value that we have mapped)

Please let me know, whether we can map non-primary key columns in many to one joins or not. If we can map, then please let me know if any thing is wrong in our mapping files or any alternatives to satisfy our requirement.

Your help regarding this will be much appreciated.

Regards
Kishore