NHibernate mapping. Set collection of joined-subclass declared elements and the where property.

Question

I am working on a project where persisted data can be flagged for deletion, but remains in the database having a column (is_deleted) set to TRUE.

This works fine using the hibernate class mapping property "where" and adding where="is_deleted = 0" to the tags. But it fails when I declare a set of sub-class elements.

This simplified example uses the class "Car" containing a member collection of class "Wheel" which inherits from the class "CarPart":

<class name="Car" where="is_deleted = 0">
  <id name="Identifier" column="car_id">
    <generator class="native" />
  </id>
  <set name="Wheels" lazy="true" where="is_deleted = 0">
    <key column="car_id" />
    <one-to-many class="Wheel" />
  </set>
</class>

<class name="CarPart" where="is_deleted = 0">
  <id name="Identifier" column="part_id">
    <generator class="native" />
  </id>
  <property name="IsDeleted" />

  <joined-subclass name="Wheel" >
    <key column="part_id" />
    <property name="radius" />
  </joined-subclass>
</class>

If I in code try to access the collection Car.Wheels I get an SQL error because the "where" clause applies to the table of the sub-class "Wheel" instead of its super-class "CarPart" where the IsDeleted property is actually defined.

The generated SQL looks similar to this:

select * from Wheel w inner join CarPart cp on...
where w.is_deleted = 0

rather than the correct

select * from Wheel w inner join CarPart cp on...
where cp.is_deleted = 0

• Is this a bug? Or am I missing a detail? The super-class CarPart already has where="is_deleted = 0", so logically this should apply to all defined sub-classes?
• Are there other methods of adding a is_deleted flag to all persisted data in NHibernate?

Your help is very much appreciated

Answer 1

I strongly recommend creating a view for every soft delete table that filters out the deleted records and map the view in your domain model. Do this will make mapping much easier.

If your database supports it, you can create an INSTEAD OF DELETE trigger on the tables to set the is_deleted flag instead of deleting the record. That combined with views allows your domain model to completely ignore soft deletes.