Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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) :
 
    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,
 
Derived1
(
    Address (int, Primary key)
    Id1 (string)
    Id2 (string)
)
Id1 and Id2 are part of a unique constraint.
 
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 :
<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 3-Aug-11 4:24am
Edited 9-Aug-11 6:45am
v7

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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
 
<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 Wink | ;)
  Permalink  
v2

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



Advertise | Privacy | Mobile
Web02 | 2.8.141220.1 | Last Updated 12 Aug 2011
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100