Search code examples
nhibernatefluent-nhibernate-mapping

NHIbernate "References" Property generating Select + 1 even though correctly OUTER JOINING

Ok, I am a little stumped on this NHibernate query. The confusion is around PasswordResetToken.

Firstly, here is the mapping:

public ContactMap()
    {
        Table("Contact");
        Id(x => x.ContactId, "ContactId").Unique().GeneratedBy.Increment();
        Map(x => x.EmailAddress);
        ...
        Map(x => x.JobTitle);

        References(x => x.PasswordResetToken, "EmailAddress")
            .PropertyRef(x => x.EmailAddress)
            .Cascade.None()
            .Not.LazyLoad()
            .Not.Update();


        HasMany(x => x.Roles)
            .Table("tblContactRole").KeyColumn("ContactId").Element("Role", part => part.Type<global::NHibernate.Type.EnumStringType<ContactRoles>>())
            .AsSet()
            .Not.LazyLoad();
    }

Now here is the query:

public IList<Contact> GetContacts(int id)
    {
        var contacts = Session.CreateCriteria<Contact>()
                           .Add(Restrictions.Eq("Id", id))
                           .Add(Restrictions.Eq("IsActive", true))
                           .SetFetchMode("Roles", FetchMode.Eager)
                           .SetFetchMode("PasswordResetToken", FetchMode.Eager)
                           .SetResultTransformer(CriteriaSpecification.DistinctRootEntity)
                           .List<Contact>();

        return contacts;
    }

My understanding is that FetchMode.Eager means a JOIN is used instead of a SUBSELECT, so there isn't any reason there for extra calls to the db to appear.

A correct SQL query is run returning all the information required to hydrate a Contact as evidenced from the screenshot from NHProf (the highlighted query) (dont' worry about different table names etc - I have sanitized the code above):

NHProf

What I don't understand is why on earth dozens of separate selects to the PasswordResetToken table are generated and run?? One of these queries is only generated for every contact that doesn't have a PasswordResetToken (ie. the first query returns nulls for those columns) - not sure what this has to do with it.

A contact might or might not have a few roles (superfluous to this issue) and similarly, may or may not have exactly one PasswordResetToken.

The DB is a little dodgy with few foreign keys. The link between Contact and PasswordResetToken in this case is a simple shared column "EmailAddress".

All these queries are generated on the running of that single line of code above (ie. that code is not in a loop).

Let me know if I am missing any info.

What should I be googling?

Solution

  • It's a bug. I would try to get it to work with just two queries, although from the bug report it sounds like that will be a challenge.

    The attached test shows that a many-to-one association referencing an unique property (instead of the Id) results in a select n+1 problem. Although the first statement contains the correct join, all associated entities are fetched one by one after the join select. (Entities with the same value in the unique column are even fetched more than once.)

    The interesting point is that this bug only occurs if the referenced entities are already in the session cache. If they are not, no additional select statements are created.