Load collection with custom SQL in NHibernate

Question

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:

public class Table
{
    public virtual String Name { get; set; }
    public virtual ISet<Column> Columns { get; set; }
    public virtual ISet<Column> 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 && c.Name == Name;
    }

    public override int GetHashCode()
    {
        return (this.TableName.GetHashCode() + Name.GetHashCode()).GetHashCode();
    }
}

The mapping xml files are straightforward:

Table.hbm.xml :

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="ModelGenerator" namespace="ModelGenerator.Models">
  <class name="Table" table="TAB">

    <id name="Name" column="TNAME"></id>

    <set name="Columns" order-by="COLNO">
      <key>
        <column name="TNAME"></column>
      </key>
      <one-to-many class="Column"></one-to-many>
    </set>

  </class>

</hibernate-mapping>

Column.hbm.xml :

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="ModelGenerator" namespace="ModelGenerator.Models">
  <class name="Column" table="COL">

    <composite-id>
      <key-property name="TableName" column="TNAME"></key-property>
      <key-property name="Name" column="CNAME"></key-property>
    </composite-id>

    <property name="Type" column="COLTYPE"></property>

    <many-to-one name="Table">
      <column name="TNAME"></column>
    </many-to-one>

  </class>

</hibernate-mapping>

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 :

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

The results of this query could be mapped with my Column class. Ideally, my Table class would be now :

public class Table
{
    public virtual String Name { get; set; }
    public virtual ISet<Column> Columns { get; set; }
    public virtual ISet<Column> PrimaryKeys { get; set; } 
}

My problem is that I can't see how to declare this SQL query in my Table.hbm.xml file. I saw the <sql-query> and <load-collection> elements in the documentation but I can't make it work as I want...

Does anyone know how to achieve this?

Many thanks,

Nico

Nico Nico · Accepted Answer · 2015-02-24T08:27:13

Well, finally I found my problem :)

It was mainly caused by the <sql-query> element that I put inside the <class> element. NHibernate was unable to find it there.

So I kept the Table.cs as is, and now my Table.hbm.xml looks is the following:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="ModelGenerator" namespace="ModelGenerator.Models">
  <class name="Table" table="TAB">

    <id name="Name" column="TNAME"></id>

    <set name="Columns" order-by="COLNO">
      <key>
        <column name="TNAME"></column>
      </key>
      <one-to-many class="Column"></one-to-many>
    </set>

    <set name="PrimaryKeys">
      <key>
        <column name="TNAME"></column>
      </key>
      <one-to-many class="Column"/>
      <loader query-ref="primarykeys"/>
    </set>

  </class>

  <sql-query name="primarykeys">

    <load-collection alias="col" role="Table.PrimaryKeys"/>

    SELECT col.tname, col.colno, col.cname, col.coltype, col.width, col.scale, col.precision, col.nulls, col.defaultval, col.character_set_name
      FROM all_constraints cons, all_cons_columns cons_cols, col
      WHERE cons_cols.table_name = ?
      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
  </sql-query>

</hibernate-mapping>

It works! I hope it can help anyone.

Load collection with custom SQL in NHibernate

1 Answers