CODEWITHSUNDEEP
nhibernatestored-procedurescomposite-key
Jeff
Jeff

Reputation: 2871

nHibernate - stored procedures and composite keys

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_.

Upvotes: 2

Views: 2651

Answers (4)

Joe Field
Joe Field

Reputation: 416

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="spWebGetNewContentBySalesRole" >
    <return class="NewContentSearchResult">
        <return-property name="NewContentSearchResultKey">
            <return-column name="CMIIdColumn" />
            <return-column name="FeatureIdColumn" />
        </return-property>
        <return-property name="Name" column="Name" />       
        <return-property name="ContentType" column="FeatureDesc" />       
        <return-property name="Count" column="Number" />       
    </return>
    exec core.spWebGetNewContentBySalesRole :SalesRoleId 
</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.

Upvotes: 1

Alex
Alex

Reputation: 9429

As mentioned in the accepted answer, I ended up using a unique row number hack in Oracle by using the ROWNUM function. Later, I ran into a problem when reusing the query with different parameters. Seems that nHibernate remembered the ids and wanted to associate them with newly returned results, thus bringing back incorrect rows. I fixed this by combining the row number with a timestamp like this: 

(ROWNUM * 10000000000 + dbms_utility.get_time) AS ROW_ID

This way you're guaranteed to get completely new ids for your query results every time. Just make sure that you use a large enough integer to hold the value in your mapped object. I use a ulong in c#.net which is 8 bytes. You can also take a few 0s out of the multiplier to make the ID shorter.

Upvotes: 2

Jeff
Jeff

Reputation: 2871

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.

Upvotes: 5

Mark Struzinski
Mark Struzinski

Reputation: 33471

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?

Upvotes: 2

Related Questions