NHibernate Forge
The official new home for the NHibernate for .NET community

Populating Entities With Associations From Stored Procedures With NHibernate

Note: this was originally posted on my own blog.

In response to my last post where i showed how you could fill entities with the resultset of a stored procedure, i was asked if it was also possible to fill entities and their associations if the stored procedure returned all of the necessary data. I looked into it, and it's possible, although it did take me some time to figure out how to actually do it.

First of all, here's the modified stored procedure:

ALTER PROCEDURE [dbo].[GetProductsByCategoryId]

    @CategoryId int

AS

BEGIN

    SET NOCOUNT ON;

 

    SELECT [Products].[ProductID] as "Product.ProductID"

          ,[Products].[ProductName] as "Product.ProductName"

          ,[Products].[SupplierID] as "Product.SupplierID"

          ,[Products].[CategoryID] as "Product.CategoryID"

          ,[Products].[QuantityPerUnit] as "Product.QuantityPerUnit"

          ,[Products].[UnitPrice] as "Product.UnitPrice"

          ,[Products].[UnitsInStock] as "Product.UnitsInStock"

          ,[Products].[UnitsOnOrder] as "Product.UnitsOnOrder"

          ,[Products].[ReorderLevel] as "Product.ReorderLevel"

          ,[Products].[Discontinued] as "Product.Discontinued"

          ,[Categories].[CategoryID] as "Category.CategoryID"

          ,[Categories].[CategoryName] as "Category.CategoryName"

          ,[Categories].[Description] as "Category.Description"

      FROM [Northwind].[dbo].[Products]

            inner join [Northwind].[dbo].[Categories]

                on [Products].[CategoryID] = [Categories].[CategoryID]

     WHERE [Products].[CategoryId] = @CategoryId

END

As you can see, this returns all of the columns of the Products table, as well as the columns of the Categories table. The goal is to let NHibernate execute this stored procedure, and use the returning data to give us a list of Product entities with a Category reference which is already set up with the proper data. The mapping of the named query now looks like this:

  <sql-query name="GetProductsByCategoryId">

    <return alias="Product" class="Product">

      <return-property column="Product.ProductID" name="Id" />

      <return-property column="Product.ProductName" name="Name" />

      <return-property column="Product.CategoryId" name="Category" />

      <return-property column="Product.SupplierID" name="Supplier" />

      <return-property column="Product.QuantityPerUnit" name="QuantityPerUnit" />

      <return-property column="Product.UnitPrice" name="UnitPrice" />

      <return-property column="Product.UnitsInStock" name="UnitsInStock" />

      <return-property column="Product.UnitsOnOrder" name="UnitsOnOrder" />

      <return-property column="Product.ReorderLevel" name="ReorderLevel" />

      <return-property column="Product.Discontinued" name="Discontinued" />

    </return>

    <return-join alias="Category" property="Product.Category">

      <return-property column="Category.CategoryId" name="Id" />

      <return-property column="Category.CategoryName" name="Name" />

      <return-property column="Category.Description" name="Description" />

    </return-join>

    exec dbo.GetProductsByCategoryId :CategoryId

  </sql-query>

We map each column of the Product table to its correct property of the Product class. Notice that we defined the 'Product' alias for this part of the data. Then we use the return-join element to map the joined properties to the 'Product.Category' property. This might look a bit weird at first. You have to specify the alias of the owning object (which in our case is the 'Product' alias), and then you need to specify the name of the property of the owning object upon which the other part of the data should be mapped (in our case, the 'Category' property of the 'Product' object). Now we can retrieve the data like this:

            IQuery query = Session.GetNamedQuery("GetProductsByCategoryId");

            query.SetInt32("CategoryId", 1);

            IList results = query.List();

I first tried to use the IQuery's generic List of T method which i had hoped would give me a generic list of Product entities. But i couldn't get that working. So i tried the regular List method, and it turns out that NHibernate doesn't just give me a list of Product entities... it gives me a list where each item in the list is an object array where the first item in the array is the Product entity, and the second item is the Category. Each Product entity's Category property references the correct Category instance though. So you can get the product instances like this:

            IEnumerable<Product> products = results.Cast<Object[]>().Select(i => (Product)i[0]);

There's probably an easier way to just get the list of Product entities from the named query, but i haven't found it yet :)


Posted nov 24 2008, 03:27 a.m. by Davy Brion

Comments

sougandhpavithran wrote re: Populating Entities With Associations From Stored Procedures With NHibernate
on 04-25-2010 0:47

Suppose we have a one-to-many relationship between class [product] and class [productcategory] defined using an IList.  And suppose, we have this relationship neatly mapped in our [product.hbm.xml] file. Now, will the products fetched using this stored procedure automatically hydrate the [productcategories] IList in product class also?

seeriuss wrote re: Populating Entities With Associations From Stored Procedures With NHibernate
on 05-17-2010 17:00

I've been trying this method, but always seem to get the error 'errors in named queries'.

Sonda de temperatura wrote Sonda de temperatura
on 10-13-2014 0:11

Populating Entities With Associations From Stored Procedures With NHibernate - NHibernate blog - NHibernate Forge

diet pills that Work fast wrote diet pills that Work fast
on 10-17-2014 16:27

Populating Entities With Associations From Stored Procedures With NHibernate - NHibernate blog - NHibernate Forge

Powered by Community Server (Commercial Edition), by Telligent Systems