Nhibernate: one-to-many, based on multiple keys?

Question

Lets assume I have two tables

Table tA
    ID
    ID2
    SomeColumns
Table tB
   ID
   ID2
   SomeOtherColumns

I am looking to create a Object let's call it ObjectA (based on tA), that will have a one-to-many relationship to ObjectB (based on tB). In my example however, I need to use the combination of ID and ID2 as the foreign key. If I was writing SQL it would look like this:

select tB.* from tA, tB where tA.ID = tB.ID and tA.ID2 = tB.ID2;

I know that for each ID/ID2 combination in tA I should have many rows in tB, therefor I know it's a one-to-many combination. Clearly the below set is not sufficient for such mapping as it only takes one key into account.

<set name="A2" table="A2" generic="true" inverse="true" >
  <key column="ID" />
  <one-to-many class="A2" />
</set>

Thanks!

Answer 1

I am looking for a solution to this exact same problem. It seems as if One to Many relationships can only have a single column specified in the mapping. There has to be some way around this...

Answer 2

Have you tried this?

<set name="A2" table="A2" generic="true" inverse="true" >
  <key>
    <column ="ID" />
    <column ="ID2" />
  </key>
  <one-to-many class="A2" />
</set>

Answer 3

To me, it sounds like you are attaching wrong problem. Proposed table structure is clearly violating Normalization principle for relation dbs. If combination of ID/ID2 in table tA has many relate record in tB then you must have surrogate primary key in tA, which will be referenced from tB (and hence tB will not have Id, ID2 column at all).

Once your table is normalized as I said earlier, nhibernate mapping is as simple as Many-to-One on EntityA or One-To-Many set mapping on EntityB (depending upon direction of relationship between those two entity in domain). After all it's domain not db structure who drives it.