NHibernate - How to map to a class that has no table (for custom sql queries)

Question

Update - Edited config for readability in SO

Hi,

I've been learning NHibernate for a day or two but getting stuck on one point.

I need to be able to execute custom stored procedures and use NHibernate to map them back to domain classes.

I have this working for the scenario where the custom query maps back to a object that maps to a database table, as shown by many a nhibernate example (See first section below).

However in the config for the second section below, the query pulls only 2 columns from the target table. For this reason, I have created a custom object so that NHibernate has something to map the return values to. The custom object properties have the same name as the return columns from the custom procedure.

When I run my tests I get an exception like:

NHibernate.MappingException: No persister for: Proj.DataEntityTracker.Domain.Entities.CustomObject

So I guess the mapping under sql-query section is not enough for NHibernate to map the return values to the object properties.

So my question is - how do I set up a mapping for which there is no equivalent table in the database such that I can map the results of a stored procedure to that object?

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="Proj.DataEntityTracker.Domain"
                   namespace="Proj.DataEntityTracker.Domain.Entities">

  <class name="TrackedEntityProperty" table="TrackedEntityProperties">
    <id name="ID" type="Int32" unsaved-value="0">
      <generator class="native"></generator>
    </id>
    <property name="TrackedEntityID" />
    <property name="Name" />
    <property name="CreatedDate" />
    <property name="ChangedDate" />
    <property name="DataType" />
    <property name="CurrentValue" />
    <property name="RequestPropertyValueQuestion" />
    <property name="NullResponseIsAcceptable" />
    <property name="Duplication" />
    <property name="Frequency" />
    <property name="IsActive" />
    <property name="IsDeleted" />
    <property name="LastUpdateTaskGenerated" />
    <property name="LastUpdateTaskCompleted" />
    <property name="LastUpdateTaskCancelled" />
  </class>

  <sql-query name="usp_GetTrackedEntityPropertiesDueForUpdate" >
    <return alias="usp_GetTrackedEntityPropertiesDueForUpdate" class="TrackedEntityProperty">

      <return-property name="ID" column="ID" />
      <return-property name="TrackedEntityID" column="TrackedEntityID" />
      <return-property name="Name" column="Name" />
      <return-property name="CreatedDate" column="CreatedDate" />
      <return-property name="ChangedDate" column="ChangedDate" />
      <return-property name="DataType" column="DataType" />
      <return-property name="CurrentValue" column="CurrentValue" />
      <return-property name="RequestPropertyValueQuestion" column="RequestPropertyValueQuestion" />
      <return-property name="NullResponseIsAcceptable" column="NullResponseIsAcceptable" />
      <return-property name="Duplication" column="Duplication" />
      <return-property name="Frequency" column="Frequency" />
      <return-property name="IsActive" column="IsActive" />
      <return-property name="IsDeleted" column="IsDeleted" />
      <return-property name="LastUpdateTaskGenerated" column="LastUpdateTaskGenerated" />
      <return-property name="LastUpdateTaskCompleted" column="LastUpdateTaskCompleted" />
      <return-property name="LastUpdateTaskCancelled" column="LastUpdateTaskCancelled" />

    </return>

    exec usp_GetTrackedEntityPropertiesDueForUpdate :TrackedEntityID

  </sql-query>

  <sql-query name="usp_SomeCustomSproc">
    <return alias="usp_SomeCustomSproc" class="CustomObject">

      <return-property name="ID" column="ID" />
      <return-property name="Name" column="Name" />

    </return>

    exec usp_SomeCustomSproc :TrackedEntityID

  </sql-query>

</hibernate-mapping>

Answer 1

What you're looking for are projections. First of all you need to modify your query

  <sql-query name="usp_SomeCustomSproc">
    <return-scalar column="Id" type="Int32"/>
    <return-scalar column="Name" type="String"/>

    exec usp_SomeCustomSproc :TrackedEntityID

  </sql-query>

Then in the code where you call it you specify a result transformer. The AliasToBeanTransformer will take column aliases and map them to properties on the object.

session.GetNamedQuery("usp_SomeCustomSproc")
       .SetInt32("TrackedEntityID", 15)
       .SetResultTransformer(Transformers.AliasToBean<CustomObject>())
       .List<CustomObject>()

Answer 2

Problem solved, although it seems apparent that NHibernate doesn’t want you using stored procedures. I am posting this to help others with the same issue, as this information was not easy to come by!

Initial blog that helped with this was here, though this example mapped the result back to a standard object - table mapping (which may be what you want). I wanted to map the result back to a custom object that did not have a table representation in the database:

http://forums.asp.net/t/1407518.aspx/1

So, I created a domain class to hold the stored procedure result.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace MyProj.DataEntityTracker.Domain.Entities
{
    public class DemoCustomSprocObj
    {
        public virtual Guid Guid { get; set; }
        public virtual int ID { get; set; }
        public virtual string Name { get; set; }
    }
}

This class does not have to have a corresponding table in SQL server, although I found that a class definition does need to be created (with no table attribute) so as to avoid “NHibernate.MappingException: No persister for:” type errors.

Note also that the domain class that is created to hold the stored procedure result needs an ID field, and this must be returned from the database. In this case, I returned NEWID() from SQL Server and configured the mapping class to use the GUID generator for the ID field.

CREATE PROCEDURE usp_DemoCustomSproc
  @TrackedEntityID INT
AS
SET NOCOUNT ON
BEGIN
    SELECT NEWID() AS [Guid],
           ID ,
           Name 
    FROM TrackedEntityProperties AS tep
    WHERE TrackedEntityID = @TrackedEntityID
END

And the mapping class:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="MyProj.DataEntityTracker.Domain"
                   namespace="MyProj.DataEntityTracker.Domain.Entities">

  <!-- This mapping does not use a table, rather it exists to
  allow the mapping of a stored procedure result back to a domain object. 
  Note the use of the GUID. An ID must be present and returned by the stored 
  procedure result, otherwise the object will not work with NHibernate.
  Note also the absence of a table in the class tag. No table exists in
  the database, but the mapping must exist to avoid "No persiter" errors.

  Arguments are passed with the :Arg syntax.

  It seems that NHibernate was not designed for use with stored procedures,
  though it may be useful to be able to use them in some situations. This
  is a means of doing so.

  -->

  <class name="DemoCustomSprocObj">
    <id name="Guid" type="guid" unsaved-value="00000000-0000-0000-0000-000000000000">
      <generator class="guid"></generator>
    </id>
    <property name="ID" />
    <property name="Name" />
  </class>

  <sql-query name="usp_DemoCustomSproc">
    <return alias="usp_DemoCustomSproc" class="DemoCustomSprocObj">

      <return-property name="Guid" column="Guid" />
      <return-property name="ID" column="ID" />
      <return-property name="Name" column="Name" />

    </return>

    exec usp_DemoCustomSproc :TrackedEntityID

  </sql-query>

</hibernate-mapping>