Archive for November, 2008

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

As i had mentioned earlier, one of the NHibernate related posts i had on my TODO list was "Getting Up To Speed With NHibernate's Second Level Cache".

Luckily, Gabriel Schenker beat me to it and wrote a fantastic post about it. It is by far the best documentation on the 2nd level cache that you'll find for now, so be sure to check that out if you're looking into using some of NHibernate's more advanced caching features.

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.

Big weekend so far... Fabio Maulo invited me to join the NHibernate development team, which i gladly accepted

It's a great project and a great team as well. I'm very proud to be a part of that from now on. I'm so psyched about this that i don't even know what to say