NHibernate many-to-many and SELECT N+1 problem

Question

I have 4 database tables (Channel, User, Message, User2Channel) and according entity classes:

class **Channel** { 
 int ChannelId {get;set;}
 int ISet<User> UsersToChannel {get;set;}
 ...
}
class **Message** {
 int MessageId {get;set;}
 Channel Channel {get;set;}
 User User {get;set;}
 ...
}
class **User**{
 int UserId {get;set;}
 ISet<Channel> Channels  {get;set;} 
 ...
}

I use fluentnhibernate for mapping:

class **ChannelMap** {
    ChannelMap(){
       ...
       HasManyToMany(x => x.UsersInChannel)
                .AsSet()
                .Cascade.All().Inverse()
                .Table("User2Channel")
                .Not.LazyLoad();
    }
}
class **UserMap** {
    UserMap(){
       HasManyToMany(x => x.Channels)
                .AsSet()
                .Cascade.All()
                .Table("User2Channel")
                .Not.LazyLoad(); 
    }
}

I load messages in this code:

...
var query = session.CreateQuery(@"select m from Message m"); 
var msgs = query.List<Message>();
...

At profiler I see many queries like this:

SELECT ... FROM User2Channel WHERE ChannelId=654
SELECT ... FROM User2Channel WHERE ChannelId=655 
etc

Please! Help me! How I can solve this problem? If I have many thousands Channels - I get also many queries into database!

Answer 1

You could load them using batches. This is the less intrusive option (means: you don't need to do anything in your domain code).

I don't know fluent. So this is the xml mapping you need:

<set name="Channels" ... batch-size="50">
  ...
</set>

This lets NH prefetch 50 channels at once. It will query them like this:

SELECT ... FROM User2Channel WHERE ChannelId IN (654, 655, 656, ... 704)

This makes the N+1 a N/50+1.