Nhibernate SetFetchMode - Inner vs Left Outer Join

Question

All,

I have an entity called Client that has an association to an entity called Region as:

Client -> Region

All my entities are Lazy loaded (nhibernate default setting).

Region in Client is mapped as NotNull = false:

[ManyToOne(Column = "region_id",
                ClassType = typeof(Region),
                NotNull = false)]
    public virtual Region Region
    {
        get { return _region; }
        set { _region = value; }
    }

When I create client criteria and set the FetchMode(FetchMode.Join), the generated select is an inner join, but I expected and left outer join since Region can be NULL.

The above happens DEPENDS on how the criteria is created. If I create the criteria as in Ex 1, I get correct SQL generated and Region is left outer joined, if I create the criteria as in Ex 2, I get the incorrect SQL generated, the Region is inner joined.

Ex 1) Correct SQL

ICriteria c = s.Session.CreateCriteria<Client>();
    c.SetFetchMode("Region", NHibernate.FetchMode.Join);
    IList<Client> list2 = c.List<Client>();

SELECT *  FROM Companies this_  left outer join Code_Region_Types region2_ on this_.region_id=region2_.entity_id

Ex 2) Incorrect SQL

ICriteria c = s.Session.CreateCriteria<Client>();
    ICriteria subC = c.CreateCriteria("Region");
    c.SetFetchMode("Region", NHibernate.FetchMode.Join);
    IList<Client> list2 = c.List<Client>();

SELECT * FROM Companies this_ inner join Code_Region_Types region1_ on this_.region_id=region1_.entity_id

In ex 2), the line which creates a sub-criteria

ICriteria subC = c.CreateCriteria("Region");

messes up the join clause.

This produces incorrect result since some clients may have no Region and therefore are not included in the query.

It appears the only fix for this is to specify explicitly the join on the sub-criteria:

ICriteria subC = c.CreateCriteria("Region", JoinType.LeftOuterJoin)

The above fixes the issue. Is this what Nhibernate expects?

Radim Köhler Radim Köhler · Accepted Answer · 2014-09-19T05:45:47

What you are experiencing is absolutely correct. And your solution is really the proper one.

The call to:

criteria.CreateCriteria(associationPath);

in fact does internally use INNER JOIN (see here):

public ICriteria CreateCriteria(string associationPath)
{
    return CreateCriteria(associationPath, JoinType.InnerJoin);
}

So, this way the Query is defined. It will be INNER JOIN. The Fetch mode, is then driven by result of that Criteria and its SubCriteria - i.e. only found results are taken into account.

But as you've found out, we can simply change that by explicit call:

ICriteria subCriteria = criteria
     .CreateCriteria(associationPath, JoinType.LeftOuterJoin)

And that will do what expected...

Nhibernate SetFetchMode - Inner vs Left Outer Join

1 Answers