Stored Procedure in NHibernate Part 3 - Populate Two Related Entities Objects

Now we will see how to use stored procedure in NHibernate to return two entities at the same time. We will continue our work from the previous post (second post of this series). You can also use the works from the first post of this series, however you need to ignore the Component part from the examples below. I choose to continue from the latter post to show that in NHibernate, we can use stored procedures to populate many different types' objects at one time.

First we modify our existing stored procedure to return data to populate objects of the new entity as well:

ALTER 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.*
   , A.*
   FROM Users U
   INNER JOIN Roles R ON U.RoleId=R.RoleId
   LEFT JOIN Attributes A ON A.UserId=U.UserId
   -- other joins and conditions
END

Then we create the new entity class:

public class UserAttribute
{
    public int AttributeId { get; set; }
    public User User { get; set; }  // link to the main entity
    public string DisplayName { get; set; }
    public string Code { get; set; }           
}

And its .hbm.xml mapping file:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping assembly="MyAssemblyName" namespace="MyNamespace" xmlns="urn:nhibernate-mapping-MyNHibernateVersionNumber">
  <class name="UserAttribute" lazy="false">
    <id name="AttributeId">
      <generator class="identity" />
    </id>
    <many-to-one name="User" column="UserId" not-null="true"/>  <!-- specify relationship to the main entity -->
    <property name="DisplayName" />
    <property name="Code" />
  </class>
</hibernate-mapping>

We also link the new entity from our main entity:

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; }
    public IList<UserAttribute> Attributes { get; set; } // link to the new entity
}

Also modify the main entity's mapping file to specify the relationship with the new entity. In this case we use bag collection mapping:

<?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>
    <bag name="Attributes">   <!-- specify relationship to the new entity -->
      <key column="UserId" />
      <one-to-many class="UserAttribute" />
    </bag>
  </class>
</hibernate-mapping>

Finally we modify the named query's mapping; add an alias attribute to the existing <return> element then add a new <return-join> element with its alias and property attributes in order to populate the new objects:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping assembly="MyAssemblyName" namespace="MyNamespace" xmlns="urn:nhibernate-mapping-MyNHibernateVersionNumber">
  <sql-query name="GetRelatedUsers"> 
    <return alias="U" class="User"></return>  <!-- alias is from the one used in the select query -->
    <return-join alias="A" property="U.Attributes"></return-join>  <!-- alias is from the one used in the select query -->
    exec spGetRelatedUsers :UserName, :NumberOfRecords  
  </sql-query>
</hibernate-mapping>

The codes to call the named query remain the same.