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 Sun, 23 November 2008 04:27:00 PM by DavyBrion

Filed under: