Stored Procedure in NHibernate Part 2 - Using Component Mapping

This time we will learn how to use stored procedure in NHibernate with Component mapping to extend an entity to contain another type in one of its properties. A Component is an object that is persisted as a value type. Usually it is used to represent objects that are part of an entity.

We will continue the work that we have done on the previous post. We will modify our main entity to have its related one-to-one type as a property. Then we will modify our stored procedure to populate these two objects together.

Firstly, we prepare a new class for the related one-to-one type that will be linked from our main entity:

public class UserRole
{
    public long RoleId { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public bool Active { get; set; }
}

Then we modify our main entity (i.e. User class) to contain the new type as a property:

public class User
{
    public long UserId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime? DOB { get; set; }
    public UserRole Role { get; set; }
}

We also need to change the stored procedure to return the new type as well in addition of the main type:

CREATE PROCEDURE [sec].[spGetRelatedUsers]
 @UserName nvarchar(256),
 @NumberOfRecords int
AS
BEGIN
   -- some processing before the final select
   -- this can include complex processing using temporary table(s), CTE(s), etc...

   SELECT DISTINCT TOP (@NumberOfRecords) U.*
   , R.*
   FROM Users U
   INNER JOIN Roles R ON U.RoleId=R.RoleId
   -- other joins and conditions
END

Finally we change the main entity mapping file to include the new type by using Component:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping assembly="MyAssemblyName" namespace="MyNamespace" xmlns="urn:nhibernate-mapping-2.2">
  <class name="User" lazy="false">
    <id name="UserId">
      <generator class="identity" />
    </id>
    <property name="FirstName" />
    <property name="LastName" />
    <property name="DOB" column="DateOfBirth" />
    <component name="Role" class="UserRole">
      <property name="RoleId">
        <column name="RoleId" />
      </property>
      <property name="Name" />
      <property name="Description" />
      <property name="Active" />
    </component>
  </class>
</hibernate-mapping>

On the next post we will see how to use stored procedure to populate two entities' objects (an entity with its related one-to-many entity).