Click here to Skip to main content
15,908,673 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am trying to use NHibernate on a legacy database that manages some sort of generic tables. I'll explain below.
I have a "base" table with 3 fields (I removed unrelevant fields) :

SQL
Base
(
    Address (int, Primary key)
    ClassId (int)
    Identifier (string)
)


There is a unique constraint on ClassId and Identifier. The Identifier field contains the concatenation of the ident fields (with separators, not always the same) for the derived table.


Then I have many derived tables that share the Address with the base table (there is a one-to-one association) and each derived table has its own natural identifiers.
For example,

SQL
Derived1
(
    Address (int, Primary key)
    Id1 (string)
    Id2 (string)
)

Id1 and Id2 are part of a unique constraint.

SQL
Derived2
(
    Address (int, Primary key)
    Id1 (string)
    Derived1Ident (string)
)

Id1 is part of a unique constraint.
Derived1Ident is a "pointer" on a Derived1 item but it uses the concatenation of identifiers not the Address field.

image

If I create a Derived1 item with Id1 = "D1" and Id2 = "A", then a Derived2 item with Id1 = "D2" pointing on previous Derived1 item, this would create rows like this :

image


I use the following mapping for NHibernate :
HTML
<class name="Base" discriminator-value="0">
        <id column="Address" type="int" name="Address">
            <generator class="hilo">
                <param name="table">Addresses</param>
                <param name="column">next_value</param>
                <param name="max_lo">100</param>
            </generator>
        </id>
        <discriminator column="ClassId" type="int"/>
        <property name="Identifier" type="string"/>
        <subclass name="Derived1" discriminator-value="1">
            <join table="Derived1">
                <key column="Address"/>
                <property name="Id1" unique-key="ident"/>
                <property name="Id2" unique-key="ident"/>
                <property name="StringVal1" type="string"/>
                <property name="IntVal1" type="int"/>
                <property name="DateVal1" type="datetime"/>
            </join>
        </subclass>
        <subclass name="Derived2" discriminator-value="2">
            <join table="Derived2">
                <key column="Address"/>
                <property name="Id1" unique-key="ident"/>
                <property name="StringVal1" type="string"/>

                <!--<many-to-one name="Derived1Ident" class="Test5Derived1" property-ref="Identifier"/>-->
            </join>
        </subclass>
    </class>


It works well until I add the many-to-one (commented in the mapping xml above) then I get the following error :

There are no primary or candidate keys in the referenced table 'Base' that match the referencing column list in the foreign key 'FK91E675AD9AF7A29'.
Could not create constraint. See previous errors.





It seems NHibernate doesn't use the discriminator to find the row with the matching Identifier.
I tried with other inheritance mapping models but I can't figure out how to point on the Base class Identifier field with a discriminator where clause.
Is there a way to map these legacy tables with NHibernate ?
Posted
Updated 9-Aug-11 5:45am
v7

1 solution

I eventually found a way to map this legacy model using foreign-key="none" on many-to-one property and adding a unique-key on ClassId and Identifier fields

XML
<class name="Base" discriminator-value="0">
        <id column="Address" type="int" name="Address">
            <generator class="hilo">
                <param name="table">Addresses</param>
                <param name="column">next_value</param>
                <param name="max_lo">100</param>
            </generator>
        </id>
        <discriminator column="ClassId" type="int"/>
        <property name="ClassId" type="int" update="false" insert="false" unique-key="key1" />
        <property name="Identifier" type="string" unique-key="key1" />
        <subclass name="Derived1" discriminator-value="1">
            <join table="Derived1">
                <key column="Address"/>
                <property name="Id1" unique-key="ident"/>
                <property name="Id2" unique-key="ident"/>
                <property name="StringVal1" type="string"/>
                <property name="IntVal1" type="int"/>
                <property name="DateVal1" type="datetime"/>
            </join>
        </subclass>
        <subclass name="Derived2" discriminator-value="2">
            <join table="Derived2">
                <key column="Address"/>
                <property name="Id1" unique-key="ident"/>
                <property name="StringVal1" type="string"/>

                <many-to-one name="Derived1Ident" class="Test5Derived1" property-ref="Identifier" foreign-key="none"/>
            </join>
        </subclass>
</class>


I hope this can help someone else ;)
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900