NHibernate Stored procedure mapping properties on complex object

Question

I have a SQL stored procedure which Im trying to use to load a collection of entities. My entities look like this

public class Person
{ 
       public virtual int Id {get;set;}
       public virtual string Name {get;set;}
       public virtual Colour FavoriteColour {get;set;}
}

public class Colour
{
       public virtual int Id {get;set;}
       public virtual string Name {get;set;}
}

My stored procedure looks more or less like this:

Create Procedure getAllPersons
AS
     SELECT
            p.Id as PersonId,
            p.Name as PersonName,
            c.Id as ColourId,
            c.Name as ColourName
     FROM
            Person p JOIN Colour c
   p.ColourId = c.Id

My mapping looks like this

<class name="Person"><id name="Id">
  <generator class="native"/>
</id>
<property name="Name"/><many-to-one name="FavoriteColour"
  class="Foo, Colour"
  column="ColourId"/></class><sql-query name="getAllPersons">
    <return class="Person">
        <return-property name="Id" column="PersonId"/>
        <return-property name="Name" column="PersonName"/>
        <return-property name="FavoriteColour">
            <return-column name="ColourId"/>
        </return-property>
    </return>
    exec getAllPersons
</sql-query>

I want to know how I can map the colourName column that comes our of my proc to the FavoriteColour.Name property on my Person entity so that the FavoriteColour object on Person is populated with both its Id and Name properties. Any ideas?

Answer 1

Have you had read of these posts:

http://ayende.com/Blog/archive/2006/09/18/UsingNHibernateWithStoredProcedures.aspx http://ayende.com/Blog/archive/2006/10/04/ShouldYouUseNHibernateWithStoredProcedure.aspx