Nhibernate custom loader for collection

Question

Im really hoping someone can help with this, have been trying various combinations for a day and a half now....

Basically I have a some hierarchical data stored in a single table, the usual parentID maps to a row id scenario. I have modelled a property within the domain object that returns a list of ancestors for a given item. It all seems to work looking at the logs (ie its retrieving and hydrating the correct rows :

CollectionLoadContext - 4 collections were found in result set for role: Domain.Keyword.Ancestors
CollectionLoadContext - 4 collections initialized for role: Domain.Keyword.Ancestors

HOWEVER: the collection never actually gets populated, stepping though my code the Ilist doesn't contain what it should - only a single instance (the same as the current record)!?? There are no errors in the logs about not being able to map the returned columns, it just doesn't appear to populate correctly? Im sure im missing something obvious - but just cant see it....

I have a collection declaration like this in my mapping

<bag name="Ancestors" inverse="true"cascade="none" lazy="true" fetch="select" generic="true" >
  <key column="KeywordID"/>
  <one-to-many class="Domain.Keyword, BS.Core" />
  <loader query-ref="CustomAncestorLoader"  />
</bag>

... and a custom loader named query to return a list of keyword ancestors for a given keyword:

<sql-query name="CustomAncestorLoader">
<load-collection alias="Ancestors" role="Domain.Keyword.Ancestors"/>

SELECT  s.KeywordID, s.kwdhier, s.Keyword, s.Notes, s.position , s.ParentKeywordId
From dbo.utKeywordBranch(:ParentID) k join Keywords s on k.KeywordId = s.[KeywordID] </sql-query>

Im at the point where im tearing hair out as I spent so long on this already, so any help would be greatly appreciated!!

Answer 1

Ok, don't you just hate it when its SO obvious.....

basically the collection was mapped to a key called KeywordId - meaning it will join based on the current object id on that key, in the rows returned none of the obects would have had this key (as they are all different objects).

So to fix I added a new return column in the named query called OriginalID, which simply returned the ID we pass in as a parameter. This way the collection has something to map to:

<bag name="Ancestors" inverse="true" cascade="none" lazy="true"  fetch="select" generic="true" >
  <key column="OriginalID"/>
  <one-to-many class="Domain.SubjectKeyword, BS.Core" />
  <loader query-ref="CustomAncestorLoader"    />
</bag>

SELECT k.OriginalId, s.KeywordID, s.kwdhier, s.Keyword, s.Notes, s.position , s.ParentKeywordId
From dbo.uKeywordBranch(:Id) k join KATKeywords s on k.KeywordId = s.[KeywordID]

Sometimes it just helps to talk it though!