How to Populate the Child Collections / Associations of an Entity from a Stored Procedure using NHibernate

Question

I want to know if there is a way to populate recursive entities ( child collections / associations ) from a stored procedure in NHibernate. Suppose I have the next class:

public class Category
{
  public int category_id { set; protected get; }
  public string category_description { set; get; }
  public IEnumerable<Category> SubCategories { set; get; }

  ....
}

Is there a way to obtain a list of root categories from a stored procedure that obtains a groups of categories and their whole trees of children, with each category having his respective children inside the SubCategories property? I want to get the same result as the solution proposed in "Eagerly load recursive relation", but getting the result from a stored procedure instead of a table.

Answer 1

This is possible with named queries. Take a look at the official NHibernate documentation on named queries here: http://nhibernate.info/doc/nh/en/index.html#querysql-namedqueries

What you want to do is use the feature of named queries which allows you to join associations: <return-join />

There is also an example on the NHibernate blog here regarding this same topic: http://nhibernate.info/blogs/nhibernate/archive/2008/11/24/populating-entities-with-associations-from-stored-procedures-with-nhibernate.aspx

Convoluted example:

<sql-query name="GetCategory">
    <return alias="Category" class="Category"/>
    <return-join alias="Subcategory" property="Category.SubCategories">
        <return-property column="Subcategory.SubcategoryId" name="Id" />
    </return-join>
    EXEC someStoredProcedureName :CategoryId
</sql-query>