title: Complex relationship between tables in NHibernate
body: <p>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.</p>
  
  <p>These are the two tables I'll focus on:</p>
  
  <p><img src="https://i.stack.imgur.com/DfpPE.png" alt="alt text"></p>
  
  <h2>Example data</h2>
  
  <pre><code>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 ...
  </code></pre>
  
  <p>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.</p>
  
  <h2>Constraints</h2>
  
  <p>Trips of the same vehicle never overlap. So the tables have an <strong>optional one-to-many relationship,</strong> because every fault either happens during a trip or it doesn't. If I wanted to join them in SQL, I would write:</p>
  
  <pre><code>select ... 
  from Faults left outer join Trips
    on Faults.VehicleId = Trips.VehicleId
    and Faults.FaultTime between Trips.TripStartTime and Trips.TripEndTime
  </code></pre>
  
  <p>and then I'd get a dataset where <strong>every fault appears exactly once</strong> (one-to-many as I said).</p>
  
  <p>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.</p>
  
  <h2>What am I actually looking for?</h2>
  
  <p>The tables are huge because they cover years of data, and every time I only need to fetch a range of a few hours.</p>
  
  <p>So I need a mapping and a criteria that will run something like the following SQL underneath:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Do you have any ideas how to achieve it?</p>
  
  <p><strong>Note 1:</strong> 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.</p>
  
  <p><strong>Note 2:</strong> I know it's a tough one, so if you give me a great answer, you will be properly rewarded :)</p>
  
  <p>Thank you for reading this long question, and now I only hope for the best :)</p>