LINQ to NHibernate does not JOIN with subclass, whereas QueryOver does

Question

I had this QueryOver query:

        var result = session.QueryOver<TopicSelection>()
            .Where(x => x.LacId == lac && x.RemoveDate == null)
            .List();

In a nutshell: TopicSelection is a base class, and one subclass has many-to-one property, with lazy=false and fetch=join.

When I used QueryOver, NHibernate created nice join and fetched additional data from many-to-one table. Which is great, one query is issued and I get everything.

When I changed it to LINQ to NHibernate:

        var result = session.Query<TopicSelection>()
            .Where(x => x.LacId == lac && x.RemoveDate == null)
            .ToList();

executed query does not contain JOIN. What is more, every time a many-to-one property is needed, an additional select is issued.

Is is a bug in LINQ in NHibernate? Can I instruct LINQ to NHibernate query to fetch data for subclass?

Radim Köhler Radim Köhler · Accepted Answer · 2014-08-07T15:10:22

What you are experiencing is a "NHibernate LINQ implementation" as is today. We can effectively do two things.

In case, that we can adjust the query to ask for a subclass, or the many-to-one property is (could be) declared on base TopicSelection, we can use .Fetch()

var result = session
     //.Query<TopicSelection>()
     .Query<TopicSelectionSubClass>()
     .Where(x => x.LacId == lac && x.RemoveDate == null)
     .Fetch(x => x.Category ) // the many-to-one property joined
     .ToList();

The second approach is even better. I would suggest that almost in any case. I.e. instead of any many-to-one mapping with lazy="false" fetch="join" - let's use:

19.1.5. Using batch fetching

Change the class mapping like this:

<class name="Category" batch-size="25" ...

That will change the 1+N select into 1+1 (or 1+2). Small cite from docs:

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.

Batch fetching for classes/entities is easier to understand. Imagine you have the following situation at runtime: You have 25 Cat instances loaded in an ISession, each Cat has a reference to its Owner, a Person. The Person class is mapped with a proxy, lazy="true". If you now iterate through all cats and call cat.Owner on each, NHibernate will by default execute 25 SELECT statements, to retrieve the proxied owners.

LINQ to NHibernate does not JOIN with subclass, whereas QueryOver does

1 Answers