Populating Entities From Stored Procedures With NHibernate

A short while ago we needed to fetch the data for some entities through a stored procedure for performance reasons. We already use NHibernate in the typical way to fetch and modify the data of this entity type, but we just wanted something so we could also use the resultset of the stored procedure to populate the entities. One of my team members spent some time figuring out how to get the data returned by the stored procedure into the entities without actually having to write the code ourselves. Turns out this was pretty easy to do. Let's go over the solution with a very simple example.

The stored procedure i'll use for the example is extremely simple, and you'd never need to use this technique for such a stupid procedure. But in the situation we faced at work, the stored procedure was obviously a lot more complicated. So the stored procedure for this example is just this:

ALTER PROCEDURE [dbo].[GetProductsByCategoryId]
    @CategoryId int
AS
BEGIN
    SET NOCOUNT ON;
 
    SELECT [ProductID]
          ,[ProductName]
          ,[SupplierID]
          ,[CategoryID]
          ,[QuantityPerUnit]
          ,[UnitPrice]
          ,[UnitsInStock]
          ,[UnitsOnOrder]
          ,[ReorderLevel]
          ,[Discontinued]
      FROM [Northwind].[dbo].[Products]
     WHERE [CategoryId] = @CategoryId
END

This just returns the product rows for the given CategoryId parameter. Again, you'd never do this in real life but this simple procedure is just used as an example.

Now, the structure of the resultset that this procedure returns is identical to the structure that the Product entity is mapped to. This makes it really easy to get this data into the Product entities. Just add a named query to your mapping like this:

  <sql-query name="GetProductsByCategoryId">
    <return class="Product" />
    exec dbo.GetProductsByCategoryId :CategoryId
  </sql-query>

And this is all you need to do in code to get your list of entities from this stored procedure:

            IQuery query = Session.GetNamedQuery("GetProductsByCategoryId");
            query.SetInt32("CategoryId", 1);
            IList<Product> products = query.List<Product>();

Is that easy or what?

Now, suppose that the stored procedure returns more columns than you've got mapped to the entity. You can still use this approach as well, but then you'll need to specify which return values map to which properties in the entity like this:

  <sql-query name="GetProductsByCategoryId">
    <return class="Product">
      <return-property column="ProductID" name="Id" />
      <return-property column="ProductName" name="Name" />
      <return-property column="SupplierID" name="Supplier" />
      <return-property column="CategoryID" name="Category" />
      <return-property column="QuantityPerUnit" name="QuantityPerUnit" />
      <return-property column="UnitPrice" name="UnitPrice" />
      <return-property column="UnitsInStock" name="UnitsInStock" />
      <return-property column="UnitsOnOrder" name="UnitsOnOrder" />
      <return-property column="ReorderLevel" name="ReorderLevel" />
      <return-property column="Discontinued" name="Discontinued" />
    </return>
    exec dbo.GetProductsByCategoryId :CategoryId
  </sql-query>

Notice how the CategoryID and SupplierID columns are mapped to Category and Supplier properties, which in Product's mapping are mapped as Category and Supplier many-to-one types, so basically references of type Category and Supplier respectively. NHibernate basically just takes care of all of the dirty work.