Relationships in nHibernate using simple columns, not PKs

Question

Hi,

I have a table T1 that has these columns:

• ID (int) - is a primary Key
• Ref (int) - (not null)
• Name (string)

And a second table T2 with columns:

• T2ID (int) - is a primary key
• Value1 (int)
• Value2 (int)
• SomeOtherData (int)

Each record from T1 has >2 records in T2: Ref column in T1 has a value from T2 table, T2ID column.

How to write mapping file for this scenario? Currently I use this:

<class name="Class1" table="T1" >
    <id name="ID" column="ID">
        <generator class="increment"/>
    </id>
    <property name="Name"/>

        <set name="Data" inverse="true" fetch="select">
            <key>
                <column name="T2ID"/>
            </key>
            <one-to-many class="Class2"/>
        </set>
</class>

<class name="Class2" table="T2" >
    <id name="ID" column="T2ID">
        <generator class="increment"/>
    </id>
    <property name="Value1"/>
    <property name="Value2"/>
    <property name="SomeOtherData"/>
</class>

in code I have this to load T2 data for T1:

NHibernateUtil.Initialize( class1.Data );

Which generates this SQL:

SELECT 
T2ID, Value1, Value2, SomeOtherData
FROM T2
WHERE T2ID = <**an ID column from T1 for which I'm loading T2 data!**>

The problem is how to tell nHibernate to use a value from Ref column to load Data property, not PK?

Cheers, Alex

Answer 1

As far as I know, this isn't supported. The property-ref element allows you to map this type of relationship from the "many" side but there's no equivalent for mapping the "one" side.

A workaround is to create a view for T1 that includes T2ID and map that instead of the table.