NHibernate - three bidirectional relations between three classes gives N+1 -

NHibernate - three bidirectional relations between three classes gives N+1 -

i'm having bit complicated object model forms triangle. there user entity has collections of items , taxonomies. item has taxonomy, too. , convenience, wanted item , taxonomy know owner , taxonomy know item, if any. see diagram:

so makes 3 bi-directional relations. problem when map in nhibernate , asking user given id, i'm getting select n+1 problem.

at first, user loaded eagerly fetched items. taxonomies loaded eagerly fetched item connected it. , expected , defined in mappings. there n+1 queries load items related taxonomies.

this redundant parts of object graph loaded. thie problem disappears when create user-item relation unidirectional user side (there 2 queries, expected), don't want remove backward relationship. is possible have optimal fetching 3 relations bidirectional?

here mapping parts:

public class useroverride : iautomappingoverride<user> { public void override(automapping<user> mapping) { mapping.hasmany(x => x.items).inverse() .not.lazyload().fetch.join(); mapping.hasmany(x => x.taxonomies).inverse() .lazyload().fetch.select(); } } public class itemoverride : iautomappingoverride<item> { public void override(automapping<item> mapping) { mapping.references(x => x.taxonomy); // many-to-one } } public class taxonomyoverride : iautomappingoverride<taxonomy> { public void override(automapping<taxonomy> mapping) { mapping.hasone(x => x.item).propertyref(x => x.taxonomy) .not.lazyload().fetch.join(); } }

and query database simplest possible way:

var user = session.get<user>(1);

because mappings effect queries, live rule mappings should changed eagerly load if entity never useful without other entity. in situation, if ever want users, , care less item , taxonomy records, doing database work no benefit.

i advise perform eager loading via other route- in query.

session.queryover<user>().where(u => u.id == 1) .join.queryover<items>(u => u.items) .join.queryover<taxonomy>(i => i.taxonomy) .transformusing(trasnformers.distinctrootentity);

nhibernate fluent-nhibernate bidirectional-relation