title: Fluent NHibernate: mapping complex many-to-many (with additional columns) and setting fetch
body: <p>I need a Fluent NHibernate mapping that will fulfill the following (if nothing else, I'll also take the appropriate NHibernate XML mapping and reverse engineer it).</p>
  
  <hr>
  
  <p><strong>DETAILS</strong></p>
  
  <p>I have a many-to-many relationship between two entities: <code>Parent</code> and <code>Child</code>. That is accomplished by an additional table to store the identities of the Parent and Child. However, I also need to define two additional columns on that mapping that provide more information about the relationship.</p>
  
  <p>This is roughly how I've defined my types, at least the relevant parts (where <code>Entity</code> is some base type that provides an <code>Id</code> property and checks for equivalence based on that Id):</p>
  
  <pre><code>public class Parent : Entity
  {
      public virtual IList&lt;ParentChildRelationship&gt; Children { get; protected set; }
  
      public virtual void AddChildRelationship(Child child, int customerId)
      {
         var relationship = new ParentChildRelationship
                          {
                             CustomerId = customerId,
                             Parent = this,
                             Child = child
                          };
         if (Children == null) Children = new List&lt;ParentChildRelationship&gt;();
         if (Children.Contains(relationship)) return;
         relationship.Sequence = Children.Count;
         Children.Add(relationship);
      }
  }
  
  public class Child : Entity
  {
      // child doesn't care about its relationships
  }
  
  public class ParentChildRelationship
  {
      public int CustomerId { get; set; }
      public Parent Parent { get; set; }
      public Child Child { get; set; }
      public int Sequence { get; set; }
  
      public override bool Equals(object obj)
      {
         if (ReferenceEquals(null, obj)) return false;
         if (ReferenceEquals(this, obj)) return true;
         var other = obj as ParentChildRelationship;
         if (return other == null) return false;
  
         return (CustomerId == other.CustomerId
             &amp;&amp; Parent == other.Parent
             &amp;&amp; Child == other.Child);
      }
  
      public override int GetHashCode()
      {
         unchecked
         {
             int result = CustomerId;
             result = Parent == null ? 0 : (result*397) ^ Parent.GetHashCode();
             result = Child == null ? 0 : (result*397) ^ Child.GetHashCode();
             return result;
         }
      }
  }
  </code></pre>
  
  <p>The tables in the database look approximately like (assume primary/foreign keys and forgive syntax):</p>
  
  <pre><code>create table Parent (
     id int identity(1,1) not null
  )
  
  create table Child (
     id int identity(1,1) not null
  )
  
  create table ParentChildRelationship (
     customerId int not null,
     parent_id int not null,
     child_id int not null,
     sequence int not null
  )
  </code></pre>
  
  <p>I'm OK with Parent.Children being a lazy loaded property. However, the ParentChildRelationship should eager load ParentChildRelationship.Child. Furthermore, I want to use a Join when I eager load.</p>
  
  <p>The SQL, when accessing Parent.Children, NHibernate should generate an equivalent query to:</p>
  
  <p><code>SELECT * FROM ParentChildRelationship rel LEFT OUTER JOIN Child ch ON rel.child_id = ch.id WHERE parent_id = ?</code></p>
  
  <p>OK, so to do that I have mappings that look like this:</p>
  
  <pre><code>ParentMap : ClassMap&lt;Parent&gt;
  {
     public ParentMap()
     {
        Table("Parent");
        Id(c =&gt; c.Id).GeneratedBy.Identity();
        HasMany(c =&gt; c.Children).KeyColumn("parent_id");
      }
  }
  
  ChildMap : ClassMap&lt;Child&gt;
  {
     public ChildMap()
     {
        Table("Child");
        Id(c =&gt; c.Id).GeneratedBy.Identity();
     }
  }
  
  
  ParentChildRelationshipMap : ClassMap&lt;ParentChildRelationship&gt;
  {
     public ParentChildRelationshipMap()
     {
        Table("ParentChildRelationship");
        CompositeId()
                  .KeyProperty(c =&gt; c.CustomerId, "customerId")
                  .KeyReference(c =&gt; c.Parent, "parent_id")
                  .KeyReference(c =&gt; c.Child, "child_id");
        Map(c =&gt; c.Sequence).Not.Nullable();
      }
  }
  </code></pre>
  
  <p>So, in my test if i try to get <code>myParentRepo.Get(1).Children</code>, it does in fact get me all the relationships and, as I access them from the relationship, the Child objects (for example, I can grab them all by doing <code>parent.Children.Select(r =&gt; r.Child).ToList()</code>).</p>
  
  <p>However, the SQL that NHibernate is generating is inefficient. When I access parent.Children, NHIbernate does a <code>SELECT * FROM ParentChildRelationship WHERE parent_id = 1</code> and then a <code>SELECT * FROM Child WHERE id = ?</code> for each child in each relationship. I understand why NHibernate is doing this, but I can't figure out how to set up the mapping to make NHibernate query the way I mentioned above.</p>