Fetching Orders and Orderlines Count with subqueries

I am in process to port an old App to Nhibernate.

The old application uses ORACLE packages extensively and I want to get rid of that.

I've started to map few tables and things seem to work very well.

Now, I've got this query which I would like to be able to manage via QueryOver ... or something similar:

SELECT
    Orders.*
    (SELECT COUNT(*) FROM OrderLines
        WHERE OrderLines.CompanyCode = Orders.CompanyCode
              AND OrderLines.Orde开发者_如何学运维rNumber = Orders.OrderNumber
              AND NOT (OrderLines.OCLSCOM = 'Y' AND OrderLines.OCLSSEQ = 0)
              AND OrderLines.Status = 'R') OrderLinesCount
    FROM
        Orders
    WHERE
        AND Orders.CompanyCode = [CompanyCode];

[CompanyCode] is a filter.

I've got to mapping files (Orders and OrderLines) and my association looks like this:

<class name="Order" table="Orders">
    ...
    <set name="OrderLines" access="field.pascalcase-underscore" inverse="true" lazy="extra" cascade="none">
      <key>
        <column name="OrderNumber" not-null="true"/>
        <column name="CompanyCode" not-null="true"/>
      </key>
      <one-to-many class="OrderLine" not-found ="ignore"/>
    </set>
</class>

The primary key for my Orders table is CompanyCode and OrderNumber.

I would like to query the Orders and fetch the number of lines for each order.

I've achieve what I want adding a formula property (thanks Ayende for that) on the Order mapping:

<property name="OrderLinesCount" formula="(SELECT COUNT(*) FROM OrderLines WHERE OrderLines.CompanyCode = CompanyCode AND OrderLines.OrderNumber = OrderNumber AND NOT (OrderLines.OCLSCOM = 'Y' AND OrderLines.OCLSSEQ = 0) AND OrderLines.Status = 'R')" />

but I am scared my customer might decided to change those nasty filters one day and I would be forced to recompile the whole project.

Is there a way to achieve the same result with a subquery (QueryOver) ?

Thanks in advance for your help.

---

Might not be exactly what you are looking for but have you considered a where clause on the collection. You can call order.OrderLinesFiltered.Count to get the value

<set name="OrderLinesFiltered" table="OrderLines" 
access="field.pascalcase-underscore" inverse="true" lazy="extra"
cascade="none" 
where=" NOT (OCLSCOM = 'Y' AND OCLSSEQ = 0) AND Status = 'R' ">
  <key>
    <column name="OrderNumber" not-null="true"/>
    <column name="CompanyCode" not-null="true"/>
  </key>
  <one-to-many class="OrderLine" not-found ="ignore"/>
</set> 

---

At the end I've decided to go for the formula property:

<property name="OrderLinesCount" formula="(SELECT COUNT(*) FROM OrderLines WHERE OrderLines.CompanyCode = CompanyCode AND OrderLines.OrderNumber = OrderNumber AND NOT (OrderLines.OCLSCOM = 'Y' AND OrderLines.OCLSSEQ = 0) AND OrderLines.Status = 'R')" />

One day, if I'll need to change filter I might think to use nHibernate filters.