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=?

score 2 · Accepted Answer · edited May 23 '17 at 11:44

2

There is no way how to generate such SELECT statement.

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

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?

edited May 23 '17 at 11:44

Community

1
1

answered Oct 19 '15 at 11:29

Radim Köhler

117,533
47
231
321

Thanks Radim. Should i avoid to use Join on mapping ? Is it better way to use JoinAlias when querying over UserEntity. Like your GetCats() example, when i will have userlist i will query for childs also wont i? – eyildiz Oct 19 '15 at 11:52
My approach is - 1) do join `many-to-one` (star schema), use projections (e.g. http://stackoverflow.com/q/24246277/1679310) 2) **NEVER** join `one-to-many`. Use sub-queries to filter over collections (e.g. http://stackoverflow.com/q/23772548/1679310) and let NHibernate to load them in batches... – Radim Köhler Oct 19 '15 at 11:54

Fluent nhibernate map multiple tables

1 Answers1