title: Not sure how to join tables using Fluent NHibernate
body: <p>I'm using NHibernate on legacy tables and after looking through our codebase, I seem to be the only person with this need. I need to join two tables so I can run a query, but I haven't made any progress today. I'll try to abbreviate where it makes sense in my code snippets. Care to help?</p>
  
  <p><strong>Tables--</strong></p>
  
  <pre><code>Order
   OrderID (primary key)
   OrderName
   OrderType
   OrderLocation
  
  OrderAppendix
   ID (composite key)
   Key (composite key)
   Value (composite key)
  </code></pre>
  
  <p>The ID portion of the OrderAppendix composite key is associated with OrderID in the Order table; therefore, and Order can have several entries in the OrderAppendix table. </p>
  
  <p><strong>Domain Objects--</strong></p>
  
  <pre><code>[Serializable]
  public class Order
  {
       public virtual string OrderID { get; set; }
       ...
       public virtual string OrderLocation { get; set; }
  }
  
  [Serializable]
  public class OrderAppendix
  {
       public virtual string ID { get; set; }
       public virtual string Key { get; set; }
       public virtual string Value { get; set; }
  
       public override bool Equals(object obj)
       {
            ...
       }
  
       public override int GetHashCode()
       {
            ...
       }
  }
  </code></pre>
  
  <p><strong>Mapping</strong></p>
  
  <pre><code>internal sealed class OrderMap : ClassMap&lt;Order&gt;
  {
       Table("Order");
       Id(x =&gt; x.OrderID).Column("OrderID").Length(20).GeneratedBy.Assigned();
       Map( x =&gt; x.OrderName).Column("OrderName")
       ....
  }
  
  internal sealed class OrderAppendixMap : ClassMap&lt;OrderAppendix&gt;
  {
       Table("OrderAppendix");
       CompositeId()
           .KeyProperty(x =&gt; x.ID, "ID")
           ....
       Map( x =&gt; x.ID).Column("ID);
       ...
  }
  </code></pre>
  
  <p>I won't muddy this up with my futile attempts at joining these tables, but what I would like to do is query by things like OrderType or OrderLocation given that the results all have the same Value from the OrderAppendix table. </p>
  
  <p><strong>Example desired SQL</strong></p>
  
  <pre><code>SELECT * FROM ORDER
  INNER JOIN
   ON Order.OrderID = OrderAppendix.ID
  WHERE OrderAppendix.Key = "Stuff"
  </code></pre>
  
  <p><strong>Edit</strong></p>
  
  <p>Here's where I've gotten by reading the documentation on "QueryOver"</p>
  
  <p><a href="http://nhibernate.info/doc/nh/en/index.html#queryqueryover" rel="nofollow">http://nhibernate.info/doc/nh/en/index.html#queryqueryover</a></p>
  
  <pre><code>Order Order = null;
  OrderAppendix OrderAppendix = null;
  
  resultList = session.QueryOver&lt;Order&gt;(() =&gt; Order)
       .JoinAlias(() =&gt; Order.OrderAppendix, () =&gt; OrderAppendix)
       .Where(() =&gt; OrderAppendix.Key == "MatchThis")
       .List();
  </code></pre>
  
  <p>I think I'm on the right track using aliases to join the table, but obviously I haven't found a way to inform NHibernate of the many-to-one mapping that's needed. Also, you can see that I've added a property of type OrderAppendix to Order in order to the use the alias functionality. </p>