nhibernate generate left outer join on many-to-one entity

Question

i'm using nHibernate 2.1.2 and relized that nhibernate will generate left outer join on nested many-to-one entities. it seems start generate left-outer-join on 3rd nested note onwards which start from entity Organization. i have set following in the mapping file to force use inner-join, has anything i missed out in the mapping file? really hope somebody could give me a hint on this. appreciate any helps!

lazy="false" fetch="join"

Example Entites and Relationships: Sales Record - Employee - Organization

nhibernate generate:

select...
from sales 
inner join employee
left outer join organization

Sales.hbm.xml

<many-to-one name="Employee" insert="true" update="true" access="field.pascalcase-underscore" not-null="true" lazy="false" fetch="join"/>
<column name="EmployeeId" not-null="true"/>
</many-to-one>

Employee.hbm.xml

<many-to-one name="Organization" insert="true" update="true" access="field.pascalcase-underscore" not-null="true" lazy="false" fetch="join"/>
<column name="OrgId" not-null="true"/>
</many-to-one>

Answer 1

If NHibernate does an inner join you don't ID from a child and ID from a parent table (but they're the same).

Example:

  TableParent (ID, Name)
  TableChild (ID, ID_TableParent, ....)

If nHibernate does an inner join, you get:

 select c.ID, c.ID_TableParent, p.Name
 from TableChild c
 inner join TableParent p on p.ID = c.ID_TableParent

If nHibernate does an left outer join, you get:

 select c.ID, c.ID_TableParent, p.ID, p.Name
 from TableChild c
 left outer join TableParent p on p.ID = c.ID_TableParent

And because of the inner workings of NHibernate it can then create 2 entities from the second query. One entity for TableChild and one for TableParent.

In the first query you'd only get TableChild entity and in some cases the p.Name would be ignored (probalby on the second level) and it would requery the database on checking the property that references TableParent.

I found out this when I wanted to load a tree structure with only one hit to the database:

public class SysPermissionTree
{
    public virtual int ID { get; set; } 
    public virtual SysPermissionTree Parent { get; set; }
    public virtual string Name_L1 { get; set; }
    public virtual string Name_L2 { get; set; }

    public virtual Iesi.Collections.Generic.ISet<SysPermissionTree> Children { get; private set; }
    public virtual Iesi.Collections.Generic.ISet<SysPermission> Permissions { get; private set; }

    public class SysPermissionTree_Map : ClassMap<SysPermissionTree>
    {
        public SysPermissionTree_Map()
        {
            Id(x => x.ID).GeneratedBy.Identity();

            References(x => x.Parent, "id_SysPermissionTree_Parent");
            Map(x => x.Name_L1);
            Map(x => x.Name_L2);
            HasMany(x => x.Children).KeyColumn("id_SysPermissionTree_Parent").AsSet();
            HasMany(x => x.Permissions).KeyColumn("id_SysPermissionTree").AsSet();
        }
    }
}

And the query I used was this:

SysPermissionTree t = null;
SysPermission p = null;

return db.QueryOver<SysPermissionTree>()
         .JoinAlias(x => x.Children, () => t, NHibernate.SqlCommand.JoinType.LeftOuterJoin)
         .JoinAlias(() => t.Permissions, () => p, NHibernate.SqlCommand.JoinType.LeftOuterJoin) 
         .Where(x => x.Parent == null)
         .TransformUsing(Transformers.DistinctRootEntity)
         .List();

With NHibernate.SqlCommand.JoinType.LeftOuterJoin. Because if I used InnerJoin the structure didn't load with only one query. I had to use LeftOuterJoin, so that NHibernate recognized the entities.

SQL Queries that executed were:

SELECT this_.ID as ID28_2_, this_.Name_L1 as Name2_28_2_, this_.Name_L2 as Name3_28_2_, this_.id_SysPermissionTree_Parent as id4_28_2_, t1_.id_SysPermissionTree_Parent as id4_4_, t1_.ID as ID4_, t1_.ID as ID28_0_, t1_.Name_L1 as Name2_28_0_, t1_.Name_L2 as Name3_28_0_, t1_.id_SysPermissionTree_Parent as id4_28_0_, p2_.id_SysPermissionTree as id4_5_, p2_.ID as ID5_, p2_.ID as ID27_1_, p2_.Name_L1 as Name2_27_1_, p2_.Name_L2 as Name3_27_1_, p2_.id_SysPermissionTree as id4_27_1_ FROM [SysPermissionTree] this_ left outer join [SysPermissionTree] t1_ on this_.ID=t1_.id_SysPermissionTree_Parent left outer join [SysPermission] p2_ on t1_.ID=p2_.id_SysPermissionTree WHERE this_.id_SysPermissionTree_Parent is null
SELECT this_.ID as ID28_2_, this_.Name_L1 as Name2_28_2_, this_.Name_L2 as Name3_28_2_, this_.id_SysPermissionTree_Parent as id4_28_2_, t1_.ID as ID28_0_, t1_.Name_L1 as Name2_28_0_, t1_.Name_L2 as Name3_28_0_, t1_.id_SysPermissionTree_Parent as id4_28_0_, p2_.ID as ID27_1_, p2_.Name_L1 as Name2_27_1_, p2_.Name_L2 as Name3_27_1_, p2_.id_SysPermissionTree as id4_27_1_ FROM [SysPermissionTree] this_ inner join [SysPermissionTree] t1_ on this_.ID=t1_.id_SysPermissionTree_Parent inner join [SysPermission] p2_ on t1_.ID=p2_.id_SysPermissionTree WHERE this_.id_SysPermissionTree_Parent is null

where the first query is left outer join and we get 2 extra fields: t1_.id_SysPermissionTree_Parent as id4_4_, t1_.ID as ID4_

So what I'm trying to tell you is that if you use NHibernate then left outer join is sometimes a must to comply with inner workings of NHibernate.