Complex relationship between tables in NHibernate

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 :)

---

Current Recommendation

Given the additional information in the comments, I would now propose trying the following class mappings instead of using any of the custom SQL solutions mentioned further down this answer:

<class name="Fault" table="Faults">
  <composite-id>
    <key-property name="VehicleId" />
    <key-property name="FaultTime" />
    <key-property name="FaultType" />
    <generator class="assigned" />
  </id> 
  <many-to-one name="Trip" class="Trip">
    <!-- Composite Key of Trip is calculated on the fly -->
    <formula>VehicleId</formula>
    <formula>
      ( SELECT  TripStartTime 
        FROM    Trips t 
        WHERE   VehicleId = t.VehicleId 
        AND     FaultTime BETWEEN t.TripStartTime AND t.TripEndTime
      )
    </formula>
  </many-to-one>
  ...
</class> 

<class name="Trip" table="Trips">
  <composite-id>
    <key-property name="VehicleId" />
    <key-property name="TripStartTime" />
  </composite-id> 
  ...
</class>

Using this mapping you can load and query the Fault entities however you like.

Obsolete Suggestions

I originally considered a (named) custom SQL query here. You could enter the following query in your mapping file to load Fault objects with for a given vehicle:

<sql-query name="LoadFaultsAndTrips" xml:space="preserve">
  <return class="Fault" alias="f"/>
  <return-join alias="t" property="f.Trip"/>
  SELECT  {f.*}
      ,   {t.*}
  FROM    Faults f
  LEFT OUTER JOIN Trips t 
      ON f.VehicleId = t.VehicleId
      AND f.FaultTime BETWEEN t.TripStartTime AND t.TripEndTime
  WHERE f.VehicleId = ?
</sql-query>

If you need to load the Faults collection on a Vehicle object without explicit queries you could try the following mapping construct in XML:

<class name="Vehicle">
   <id name="VehicleId" type="...">
     <generator class="..." />
   </id>
   ...
   <bag name="Faults" table="Faults" inverse="true">
     <key column="VehicleId" />
     <loader query-ref="VehicleFaultsLoader" />
   </bag>
   ...
</class>

<sql-query name="VehicleFaultsLoader" xml:space="preserve">
  <load-collection role="Vehicle.Faults" alias="f" />
  <return-join alias="t" property="f.Trip"/>
  SELECT  {f.*}
      ,   {t.*}
  FROM    Faults f
  LEFT OUTER JOIN Trips t 
      ON f.VehicleId = t.VehicleId
      AND f.FaultTime BETWEEN t.TripStartTime AND t.TripEndTime
  WHERE f.VehicleId = ?
</sql-query>

The key here is to define a custom collection loader for the Faults collection on the Vehicle class and to define a custom SQL query that receives the primary key of Vehicle as parameter. I haven't used fluent NHibernate yet myself, so I'm afraid I cannot help you with that part of the question.

Cheers, Gerke.

---

You example sql there is syntactically the same as

select ... 
from Faults left join Trips
  on Faults.VehicleId = Trips.VehicleId
where Faults.VehicleId is null or (Faults.FaultTime between Trips.TripStartTime and Trips.TripEndTime)

with this in mind, you can create a regular map such as (fluent)

HasMany< Trip >( fault => fault.Trips )
    .KeyColumn( "VehicleId" )
    .Table( "Trips" )
    .LazyLoad( )
    .Cascade.Delete( )
    .AsSet()

then using what every form of querying you are comfortable with, be it hql, icriteria, icriteriaover or linq do your standard query with a where clause as mentioned above.

in linq that would be:

IList<Trip> results = 
( 
    fault in Session.Query< Entities.Faults > 
    join trip in Session.Query< Entities.Trips > on fault.VehicleId equals trip.VehicleId into trip
    where
    fault.FaultTime > startTime && fault.FaultTime < endTime &&
    // Here is the rest of the join criteria expressed as a where criteria
    (
        trip == null
            || 
        (
            fault.FaultTime > trip.TripStartTime && fault.FaultTime < trip.TripEndTime
        ) 
    )
    select fault
).ToList();

If need be I can give you an example in ICriteria or IQueryOver.

Of course this only work because of the example statement you provided can be re-written as a where clause while having the result. If you real world desired sql is more complex you'd need to think if the desired sql can be re-written while archiving the same result.

---

I'm pretty new to NH and only know NH rudiments, so when I've hit a situation like this I've written a stored proc and then called it through NH. Eventually I'll find an all-NH solution, and then I'll refactor the code and remove the necessity for the stored proc.

Another approach that might work is to just write the HQL you need.

---

I'll make a suggestion, if you are using NHibernate 3, try Linq to NH. Using Linq you can specify manualy/arbitrary relationships for a once off execution, or use pipes if you think it's going to be re-used (or being linq if you want to do a left/right join you need to specify it, if it's an isser join you don't need to specify a join at all, its all inferred from the mappings) and is business logic and not persitence logic.

As a quick example it would be somethng like:

var result = ( 
fault in Session.Query< Entities.Faults > 
join trip in Session.Query< Entities.Trips > on fault.VehicleId equals trip.VehicleId into trip
where 
fault.FaultTime > startTime && fault.FaultTime < endTime &&
fault.FaultTime > trip.TripStartTime && fault.FaultTime < trip.TripEndTime
select fault
).ToList();

I've written this by hand so it might not be perfect, but close enough. This should do exactly what you need, and allow you to change it as you see fit without changing you mappings.

---

If you already know what query you want the DB to execute, why not just execute the query directly using your own custom DAO class? Why bother with the NHibernate abstraction, if it's just getting in the way?