Getting multiple queries when transaction is committed, why?

Question

public Parent GetByName(string Name)
{
    return _session.CreateCriteria<Parent>()
        .Add(Restrictions.Eq("Name", Name))
        .SetFetchMode("Children", FetchMode.Eager)
        .SetResultTransformer(new DistinctRootEntityResultTransformer())
        .UniqueResult<Parent>();
}

public ParentDetailVM GetMeAParent(string Name)
{
    Parent parent;
    using (var tx = _session.BeginTransaction())
    {
        //This works well, one single query loading
        //both parent and children
        parent = _parentRepository.GetByName(Name);

        //If I include this as suggested by NHProfiler
        //it all of the sudden sends a new query for each child
        //and a query for the grandchildren collection
        tx.Commit();
    }

    return Mapper.Map<Parent, ParentDetailVM>(parent);
}

I have checked to make sure that nothing in the mapping files has been set to eager load. I can't figure out why it works if I leave off the transaction commit but otherwise it issues N more queries. Anyone know why this might be happening?

Answer 1

If you examine _session.IsDirty() before committing the transaction my bet is that it will return true. When the transaction is committed the session is flushed and, for some reason, the child objects are loaded to cascade the change.

This is a problem known as "ghosting" or phantom updates. A typical scenario is that a database column is a nullable int but the corresponding object property is non-nullable. When a record with a null value is retrieved, NHibernate sets the property value to 0 and the object is therefore dirty.

The quickest way to troubleshoot this is to turn on dynamic-update for the object using dynamic-update="true" in XML mappings, DynamicUpdate() in fluent, and use a profiler or logging to see which columns are being updated after the select. There's also a utility called Ghostbuster that you can include in unit tests.