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"/>
<!--
</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 ?