NHibernate - Populating Collection with Stored Proc

Question

I've got an entity Transaction that I'm trying to retrieve a list of with a stored procedure in NHibernate. I also want to eagerly load a one-to-many relationship (entity name TransactionCategorySplit, property name on Transaction is Splits). My sql-query definition looks something like this:

<sql-query name="GetTransactionsForDownload">
    <query-param name="AccountIdentifier" type="Guid"/>
    <query-param name="StartDate" type="DateTime"/>
    <query-param name="EndDate" type="DateTime"/>
    <return alias="Transaction" class="Transaction">
        <return-property column="Transaction.ID" name="ID" />
        <return-property column="Transaction.PostingDate" name="PostingDate" />
        <return-property column="Transaction.EffectiveDate" name="EffectiveDate" />
        <return-property column="Transaction.Amount" name="Amount" />
        <return-property column="Transaction.Balance" name="Balance" />
        ...etc
    </return>
    <return-join alias="TransactionCategorySplit" property="Transaction.Splits">
        <return-property column="TransactionCategorySplit.Amount" name="Amount" />
        <return-property column="TransactionCategorySplit.CreateDate" name="CreateDate" />
        <return-property column="TransactionCategorySplit.TransactionCategoryID" name="TransactionCategoryID" />
        <return-property column="TransactionCategorySplit.TransactionID" name="TransactionID" />
        <return-property column="TransactionCategorySplit.ID" name="ID" />
    </return-join>
  <![CDATA[
    exec core.GetTransactionsForDownload :AccountIdentifier, :StartDate, :EndDate
  ]]>
</sql-query>

The stored procedure looks like this:

SELECT  t.ID AS 'Transaction.ID' ,
        t.UserID AS 'Transaction.UserID' ,
        t.PostingDate AS 'Transaction.PostingDate' ,
        t.EffectiveDate AS 'Transaction.EffectiveDate' ,
        t.Amount AS 'Transaction.Amount' ,
        t.PrincipalAmount AS 'Transaction.PrincipalAmount' ,
        t.InterestAmount AS 'Transaction.InterestAmount' ,
        t.EscrowAmount AS 'Transaction.EscrowAmount' ,
        t.FeeAmount AS 'Transaction.FeeAmount' ,
        t.OtherAmount AS 'Transaction.OtherAmount' ,
        t.Balance AS 'Transaction.Balance' ,
...etc
         tcs.ID AS 'TransactionCategorySplit.ID' ,
         tcs.TransactionID AS 'TransactionCategorySplit.TransactionID',
         tcs.Amount AS 'TransactionCategorySplit.Amount' ,
         tcs.CreateDate AS 'TransactionCategorySplit.CreateDate' ,
         tcs.TransactionCategoryID AS 'TransactionCategorySplit.TransactionCategoryID' ,
         tcs.TransactionID AS 'TransactionCategorySplit.TransactionID'
 FROM    core.Transactions t
         LEFT OUTER JOIN core.TransactionCategorySplit tcs ON t.ID = tcs.TransactionID
 (where and order by clauses)

When I try to get results back from this, I get an odd exception. It's a System.IndexOutOfRangeException with "ID106_1_" as its Message property. It's like NHibernate is still expecting its generated aliases to be present in the result set, and it's not taking the column attributes I set into account. If I set the column aliases to the odd-looking things NH is expecting, I can get the data back successfully, but that seems awfully hacky.

Is the sql-query mapping incorrect?

Answer 1

As you can see here.

Notice that stored procedures currently only return scalars and entities. <return-join> and <load-collection> is not supported.