NHibernate filter collection

Question

Using NHibernate I want to filter a collection in a class to contain ONLY a subset of possible objects. Below I am including a sample table data to help explain. I can find no way to do this using NHibernate.

Table:DataObject

DataObjectId(PK) / Name / CurrentVersion

11          "data.txt"      2
12          "info.txt"      3

Table:DataObjectVersion

Id / Comment / VersionNumber / DataObjectId(FK)

31   "Genesis"         1          11     <= Ignore this object
32   "Changed data"    2          11     <= Get this object
34   "Genesis"         1          12     <= Ignore this object   
35   "Changed info"    2          12     <= Ignore this object
36   "Added info"      3          12     <= Get this object

I want to join on a non-foreign key DataObject.CurrentVersion = DataObjectVersion.VersionNumber for each DataObject in one command.

Here are the classes and mapping files:

public class DataObject
{
  public virtual int DataObjectId { get; set; }
  public virtual string Name { get; set; }
  public virtual int CurrentVersionNumber { get; set; }
  public virtual IList<DataObjectVersion> Versions { get; set; }
}

<class name="DataObject" table="DataObject" lazy="false">
    <id name="DataObjectId" column="DataObjectId" type="int">
      <generator class="assigned" />
    </id>
    <property name="Name" column="Name" type="String(512)" />
    <property name="CurrentVersionNumber" column="CurrentVersionNumber" type="int" />
    <bag name="Versions" cascade="all-delete-orphan" inverse="true" lazy="false" >
        <key column="DataObjectId" />
        <one-to-many class="DataObjectVersion" />
    </bag>
</class>

public class DataObjectVersion
{
    public virtual int DataObjectVersionId { get; set; }
    public virtual string Comment { get; set; }
    public virtual int VersionNumber { get; set; }
    public virtual int DataObjectId { get; set; }
}

<class name="DataObjectVersion" table="DataObjectVersion" lazy="false">
    <id name="Id" column="DataObjectVersionId" type="int">
      <generator class="assigned" />
    </id>
    <property name="Comment" column="Comment" type="String(512)" />
    <property name="VersionNumber" column="VersionNumber" type="int" />
    <property name="DataObjectId" column="DataObjectId" type="int" />
</class>  

Answer 1

if you want to filter the collection on demand, using a filter is a valid choice. You would need to declare the filter on both the Version class and in the bag element and apply the filter from the NHibernateSession.EnableFilter method

if you always want to fetch a single Version in the bag then implement a 'where' in the mapping of the bag:

<bag name="Versions" cascade="all-delete-orphan" inverse="true" lazy="false" where="CurrentVersionNumber = Versions.VersionNumber" >
    <key column="DataObjectId" />
    <one-to-many class="DataObjectVersion" />
</bag>

note that in the 'where' you write proper SQL not HQL and as such the proper SQL i write above probably has to be changed to reflect your schema

Additionally if a single object is to be fetched setting up a bag and the according IList may be an overkill. Applying a formula property and a DataObjectVersion object in the class may be more appropriate

in the class DataObject replace the IList with

public virtual DataObjectVersion Version { get; set; }

and in the mapping replace the 'bag' with something in the lines of

<property name="Version" type="DataObjectVersion" update="false" insert="false" formula="(select v.DataObjectVersionId, v.Comments, v.VersionNumber, v.DataObjectId from DataObjectVersion v where v.VersionNumber  = CurrentVersionNumber)" />

again only proper SQL is allowed

i've used computed properties with native datatypes (datetime, string etc) and fetching an Entity may (or may not) need something more or different

Last but not least, you could apply a filter on the collection after you have fetched the primary object DataObject by creating a filter on the collection

IList<DataObjectVersion>  fVersion = 
    NHibernateSession.CreateFilter(do.Versions, "where VersionNumber = :ver")
        .SetParameter("ver", do.CurrentVersionNumber)
        .List<DataObjectVersion>();

where the do.Versions collection is not initialized, only the results fetched in the separate fVersion collection and this is a second SELECT after already having made the round-trip to the db for the DataObject fetch.