Complex relationship between tables in NHibernate
- by Ilya Kogan
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 :)
