Fluent nHibernate Mapping with Ternary Mapping Table

Question

Have a legacy database with mapping table structures like the following. I am trying to figure out how to fluently map this type of relationship.

There are multiple parent tables that use a mapping table to store notes.

The parent tables look like the following:
P1 Table
ID iSomething

P2 Table
ID iSomethingElse

There is a mapping table that will take a parent table and map it to a note table.
Mapping Table
ID i_RecordUniqueID
ID i_NoteID
ID i_RecordID

The column i_RecordID contains a numeric value indicating which parent table the i_RecordUniqueID value came from. The mapping table only has those three columns and is a ternary primary key.

Here is the note table:
Note Table
ID i_NoteID

The query to find table P1's notes is as follows:

Select n.*
from P1 p
inner join Mapping m on p.iSomething = m.i_RecordUniqueID and m.i_RecordID = 1
inner join Note n on m.i_NoteID = n.i_NoteID

The query to find table P2's notes is as follows:

Select n.*
from P2 p
inner join Mapping m on p.iSomething = m.i_RecordUniqueID and m.i_RecordID = 2
inner join Note n on m.i_NoteID = n.i_NoteID

In my Parent tables mapping file, I have an association like the below. I don't know how to add the i_RecordID constraint.

HasManyToMany<Note>(x => x.Notes)
  .Table("Mapping")
  .ParentKeyColumn("i_RecordUniqueID")
  .ChildKeyColumn("i_NoteID")
  .Cascade.All();

score 0 · Answer 1 · answered Apr 18 '12 at 06:54

FluentNHibernatew does not yet support ManyToAny mapping. you could map it for readonly access

// P1Map()
HasManyToMany(x => x.Notes)
  .Table("Mapping")
  .ParentKeyColumn("i_RecordUniqueID")
  .Where("i_RecordID == 1")
  .ChildKeyColumn("i_NoteID")
  .Cascade.All();

// P2Map()
HasManyToMany(x => x.Notes)
  .Table("Mapping")
  .ParentKeyColumn("i_RecordUniqueID")
  .Where("i_RecordID == 2")
  .ChildKeyColumn("i_NoteID")
  .Cascade.All();

or you have to create a component

ICollection<TableToNote> Notes;

public TableToNoteMap()
{
    ReferencesAny(x => x.Parent).IdentityColumn("i_RecordUniqueID").MetaTypeColumn("i_RecordID")...;

    References(x => x.Note);
}

Fluent nHibernate Mapping with Ternary Mapping Table

1 Answers1