Fluent nhibernate map multiple tables

Question

I have an UserEntity that mapped like and got Cannot simultaneously fetch multiple bags. error

public UserEntityMap : ClassMap<UserEntity>
{
   //Other properties
   HasMany(x => x.Addresses).KeyColumn("User_id").Fetch.Join();
   HasMany(x => x.Roles).KeyColumn("User_id").Fetch.Join();
}

I want to get both addresses and roles when i create a query for userentity. What should i do to see an output like

Select * from UserEntity u 
  join Addresses a on u.id=a.User_id 
  join Roles r on u.id=r.User_id where u.id=?

Answer 1

There is no way how to generate such SELECT statement.

I would suggest to use batch fetching. See these for more details:

• How to Eager Load Associations without duplication in NHibernate?
• How to implement batch fetching with Fluent NHibernate when working with Oracle?

The adjusted mapping:

public UserEntityMap : ClassMap<UserEntity>
{
   //Other properties
   HasMany(x => x.Addresses)
       .KeyColumn("User_id").Fetch.Join()
       .BatchSize(100);
   HasMany(x => x.Roles)
       .KeyColumn("User_id").Fetch.Join()
       .BatchSize(100);
}

This will allow to query the root entity and with just few SELECTS get also their collections (no 1 + N issue)

Also check What is the solution for the N+1 issue in hibernate?