Hi all,

I'm writing a Fluent NHibernate mapping for a legacy Oracle database. The challenge is that the tables have composite primary keys. If I were at total freedom, I would redesign the relationships and auto-generate primary keys, but other applications must write to the same database and read from it, so I cannot do it.

These are the two tables I'll focus on:

Example data

Trips table:
1, 10:00, 11:00 ...
1, 12:00, 15:00 ...
1, 16:00, 19:00 ...
2, 12:00, 13:00 ...
3, 9:00, 18:00 ...

Faults table:
1, 13:00 ...
1, 23:00 ...
2, 12:30 ...

In this case, vehicle 1 made three trips and has two faults. The first fault happened during the second trip, and the second fault happened while the vehicle was resting. Vehicle 2 had one trip, during which a fault happened.

Constraints

Trips of the same vehicle never overlap. So the tables have an optional one-to-many relationship, because every fault either happens during a trip or it doesn't. If I wanted to join them in SQL, I would write:

select ... 
from Faults left outer join Trips
  on Faults.VehicleId = Trips.VehicleId
  and Faults.FaultTime between Trips.TripStartTime and Trips.TripEndTime

and then I'd get a dataset where every fault appears exactly once (one-to-many as I said).

Note that there is no Vehicles table, and I don't need one. But I did create a view that contains all VehicleIds from both tables, so I can use it as a junction table.

What am I actually looking for?

The tables are huge because they cover years of data, and every time I only need to fetch a range of a few hours.

So I need a mapping and a criteria that will run something like the following SQL underneath:

select ... 
from Faults left outer join Trips
  on Faults.VehicleId = Trips.VehicleId
  and Faults.FaultTime between Trips.TripStartTime and Trips.TripEndTime
where Faults.FaultTime between :p0 and :p1

Do you have any ideas how to achieve it?

Note 1: Currently the application shouldn't write to the database, so persistence is not a must, although if the mapping supports persistence, it may help at some point in the future.

Note 2: I know it's a tough one, so if you give me a great answer, you will be properly rewarded :)

Thank you for reading this long question, and now I only hope for the best :)