Mapping one to many collection with Foreign Key to Foreign key

Question

I'm mapping a legacy database with nhibernate and having some problems with mapping a realation.

The two classes look like this

public class Questionnaire
{
    public int Id {get; set;}
    public string FormCode {get; set;}
    public IList<Question> Questions {get; set;}
}

public class Question
{
    public int Id{get; set;}
    public Questionnaire Questionnaire {get;set;}
    public string QuestionText{get;set;}
}

to tables that are like this

Questionnaire Table
Id int
FormCode varchar(100)

Question Table
Id int
FormCode varchar(100)
QuestionText varchar(max)

The relationship between the two tables being the formcode column.

My current mapping is like this

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="QDesign.Core.Models" assembly="QDesign.Core">
<class name="Questionnaire" table="_questionnaire_list">
    <id column="Id" name="Id">
            <generator class="identity"/>
    </id>
        <property name="FormCode" column="FormCode"/>
        <bag name="Questions" >
            <key foreign-key="FormCode" property-ref="FormCode" />
            <one-to-many class="Question" />            
        </bag>
</class>
</hibernate-mapping>

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="QDesign.Core.Models" assembly="QDesign.Core">
<class name="Question" table="_questionnaire_items">
    <id column="ID" name="Id" unsaved-value="-1">
            <generator class="identity" />
        </id>
        <property name="QuestionText" column="QuestionText" />
</class>
</hibernate-mapping>

When I run the mapping I get a Identifier type mismatch assuming it is trying to put the formcode into the Id of the question. Unfortunalty I am unable to change the structure of the table and I am at a lost at how to map this and any help would be greatly appriciated.

Colin G

Answer 1

Well this is the "problem" with ORMs. I say "problem" because Hibernate is technically correct: foreign keys should be primary keys. But, as you're witnessing, that's not always the case.

Is ID actually used for anything? If not, the path of least resistance is to make FormCode the primary key. Is that an option?

If not, I'm really not sure what to do other than query the questions rather than treating them as child entities.

Answer 2

Your relationship is an m:n relationship without an intermediate entity. It's a classic mistake to define m:n relationships this way, because it cuts out a pk side, which leads to the situation where there are two tables with the same attribute/field and they happen to semantically represent the same thing. However, because they're non-pk values in both sides, there's redundancy and also inaccuracy possible. You can join the two tables together over the two fields but semantically it means nothing: for an entity model to relate entity X to Y, the FK side gets the PK side of the relationship as FK fields, and for m:n relationships, you need two m:1 relationships originating from the intermediate entity. That's it, there's no exception.

So although you want to map this the way you propose, it can't be done as the o/r mapper can't guarantee correctness because the suggested relationship is simply not correct.