Filter collection using parent's property

Question

I would like to apply a filter on a collection (SET) using a property in the parent's class.
I've found out that I can use the where clause for a collection - and it seems to work - but I am not sure if this is the best option or if this solution could give me pains in the future.

This is the mapping for my class Order:

<class name="Order" table="OCSAORH_NHBP" mutable="false" where="OCHAMND = 0">
    <composite-id>
      <key-property name="Number" column="OCHORDN" type="String" length="10"></key-property>
      <key-property name="Ver" column="OCHAMND" type="Int32"></key-property>
      <key-property name="Company" column="OCHCOSC" type="String" length="5"></key-property>
    </composite-id>
    <property name="WareHouseDelivery" column="OCHMAGS" type="String" length="7"></property>

    <set name="OrderLines" access="field.pascalcase-underscore" inverse="true" lazy="true" mutable="false" cascade="none" where="OCLMAGS = this_.OCHMAGS">
      <key>
        <column name="OCLORDN" not-null="true"/>
        <column name="OCLAMND" not-null="true"/> 
        <column name="OCLCOSC" not-null="true"/>
      </key>
      <one-to-many class="OrderLine" not-found ="ignore" />
    </set>
</class>

and this is my class OrderLine :

<class name="OrderLine" table="OCSALIN_NHBP" mutable="false" where="OCLAMND = 0">
    <composite-id>
      <key-property name="Number" column="OCLORDN" type="String" length="10"></key-property>
      <key-property name="Company" column="OCLCOSC" type="String" length="5"></key-property>
      <key-property name="Line" column="OCLLINN" type="Int32"></key-property>
      <key-property name="Seq" column="OCLSSEQ" type="Int32"></key-property>
    </composite-id>
    <property name="Item" column="OCLITMN" type="String" length="19"></property>
    <property name="WareHouseDelivery" column="OCLMAGS" type="String" length="7"></property>

    <many-to-one name="Order" class="Order">
      <column name="OCLORDN" not-null="true"/>
      <column name="OCLAMND" not-null="true"/>
      <column name="OCLCOSC" not-null="true"/>
    </many-to-one>
</class>

This is a Oracle legacy database and I cannot change it's schema. Primary keys are all composite.

Now, I would like to fetch the orders using some criteria and lazy-load the order lines using, as a filter, the field OCHMAGS (property WareHouseDelivery).
As you can see I've defined a where clause in the set using the alias of the parent's table :

<set name="OrderLines" ... where="OCLMAGS = this_.OCHMAGS">

PS: I eager-load my orderlines collection:

var qry = .QueryOver<Domain.Order>()
    .Fetch(t => t.OrderLines).Eager
    .JoinAlias(t => t.OrderLines, () => orderLine, JoinType.LeftOuterJoin);

Is this a viable solution? Are there any other alternatives?

Answer 1

I am not sure about your comment: ... and it seems to work ...
Because it cannot work. The this representing the Parent, is not available in the WHERE clause of the collection mapping. It is the alias of the collection table.

The collection is loaded separately from the owner. NHibernate will execute the SELECT...FROM the OrderLine table: "OCSALIN_NHBP". Without any JOIN to the parent. As a parameter in the WHERE clause is injected the ParentId (in your case, the key is composed from more columns). That's it.

One way how to do that, is to create more complex WHERE clause, using the Parent Table as subselect. (This is of course not so effective on large tables)

  <set ...
    where="OCLMAGS IN 
      (SELECT p.OCLMAGS FROM OCSAORH_NHBP p WHERE p.OCLORDN = OCLORDN...)" >

Maybe there are even other ways, but the this is not what it seems to be