How can I apply a filter to grandparents in nHibernate?

Question

I have a question about nHibernate and filtering. I am using nHibernate 2.2 but I think this will apply to versions as early as 1.2.

I have created a simple example to illustrate the problem. I have three domain objects: Country, State, and City (assume the DB tables match the domain and have the appropriate relations defined. I want to select cities that belong to a specific country using nHibernate filters. I believe I've set up the filters correctly and the filter by state (the parent) works great.

I have included the domain model and my nHibernate mapping below. My query is a simple HQL statement: select f from city f

Country
 CountryId int
 Name      string
 States    list

State
  StateId   int
  Name      string
  Country   object
  Cities    list
City
  CityId    int
  Name      string
  State     object

nHibernate Mapping:

    <?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="CMDB.Model" namespace="CMDB.Model">
  <class name="Country" table="Country" lazy="false">
    <id name="CountryId" type="int" unsaved-value="0">
      <generator class="identity" />
    </id>
    <property name="Name" type="string"/>
    <bag name="States" table="State" inverse="true" lazy="false" cascade="all-delete-orphan">
      <key column="StateId"/>
      <many-to-many class="State" column="StateId"/>
      <filter name="ByCountry" condition=":CountryId = CountryId"/>
    </bag>
  </class>
  <class name="State" table="State" lazy="false">
    <id name="StateId" type="int" unsaved-value="0">
      <generator class="identity" />
    </id>
    <property name="Name" type="string"/>
    <many-to-one name="Country" column="CountryId" class="Country" lazy ="false" />
    <bag name="Cities" table="City" inverse="true" lazy="false" cascade="all-delete-orphan">
      <key column="CityId"/>
      <many-to-many class="City" column="CityId"/>
      <filter name="ByState" condition=":StateId = StateId"/>
    </bag>
    <filter name="ByCountry" condition=":CountryId = CountryId"/>
  </class>
  <class name="City" table="City" lazy="false">
    <id name="CityId" type="int" unsaved-value="0">
      <generator class="identity" />
    </id>
    <property name="Name" type="string"/>
    <many-to-one name="State" column="StateId" class="State" lazy ="false" />
    <filter name="ByState" condition=":StateId = StateId"/>
  </class>
  <filter-def name="ByCountry">
    <filter-param name="CountryId" type="int"/>
  </filter-def>
  <filter-def name="ByState">
    <filter-param name="StateId" type="int"/>
  </filter-def>
</hibernate-mapping>

Answer 1

I spent a lot of time researching this and determined that nHibernate will not apply nested filters. The solution is to handle the problem programatically and start with the parent, returning a filterered list and then building a list of all the children belonging to the filtered parents.

So, in my example I would load all states belonging to a particular country and the build a list of the states child cities.