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

Question

I'm having bit complicated object model that forms a triangle. There is User entity that has collections of Items and Taxonomies. Item has a taxonomy, too. And for convenience, I wanted Item and Taxonomy to know its owner and Taxonomy to know its Item, if any. See diagram:

diagram

So this makes three bi-directional relations. My problem is when I map it in NHibernate like that and asking for user with given ID, I'm getting Select N+1 problem.

At first, User is loaded with eagerly fetched Items. Then Taxonomies are loaded with eagerly fetched Item connected to it. And this is as expected and as defined in mappings. But now there is N+1 queries to load Items related with Taxonomies.

queries

This is redundant as all parts of object graph was already loaded. Thie problem disappears when I make my User-Item relation unidirectional from User side (there are only 2 queries, as expected), but I don't want to remove that backward relationship. Is it possible to have optimal fetching with all three relations bidirectional?

Here are my 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 I query my database the simplest possible way:

var user = session.Get<User>(1);

score 1 · Accepted Answer · edited Jul 11 '11 at 12:52

1

Because mappings will effect all queries, I like to live by the rule that mappings should only be changed to eagerly load if an entity is NEVER useful without an other entity. In your situation, if you ever just want Users, and could care less about the Item and the Taxonomy records, you will be doing extra database work for no benefit.

I would advise you perform the eager loading via the other route- in your query.

Session.QueryOver<User>().Where(u => u.Id == 1)
    .join.QueryOver<Items>(u => u.Items)
    .Join.QueryOver<Taxonomy>(i => i.Taxonomy)
    .TransformUsing(Trasnformers.DistinctRootEntity);

edited Jul 11 '11 at 12:52

NOtherDev

9,542
2
36
47

answered Jul 11 '11 at 12:35

tom.dietrich

8,219
2
39
56

I wouldn't use `User` in any other scenario. Anyway, the result is similar. First query joins everything what is needed, second pulls `Items` separately and there is N+1 for `Items` once again. – NOtherDev Jul 11 '11 at 12:56
Did you remove the lazy loading specifications from the mappings prior to testing? – tom.dietrich Jul 11 '11 at 13:15
OK, I get it to run, both in query and mapping version. Anyway, I'm curious why it needs to issue separate query for loading `Taxonomy`'s `Items` if all the values should be already known... – NOtherDev Jul 11 '11 at 21:30

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

1 Answers1