Effective Date in nhibernate

Question

I have a many to many relationship between two existing tables (User: UserId, Name) and (Location: LocationId, Name), these can't be changed. I have used an many-to-many mapping, but we have a new requirement to include history/future movements.

I have used the peoplesoft concept of effectively dating the records in a new intersect table (UserLocation: UserId, LocationId, EffectiveDate, Status) that replaces the old table, where the first 3 fields make up the primary key.

In SQL I can simply use the following statement to return the active record for the given effective date, but I can't get anything like this from NHibernate (even get an error about parameters not working in multilevel select).

select l.*  
from location l  
where l.locationId in  
  (select ul.locationId  
   from UserLocation ul  
   where (ul.EffectiveDate =   
     (select max(ul_ed.EffectiveDate)  
      from UserLocation ul_ed  
      where ul.LocationId = ul_ed.LocationId  
        and ul.EffectiveDate = ul_ed.EffectiveDate  
        and ul.UserId - ul_ed.UserId  
        and ul.Status = true))  

I need to return back further filtered records by including parameters for UserId and EffectiveDate columns but would be happy to return everything to start with.

My issue however is the mapping classes (I use code mapping, not fluent) and can't even get it close to working (limited NHibernate knowledge). I was hoping not to have to create a new class for the intersect table. I haven't even got to the insert/update/delete part yet as I can't even get select to work.

Requirements: 1. Insert new record in UserLocation when associating a user to a location with EffectiveDate = NOW, Status = True 3. Moving a user to a new location should insert new record in UserLocation with EffectiveDate = NOW, Status = True 4. Removing user from the location will insert new UserLocation record with EffectiveDate = NOW, Status = False 5. Retrieve the Locations the user is associated to on a given date.

Note: As reports are generated on this data we cannot delete the records, and data is also created for future events.

If someone could point me in the right direction to get started that would be appreciated. I have looked at Loader and Interceptor classes but can't seem to make it fit.

Answer 1

This is the answer we have come up with that works for the scenario described.

Filter("EffectiveDate", mm => mm.Condition("Status = 1 and EffectiveDate = (select max(t1.EffectiveDate) from Table1 t1 where t1.Id = Id and t1.EffectiveDate <= :effectiveDate)"));

Filters can be applied to the table, and to the mappings. However we ended up combining the 2 tables into a single table to reduce the complexity.

Note: The Filter must be defined and applied to the session before it will be used in the mapping. See nHibernate doco for full details it quite well documented.