NHibernate specific mapping ("select using grand-parent")

Question

I have such domain structure (example, pseudo code):

class Room 
{
  Id: long
  Shelves: list of Shelf
}

class Shelf 
{
  Id: long
  Room: Room class (room_id column)
  Books: list of Book
}

class Book 
{
    Id: long (id column)
    Shelf: Shelf class (shelf_id column)
    Room: Room class (room_id column)
    Title: string (title column)
}

Every collection is lazy. I'd like to know is there a way to fetch all books for a room (without subselect) when I access a Shelf's Books property in lazy manner.

When I get Room, nhibernate fetches only room (select * from rooms r where ...), when I access Shelves property, nhibernate fetches all shelves for this room (select * from shelves s where s.room_id = X), when I access Books property it loads books for the only one shelf (what is normal in general), but...

In my case if I access Book, there is high probability that I will work with other books of this room too, so if I touch book I want to preload all books for this room and put them on theirs shelves.

The most closest feature of nhibernate is fetch='subselect'. But there is unnecessary select in my case because I can fetch books for this room by room_id: select * from books b where b.room_id = x.

Is there a way to do this?

Thanks!

score 1 · Answer 1 · edited May 23 '17 at 12:31

The native way built into NHibernate is called Batch Fetching:

19.1.5. Using batch fetching

NHibernate can make efficient use of batch fetching, that is, NHibernate can load several uninitialized proxies if one proxy is accessed (or collections. Batch fetching is an optimization of the lazy select fetching strategy. There are two ways you can tune batch fetching: on the class and the collection level.

More details (except the doc link above) could be found here:

score 0 · Accepted Answer · answered Nov 08 '15 at 14:22

I've found a more elegant solution. It turns out I can define sql-query for a bag/map...

It looks like:

<hibernate-mapping...>
    <class name="Shelf">
        <many-to-one name="Room" column="room_id"... />

        <bag name="Books">
            ...

            <loader query-ref="ShelvesQuery" />
       </bag>
    </class>

    <sql-query name="ShelvesQuery">
        <load-collection alias="book" role="Shelf.Books" />

        <![CDATA[
          select b.id
                 b.room_id,
                 b.shelf_id,
                 b.title
            from books b
           where b.room_id = :room_id
       ]]>
    </sql-query>
</hibernate-mapping>

It works like a charm!

NHibernate specific mapping ("select using grand-parent")

2 Answers2