I have a one-to-many object that I need to load using a stored procedure.

Can you use a Named SQL stored procedure that returns two result sets to populate the parent and its child collection?

My collection is a List and I have the parent loading fine. The problem is I can not find an example of how to map the loading of the collection using a stored procedure.

Is that because it is not possible?

Thanks,

Cary

I don't believe you can map a collection to a stored procedure. The mappings need to be able to have actual column or classes (which are then also mapped to columns) to be useful in that sense. otherwise you're only able to read from the collection, and it could never be populated to begin with.

It sounds like, what you want, is to map the collection to a physical class (which is then mapped to a table/columns).

Then you can create a new collection of that type and use a Stored Procedure to query t as described on chapter 13.2.2 of the NHibernate Reference pdf.

Again, I don't think you're going to be able to map directly to a stored procedure because of the way NHibernate expects to interact with the objects.

After several DAYS of working with this. I got it.

Instead of having a stored procedure return multiple result sets have it return one that has both parent and children records denormalized.

Couple Notes:
- This was done to populate a Read Only Object.
-This was to an existing db and the Address table is a multi-part key of PersonKey and AddressSequence. It requires a composite key and an Index Column.

Example(not the actual objects):

You can call Session.GetNamedQuery("Load_PersonAndAddresses_by_Id").

Use an hbm.xml like the following and nhibernate will return you a list of Parent objects with the children objects populated:

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
<class name="Person" table="Per">
<id name="Id" column="PersonKey" type="Int32">
<generator class="assigned" />
</id>
<property name="Name" column="Person_Name" type="String"/>
<list name="Addresses" lazy="true">
<key>
<column name="PersonKey"/>
</key>
<index column="Composite_Id" type="Int32"/>
<one-to-many class="Address"/>
</list>
</class>
<sql-query name="Load_PersonAndAddresses_by_Id">
<return alias="Per" class="Person"/>
<load-collection alias="Address" role="Person.Addresses"/>
exec usp_Person-AddressesList_LoadById ?
</sql-query>
</hibernate-mapping>

The difficult part is the nHibernate documentation does not explain what elements need to match up to make this work.