title: Fluent nHibernate Mapping with Ternary Mapping Table
body: <p>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.</p>
  
  <p>There are multiple parent tables that use a mapping table to store notes.</p>
  
  <p>The parent tables look like the following:<br>
  <strong>P1 Table</strong><br>
  ID iSomething</p>
  
  <p><strong>P2 Table</strong><br>
  ID iSomethingElse</p>
  
  <p>There is a mapping table that will take a parent table and map it to a note table.<br>
  <strong>Mapping Table</strong><br>
  ID i_RecordUniqueID<br>
  ID i_NoteID<br>
  ID i_RecordID<br></p>
  
  <p>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.</p>
  
  <p>Here is the note table:<br>
  <strong>Note Table</strong><br>
  ID i_NoteID</p>
  
  <p>The query to find table P1's notes is as follows:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The query to find table P2's notes is as follows:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>In my Parent tables mapping file, I have an association like the below.  I don't know how to add the i_RecordID constraint.</p>
  
  <pre><code>HasManyToMany&lt;Note&gt;(x =&gt; x.Notes)
    .Table("Mapping")
    .ParentKeyColumn("i_RecordUniqueID")
    .ChildKeyColumn("i_NoteID")
    .Cascade.All();
  </code></pre>