title: Load collection with custom SQL in NHibernate
body: <p>I'm pretty new to NHibernate. I understood the main concepts, but now I'm a bit stuck : I need to load a collection property using a slightly complicated SQL query. 
  Let me explain:
  I'm writing a code generator that generates classes from Oracle tables (using oracle TAB and COL views).
  So basically, I have two classes, Table and Column, described like this:</p>
  
  <pre><code>public class Table
  {
      public virtual String Name { get; set; }
      public virtual ISet&lt;Column&gt; Columns { get; set; }
      public virtual ISet&lt;Column&gt; PrimaryKeys { get; set; } 
  }
  
  public class Column
  {
      public virtual Table Table { get; set; }
      public virtual String TableName { get; set; }
      public virtual String Name { get; set; }
      public virtual String Type{ get; set; }
  
      public override bool Equals(object obj)
      {
          Column c = obj as Column;
          if (obj == null)
              return false;
  
          return c.Table == Table &amp;&amp; c.Name == Name;
      }
  
      public override int GetHashCode()
      {
          return (this.TableName.GetHashCode() + Name.GetHashCode()).GetHashCode();
      }
  }
  </code></pre>
  
  <p>The mapping xml files are straightforward:</p>
  
  <p>Table.hbm.xml :</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8" ?&gt;
  &lt;hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="ModelGenerator" namespace="ModelGenerator.Models"&gt;
    &lt;class name="Table" table="TAB"&gt;
  
      &lt;id name="Name" column="TNAME"&gt;&lt;/id&gt;
  
      &lt;set name="Columns" order-by="COLNO"&gt;
        &lt;key&gt;
          &lt;column name="TNAME"&gt;&lt;/column&gt;
        &lt;/key&gt;
        &lt;one-to-many class="Column"&gt;&lt;/one-to-many&gt;
      &lt;/set&gt;
  
    &lt;/class&gt;
  
  &lt;/hibernate-mapping&gt;
  </code></pre>
  
  <p>Column.hbm.xml :</p>
  
  <pre><code>&lt;?xml version="1.0" encoding="utf-8" ?&gt;
  &lt;hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="ModelGenerator" namespace="ModelGenerator.Models"&gt;
    &lt;class name="Column" table="COL"&gt;
  
      &lt;composite-id&gt;
        &lt;key-property name="TableName" column="TNAME"&gt;&lt;/key-property&gt;
        &lt;key-property name="Name" column="CNAME"&gt;&lt;/key-property&gt;
      &lt;/composite-id&gt;
  
      &lt;property name="Type" column="COLTYPE"&gt;&lt;/property&gt;
  
      &lt;many-to-one name="Table"&gt;
        &lt;column name="TNAME"&gt;&lt;/column&gt;
      &lt;/many-to-one&gt;
  
    &lt;/class&gt;
  
  &lt;/hibernate-mapping&gt;
  </code></pre>
  
  <p>Now I'd like to add in the class Table a collection that contains the primary keys.
  I can get the primary key columns with the following query :</p>
  
  <pre><code>SELECT col.tname, col.cname, col.coltype
  FROM all_constraints cons, all_cons_columns cons_cols, col
  WHERE cons_cols.table_name = :MY_TABLE
  and   cons_cols.OWNER = :MY_ORACLE_USER
  AND cons.constraint_type = 'P'
  AND cons.constraint_name = cons_cols.constraint_name
  AND cons.owner = cons_cols.owner
  and col.tname = cons_cols.table_name
  and col.cname = cons_cols.column_name
  ORDER BY cons_cols.table_name, cons_cols.position
  </code></pre>
  
  <p>The results of this query could be mapped with my Column class.
  Ideally, my Table class would be now :</p>
  
  <pre><code>public class Table
  {
      public virtual String Name { get; set; }
      public virtual ISet&lt;Column&gt; Columns { get; set; }
      public virtual ISet&lt;Column&gt; PrimaryKeys { get; set; } 
  }
  </code></pre>
  
  <p>My problem is that I can't see how to declare this SQL query in my Table.hbm.xml file.
  I saw the &lt;sql-query&gt; and &lt;load-collection&gt; elements in the documentation but I can't make it work as I want...</p>
  
  <p>Does anyone know how to achieve this?</p>
  
  <p>Many thanks,</p>
  
  <p>Nico</p>