Hello,
this probably is the wrong way to go about things with nhibernate but I have a situation involving a legacy database. Where I have a parent entity (Schedule) which contains a collection of children entites (Travel). There is one database table representing the schedule (called SCHEDULE) however there are two tables reprenting Travel (TRAVEL and TRAVEL_OTHER). In the Travel tables the primary keys are a composite of the foreign-key to the SCHEDULE table (SCHEDULE_NO) and a TRAVEL_NO.
So the PK for SCHEDULE is SCHEDULE_NO
PKs for TRAVEL/TRAVEL_OTHER are SCHEDULE_NO, TRAVEL_NO
The hbm (or rather a cut down version with just the one-to-many relationship) for schedule.
Code:
<class name="Schedule"
lazy="true"
table="SCHEDULE">
<id name="Number" column="SCHEDULE_NO" >
<generator class="assigned" />
</id>
<list name="TravelSlots" inverse="true">
<key column="SCHEDULE_NO" />
<index column="TRAVEL_NO" />
<one-to-many class="TravelBooking" />
</list>
</class>
The mapping file for the Travel entity including the <join> to TRAVEL_OTHER
Code:
<class name="Travel"
lazy="false"
table="TRAVEL">
<id name="Number">
<column name="TRAVEL_NO" not-null="false" />
<generator class="native"/>
</id>
<property name="Notes" column="NOTES" type="AnsiString" />
<many-to-one name="Schedule" column="SCHEDULE_NO" not-null="true" />
<join table="TRAVEL_OTHER" >
<key>
<column name="TRAVEL_NO" />
</key>
<property name="DestinationCity" column="DEST_CITY" type="AnsiString" />
<join>
</class>
Now this all works fine when I get the schedule and then iterate through the travel.
eg
Code:
Schedule schedule = session.Get<Schedule>( 1 );
IList<Travel> travelSlots = schedule.Travel;
However, I got to thinking if it was possible given that I might know exactly the travel entity I'm wanting (i.e I know the SCHEDULE_NO and TRAVEL_NO) what is the best way of getting it.
I tried using Linq to NHibernate with the following query:
Code:
var specificTravel = ( from travel in session.Linq<Travel>()
where travel.Schedule.Number == 1 && travel.Number == 1
select travel );
Assert.AreEqual( 1, specificTravel ); //this is false even though there is only 1 travel of both the schedule number and travel number
This doesn't return back the single entity I expected and the reason being is the the sql between the TRAVEL and TRAVEL_OTHER only does an INNER JOIN on TRAVEL_NO and doesn't also include DEAL_NO.
Generated sql
Code:
SELECT this_.TRAVEL_NO as TRAVEL1_2_1_,
this_.NOTES as NOTES2_1_,
this_.CITY as CITY2_1_,
schedule1_.SCHEDULE_NO as SCHDULE1_4_0_
FROM TRAVEL this_
inner join TRAVEL_OTHER this_1_
on this_.TRAVEL_NO=this_1_.TRAVEL_NO <--- no join on schedule_no as well
left outer join SCHEDULE schedule1_
on this_.SCHEDULE_NO=schedule1_.SCHEDULE_NO
WHERE this_.TRAVEL_NO=1 AND schedule1_.SCHEDULE_NO = 1
doing a similar query using hql gives the same response. So (after all that) my question is should nhibernate be able to cope with this or am I approaching it wrong.
Cheers,
Huw
Login
FAQ
Search