NHibernate <join> with Multiple Child Records

Question

I have a master record which I'd like to use with. Here's a very basic example mapping example:

<class name="Master">
 <join table="Detail">
  <key>
   <column name="Id" />
  </key>
  <property name="Name" />
 </join>
</class>

This is all very well, and would work but for one problem. The Detail table looks like:

Master_ID    EffectiveTo   Name
1            1/1/2010      Colin
1            NULL          ColinRamsay

There are multiple Detail records for each master, with the current one being the record with a NULL EffectiveTo. With the , I only want that current record to be joined and no other ones.

I'm not sure this is even possible with NH, I know I could create a many-to-one relationship here but I'd also have to create the Detail class and hbm.xml mapping. just seems simpler.

Any suggestions?

Answer 1

join is used to map properties of one class to several tables, when there's a 1-to-1 relationship between the tables.

You can use custom SQL for create, update and delete and load.

Another option is to join onto a view that selects the current records of the Detail table.