NHibernate join does not fully populate objects within transactions

Question

We have a situation where a transaction is started on an NHibernate session, some rows are populated into a couple of tables, and a query is executed which performs a join on the two tables.

Models:

public class A
{
    public virtual string ID { get; set; } // Primary key
    public IList<B> Bs { get; set; }
}

public class B
{
    public virtual string ID { get; set; } // Foreign key
}

NHibernate maps:

public class AMap: ClassMap<A>
{
    public AMap()
    {
        Table("dbo.A");
        Id(x => x.ID).Not.Nullable();

        HasMany(u => u.Bs).KeyColumn("ID");
    }
}

public class BMap: ClassMap<B>
{
    public BMap()
    {
        Table("dbo.B");

        Map(x => x.ID, "ID").Not.Nullable();
    }
}

A transaction is started and the following code is executed:

var a1 = new A
{
    ID = "One"
};
session.Save(a1);

var a2 = new A
{
    ID = "Two"
};
session.Save(a2);
session.Flush();

var b1 = new B
{
    ID = a1.ID
};
session.Save(b1);

var b2 = new B
{
    ID = a2.ID
};
session.Save(b2);
session.Flush();

A a = null;
B b = null;
var result = _session.QueryOver(() => a)
    .JoinQueryOver(() => a.Bs, () => b,JoinType.LeftOuterJoin)
             .List();

The result is a list of A. In the list, objects of A do not have Bs populated.

Although this example is simplified, the actual objects in question have additional properties associated with corresponding table columns; all those properties populate as expected; the issue is confined to the property mapped as HasMany (foreign key association).

If the table is populated first, and then the query is performed (either as separate processes or in consecutive transactions), the objects of A do have their Bs correctly populated. In other words, it seems as though queries executed in a transaction are not able to see the complete effect of inserts previously performed within the same transaction.

Inspection of the SQL generated by NHibernate confirms that it correctly performed all the inserts and correctly formulated the join query; it appears that it simply did not correctly populate the objects from the query result.

Are there any special steps required to ensure that database inserts/updates performed via NHibernate are fully visible to subsequent fetches in the same transaction?

Answer 1

HasMany(u => u.Bs).KeyColumn("ID");

looks wrong to me. The id of a one-to-many relation should be A_ID.

You do lots of strange things in your code. I hope your real code doesn't look like this. You should not set foreign keys directly. They are managed by NH. You should not Flush all the time. Normally you never flush.

Also note that the left outer join is not used to populate the list of Bs in A. (There is no information for NHibernate that this would be a valid option.) There are mapping tricks to load entities and one of its collections in one query, but this is most of the time not such a good idea and I suggest to not try this unless you really know NH and how queries are processed very well. You'll only get the same A multiple times and some performance problems if you do not break it completely. If you are afraid of the N+1 problem (I hope you are), use batch-size instead.

Answer 2

Figured out the solution. The gist of it is to add the "child" items to the "parent" and then save that.

So... classes now look like:

    public class A
    {
        public virtual string ID { get; set; } // Primary key
        public virtual IList<B> Bs { get; set; }
    }

    public class B
    {
        public virtual A A { get; set; } // Foreign key now expressed as reference to "parent" object instead of property containing key value
    }

ClassMaps for both parent and child express the relationship as object/list:

    public class AMap: ClassMap<A>
    {
        public AMap()
        {
            Table("dbo.A");
            Id(x => x.ID).Not.Nullable();

            HasMany(u => u.Bs).KeyColumn("ID").Cascade.SaveUpdate();
        }
    }

    public class BMap: ClassMap<B>
    {
        public BMap()
        {
            Table("dbo.B");

            Map(x => x.ID, "ID").Not.Nullable();
            References(x => x.A, "ID").Not.Nullable();
        }
    }

Finally, data is saved by constructing the objects and their relationship before saving them i.e. relationships are saved with the objects:

    var a1 = new A
    {
        ID = "One"
    };

    var b1 = new B
    {
        A = a1          
    };

    a1.Bs = new []{b1};

    session.Save(a1);

    var a2 = new A
    {
        ID = "Two"
    };

    var b2 = new B
    {
        A = a2
    };

    a2.Bs = new []{b2};

    session.Save(a2);
    session.Flush();

This query:

    A a = null;
    B b = null;
    var result = _session.QueryOver(() => a)
        .JoinQueryOver(() => a.Bs, () => b,JoinType.LeftOuterJoin)
                 .List();

Now returns the expected result, and within the same session/transaction.