Mapping and query for three way many-to-many-to-one in NHibernate

Question

UPDATE: I mistakenly pasted query code from the wrong overload. At the bottom is the fixed code

Hi, let's say here's my domain.

I'm tracking some sport Events like say car races.

Each Race has Racers who take part in the race.

Racer is a Driver in particular Race (it has the Driver, start lane, run-time, etc).

Driver has things like Name etc... typical personal data.

I want to do the following query: Get me all races (let's leave paging out) in this Event, showing their contestants information including data taken from the drivers.

My problem is, I don't think my mapping (or criteria query, or both) is optimal for this scenario, so I wanted to ask you, if you see any chance for optimization here. I especially dislike the fact that currently it takes two roundtrips to the database when I think a subquery could make it work in just one go.

Here are my mapping and the query (DB is generated from the mappings)

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
        assembly="Foo"
        namespace="Foo">
  <class name="Event" table="Events">
      <id name="Id">
        <generator class="guid"/>
      </id>
    <property name="EventId" not-null="true" unique="true" index="IDX_EventId" />
  </class>
</hibernate-mapping>

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
     assembly="Foo"
     namespace="Foo">
  <class name="Race" table="Races">
    <id name="Id">
      <generator class="guid"/>
    </id>
    <property name="RaceId" not-null="true" unique="true" index="IDX_RaceId"/>
    <property name="Year" not-null="true" />
    <property name="IsValid" not-null="true" />
    <property name="Time" not-null="true" />

    <many-to-one name="Event" cascade="all" not-null="true" />

    <bag name="Contestants" cascade="save-update" inverse="true" lazy="false" batch-size="20" >
      <key column="Race"/>
      <one-to-many class="Racer"/>
    </bag>
  </class>
</hibernate-mapping>


<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
     assembly="Foo"
     namespace="Foo">
  <class name="Racer" table="Racers">
    <id name="Id">
      <generator class="guid"/>      
    </id>
    <many-to-one name="Race" foreign-key="FK_Racer_has_race" not-null="true" cascade="save-update" />
    <property name="Lane" not-null="true" />
    <many-to-one name="Driver" foreign-key="FK_Racer_has_driver" cascade="save-update" lazy="false" fetch="join" />
    <property name="FinishPosition" />
    <property name="Finished" not-null="true" />
  </class>
</hibernate-mapping>

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="Foo"
                   namespace="Foo">
  <class name="Driver" table="Drivers">
    <id name="Id">
      <generator class="hilo"/>
    </id>
    <property name="Name" not-null="true" length="32" unique="true" index="IDX_Driver_Name" />
  </class>
</hibernate-mapping>

And the query code:

public IList<Race> GetMostRecentRacesForEvent( int eventId, int firstRaceToFetch, int count ) {
    DetachedCriteria criteria = DetachedCriteria.For( typeof( Race ) ).
        CreateAlias( "Event", "event" ).
        Add( Restrictions.Eq( "event.EventId", eventId ) ).
        AddOrder<Race>( r => r.Time, Order.Desc ).
        SetResultTransformer( new DistinctRootEntityResultTransformer() ).
        SetFirstResult( firstRaceToFetch ).
        SetMaxResults( count );
    return this.ExecuteListQuery<Race>(criteria); }

With paging set to 3 races/page, here's the SQL it generates:

SELECT TOP 3 Id2_1_, RaceId2_1_, Year2_1_, IsValid2_1_, Time2_1_, Event2_1_, Id1_0_, EventId1_0_ FROM (SELECT ROW_NUMBER() OVER(ORDER BY __hibernate_sort_expr_0__ DESC) as row, query.Id2_1_, query.RaceId2_1_, query.Year2_1_, query.IsValid2_1_, query.Time2_1_, query.Event2_1_, query.Id1_0_, query.EventId1_0_, query.__hibernate_sort_expr_0__ FROM (SELECT this_.Id as Id2_1_, this_.RaceId as RaceId2_1_, this_.Year as Year2_1_, this_.IsValid as IsValid2_1_, this_.Time as Time2_1_, this_.Event as Event2_1_, event1_.Id as Id1_0_, event1_.EventId as EventId1_0_, this_.Time as __hibernate_sort_expr_0__ FROM Races this_ inner join Events event1_ on this_.Event=event1_.Id WHERE event1_.EventId = @p0) query ) page WHERE page.row > 0 ORDER BY __hibernate_sort_expr_0__ DESC

2nd query:

SELECT contestant0_.Race           as Race2_,
       contestant0_.Id             as Id2_,
       contestant0_.Id             as Id0_1_,
       contestant0_.Race           as Race0_1_,
       contestant0_.Lane           as Lane0_1_,
       contestant0_.Driver         as Driver0_1_,
       contestant0_.FinishPosition as FinishPo5_0_1_,
       contestant0_.Finished       as Finished0_1_,
       driver1_.Id                 as Id3_0_,
       driver1_.Name               as Name3_0_
FROM   Racers contestant0_
       left outer join Drivers driver1_
         on contestant0_.Driver = driver1_.Id
WHERE  contestant0_.Race in ('4157280d-be8d-44be-8077-a770ef7cd394' /* @p0 */,'74e1bfaa-9926-43c7-8b17-e242634dc32f' /* @p1 */,'e1e86b67-2c37-4fbe-8793-21e84a6e4be4' /* @p2 */)

Answer 1

Try this:

DetachedCriteria criteria = DetachedCriteria.For( typeof( Race ), "race" )
    .CreateAlias("Event", "event")
    .CreateAlias("race.Contestants", "contestant")
    .SetFetchMode("race.Contestants", FetchMode.Join)
    .SetFetchMode("contestant.Driver", FetchMode.Join)
    .Add( Restrictions.Eq( "event.EventId", eventId ) )
    .AddOrder<Race>( r => r.Time, Order.Desc )
    .SetResultTransformer( new DistinctRootEntityResultTransformer() )
    .SetFirstResult( firstRaceToFetch )
    .SetMaxResults( count );
return this.ExecuteListQuery<Race>(criteria); }

It should make a single query and join the races, events and contestants.

You shouldn't use FetchMode.Join too much, the returning result set is larger. (It includes the race and event information as many times as you have contestants.)

Answer 2

Stefan (to move out of the comments because this is becoming not very usable at this moment).

I redid the code according to your suggestion (without changing any mapping) and now, the query returns just one Race - the first out of three (paging is set to 3 races/page) matched by the query.

When I remove the

SetResultTransformer( new DistinctRootEntityResultTransformer() )

I get three items, but it's the same value three times, which is... weird. Any ideas?

Answer 3

I think it creates multiple queries to avoid duplicates creeping into the paging query. (but I'm not sure, I haven't seen the nhibernate internal sourcecode). Paging over a set which contains duplicates for the entity to fetch because joins were used is actually useless, so you need to be sure the resultset returned is without duplicates. I think nhibernate opts for 2 or more queries here instead of 1 query.