Mapping for versioned entities with euntime timestamp filtering

[cschiel]

Hello everyone,

I'm working on a legacy project and need help with NHibernate mapping. Here's the situation:

Class A in table_1 has a referenced_id (Guid).
Class B in table_2 has version_id (Guid, pk), id (Guid), and valid_from (DateTime).

Business Logic: At any given time, there is exactly one valid version of Class B with a specific id.

Goal: Map Class A to Class B using a user-provided timestamp to find the correct version at runtime.

Current Attempt:

<set name="Class_B" table="my_new_mapping_table" cascade="none" lazy="false" fetch="join" batch-size="100">
  <key column="..." />
  <many-to-many class="Class B"
    column="referenced_id"
    property-ref="id"
    fetch="join"
    lazy="false"
    unique="true" />
  <filter name="filter"
    condition="valid_from in (select max(b.valid_from) from table_2 b where :timestamp >= b.valid_from and b.id = referenced_id)" />
</set>

The set will never have more than one entry, creating unnecessary overhead. I am not sure if I can use a different mapping since the filter is not possible and I can't figure out if the 'formula' option allows for runtime user input.

I'm looking for alternative approaches to achieve this mapping efficiently. Any suggestions would be greatly appreciated.

[cschiel]

Hi again.

So I guess I found the main problem with my view of nhibernate. What I am trying to achieve is a <many-to-one> relation where there is somehow a filter being applied on the class B table to only give me the correct entry with `id` (there are multiple but with different versionid/validfrom). This does not seem to be possible and most likely should not work since it could cause a lot of problems. If someone has an idea how to solve it besides using a set, I would still appreciate it.