NHibernate eager load with FetchMany for subclass only

Question

I have a following (simplified) hierarchy:

class Account
{
    AccountType Type;
    long Id;
    string Name;
}

public enum AccountType
{
    UsualAccount = 0,
    SpecialAccount = 1
}

class SpecialAccount : Account 
{
    List<SpecialItem> SpecialItems;
}

class SpecialItem
{
    long Id;
    string Name;
}

To help NHibernate to detect subclass SpecialItem, I use the following code in the Account mapping:

<discriminator column="Type" formula="Type"/>

and SpecialItems have lazy="false" set in the mapping, but as far as I know, it is ignored during LINQ queries.

Now when I use LINQ and call

Session.Query<Account>().ToList();

I see that SpecialItems are fetched in a separate query. I would like to load them eagerly.

I could do that using

Session.Query<Account>().FetchMany(a => a.SpecialItems).ToList();

but there is a problem - SpecialItems is the property of the SpecialAccount. So I somehow need FetchMany to work only if a is a SpecialAccount class.

I tried even something as ugly as:

Session.Query<Account>().
    FetchMany(a => (a is SpecialAccount ? (a as SpecialAccount ).SpecialItems : null));

but NHibernate still selected SpecialItems in a separate query.

How can I make NHibernate select SpecialItems for SpecialAccount in a single query?

Additional information

I am using MS SQL Express 2012 and NHibernate configuration has the following lines:

<property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
<property name="adonet.batch_size">50</property>
<property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property>

NHibernate.dll version 3.2.0.4000

Here are the queries generated by NHibernate for one existing SpecialAccount with Id=2, as they appear in MS SQL Profiler (with selected events RPCCompleted, Exec Prepared SQL, SQL:StmtCompleted):

select account0_.Id as Id5_, account0_.Type as Type5_, account0_.Name as Name5_, account0_.Type as clazz_ from tAccount account0_ 

exec sp_executesql N'SELECT specialite0_.AccountId as Id1_, specialite0_.Id as specialite1_1_, specialite0_.Id as specialite1_13_0_, specialite0_.Name as Name13_0_ 
FROM tSpecialItem specialite0_ WHERE specialite0_.Id=@p0',N'@p0 bigint',@p0=2

score 2 · Answer 1 · answered Aug 28 '12 at 14:54

2

You can't to a "conditional fetch". You need at least two queries.

You can, however, group them using a Future LINQ query, so it would be effectively one roundtrip:

session.Query<SpecialAccount>().FetchMany(x => x.SpecialItems).ToFuture();
var accounts = session.Query<Account>().ToFuture();

Both queries run when you enumerate accounts. We don't store the results of the first query explicitly, but the SpecialAccounts are loaded in memory with their corresponding SpecialItems collections, so there are no extra DB calls.

answered Aug 28 '12 at 14:54

Diego Mijelshon

52,548
16
116
154

Thanks, ToFuture seems a good idea, but for some reason it did not change the SQL generated. Profiler still shows two "exec sp_executesql" queries. Maybe ToFuture does not work with Fetch as expected (as they say here http://stackoverflow.com/questions/5143480/good-behaviour-for-eager-loading-multiple-siblings-and-grandchildren-cousins )... – JustAMartin Aug 28 '12 at 16:04
It WorksOnMyMachine™, using NH 3.3.1 and SQL2008. Just one query sent to the DB (and it doesn't have sp_executesql). Wanna post your profiler output as an edit to your question? – Diego Mijelshon Aug 28 '12 at 16:16
Added more information about my configuration and the queries in the SQL Profiler. – JustAMartin Aug 28 '12 at 18:57
The queries you are using are different from the ones I provided. Could you please post your queries, besides the resulting SQL? – Diego Mijelshon Aug 28 '12 at 19:37
Actually my classes have some more fields (just simple CLR types) in them, so I cleaned up the queries to keep the bare minimum. I tried the LINQ code almost exactly as you posted and added accounts.ToList() to trigger the SQL, but still I see two queries in the Profiler. I guess, I'll have to create a completely clean project from scratch to match my simplified example, so I can see better what might be different. – JustAMartin Aug 28 '12 at 19:45

NHibernate eager load with FetchMany for subclass only

1 Answers1