NHibernate: Where clause on one-to-many relationships doesn't work when column name is ambiguous

Question

It is possible to specify an arbitrary SQL where clause for collection mappings. For example:

<map name="myEntity" where="foo = 1" />

However if the column name is ambiguous for some reason, the sql fails. For example, this can occur if you are trying to use joins for example.

Given that the table aliases are automatically generated, you can't qualify the column name. This makes the feature seem rather silly. Does anyone know if there is a work around?

Answer 1

NHibernate should figure out the correct alias for the property you are referencing. Is foo a mapped property of the item entity type (the item type that is in the map collection) ?

For example this works:

<class name="Category" table="Category">
  <id name="Id">
    <generator class="guid.comb" />
  </id>

  <property name="Name" not-null="true" length="255" />

  <bag name="ProductList" table="Product" cascade="none" where="Name like '%test%'" fetch="join">
    <key column="CategoryId" />
    <one-to-many class="Product" />
  </bag>

</class>

There is a property on both Category and the Product class named "Name" but nhibernate will in this case use the on defined on the Product class.