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.

Not that I'm aware. Without building the SQL itself, Hibernate won't know how to map the result back to instances. Assuming that you don't know how deep to go — with projections you might be able to rebuild some, but without knowing how many levels down everything goes, or without having them all have the exact same depth, it wouldn't work. If it's a stored procedure, why use Hibernate at all? You could skip it and save yourself the trouble. If you need to make changes to any categories, use Merge on the changed instances to push them back into Hibernate's session one-by-one. — Jon Adams, Aug 21 '12 at 20:18
A bit similar to Jon's comment, but with a different approach: why user stored procedures at all for this? What value do they add? — Diego Mijelshon, Aug 21 '12 at 21:23
I want to use a stored procedure because I need to filter the results, both root and childrens, and I didn't found a way to do that just using Criteria. — Guillermo Gutiérrez, Aug 31 '12 at 15:57

score 3 · Accepted Answer · edited Jan 08 '16 at 09:24

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>

I'm still not able to do the load of the whole tree, but it's useful for loading master-detail data. Thank you. — Guillermo Gutiérrez, Sep 12 '12 at 15:11

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

1 Answers1