nHibernate - stored procedures and composite keys

Question

I'm trying to map the output of a stored procedure to an object in my project using nHibernate.

The object is delcared like this:

public class NewContentSearchResult
{
    public string Name { get; set; }
    public string ContentType { get; set; }
    public int Count { get; set; }
    public int CMIId { get; set; }
    public int FeatureId { get; set; }

    public override bool Equals(object obj)
    {
     return base.Equals(obj);
    }

    public override int GetHashCode()
    {
     return base.GetHashCode();
    }
}

and the mapping looks like this:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Class.Assembly"
    namespace="Class.Assembly"
    default-lazy="false" default-cascade="none">
  <class name="NewContentSearchResult" mutable="false" check="none">
    <composite-id unsaved-value="none">
      <key-property name="CMIId" type="int" />
      <key-property name="FeatureId" type="int" />
    </composite-id>
    <property name="ContentType" type="string" />
    <property name="Name" type="string" />
    <property name="Count" type="int" />
  </class>
  <sql-query name="spWebGetNewContentBySalesRole">
    <return class="NewContentSearchResult" lock-mode="read">
      <return-property name="Name" column="Name" />
      <return-property name="ContentType" column="FeatureDesc" />
      <return-property name="Count" column="Number" />
      <return-property name="CMIId" column="CMIId" />
      <return-property name="FeatureId" column="FeatureId" />
    </return>
    exec core.spWebGetNewContentBySalesRole :SalesRoleId
  </sql-query>
</hibernate-mapping>

The stored proc call is correct, and I get back results that look like this for SalesRoleId 266 (as an example):

CMIId       FeatureDesc       FeatureId     Name       Count
4000719 Guest Book       12    Charlie Brown 2
4000719 Audio Guest Book 3    Charlie Brown 1

Without the composite key (using just the CMIId) it works just fine, except when there are 2 results (as above) that share a CMIId...the 2nd is overwritten by the first.

I have to use a composite key, and CMIId/FeatureId is the logical combination.

When I run this now, I get an exception:

NHibernate.ADOException: could not execute query
[ exec core.spWebGetNewContentBySalesRole ? ]
  Name: SalesRoleId - Value: 266
[SQL: exec core.spWebGetNewContentBySalesRole ?] --->  System.IndexOutOfRangeException: CMIId22_0_.

Answer 1

In the past when I've used a composite-key I've always had to create a separate class for that ID with the properties of the class matching the separate keys for the composite. Have you tried this?

Answer 2

Ok, further research has convinced me that it's not possible to have composite ids in nHibernate when you use a stored procedure.

To that end, I modified my SQL to include rownumber() and I'm using that as an id. I can only do this because it's read-only, with no writing to the db, but it works for my purpose.

Answer 3

A bit late in the day, but I can second Mark Struzinski - this is possible.

I ran into this problem recently and solved it by using a separate class for the composite key and using elements within the mapping as follows.

<sql-query name="" >
    <return alias="" class="">
        <return-property name="MyId">
            <return-column name="NameInResultSet1" />
            <return-column name="NameInResultSet2" />
            <return-column name="NameInResultSet3" />
            <return-column name="NameInResultSet4" />
        </return-property>
        <return-property name="OtherProperty" column="OtherProperty" />
    </return>
    exec MyStoredProc :parameter
</sql-query>

Note that the "name" attributes inside the return-column elements represent the names in the result set, and should be in the same order as the properties in the id class.