nhibernate 在多对一实体上生成左外连接

Question

我正在使用 nHibernate 2.1.2 并意识到 nhibernate 将在嵌套的多对一实体上生成左外连接。似乎从实体组织开始的第三个嵌套注释开始生成左外连接。我在映射文件中设置了以下内容以强制使用内连接，映射文件中是否遗漏了任何内容？真的希望有人能给我一个提示。感谢任何帮助！

lazy="false" fetch="join"

示例实体和关系： 销售记录 - 员工 - 组织

nhibernate 生成：

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

如果 NHibernate 执行内部联接，则您不会从子表中获取 ID，也不会从父表中获取 ID（但它们是相同的）。

示例：

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

如果 nHibernate 执行内连接，您将得到：

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

如果 nHibernate 执行左外连接，您将得到：

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

由于 NHibernate 的内部工作原理，它可以从第二个查询创建 2 个实体。一个实体用于 TableChild，一个实体用于 TableParent。

在第一个查询中，您只会获得 TableChild 实体，在某些情况下，p.Name 将被忽略（可能在第二级），并且它将在检查引用 TableParent 的属性时重新查询数据库。

当我想加载一个只需要一次数据库命中的树结构时，我发现了这一点：

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();
        }
    }
}

我使用的查询是这样的：

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。因为如果我使用 InnerJoin，结构不会仅通过一个查询加载。我必须使用 LeftOuterJoin，以便 NHibernate 识别这些实体。

执行的 SQL 查询是：

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

第一个查询是左外连接，我们得到 2 个额外字段：t1_.id_SysPermissionTree_Parent as id4_4_，t1_.ID as ID4_

所以我想告诉你的是，如果你使用 NHibernate 那么左外连接有时，为了遵守 NHibernate 的内部运作方式，join 是必须的。