c# - 我如何使用 Nhibernate 多次在同一张表上进行内部连接

Question

我想使用 Nhibernate QueryOver(下方)使用 nHibernate 代码(下方)实现所需的 sql。生成的查询不会产生任何错误，但是，它不会执行 inner join，因此，数据集使用 managerAlias 为那些属性返回 null 和 supervisorAlias。

编辑:我已经包含了所有相关的映射/实体类。

我怎样才能做到这一点？

所需的 sql:

select head.EmployeeID
        ,e1.EmployeeFirstName
        ,e1.EmployeeLastName
        ,head.SupervisorID
        ,e2.EmployeeFirstName
        ,e2.EmployeeLastName
        ,head.ManagerID
        ,e3.EmployeeFirstName
        ,e3.EmployeeLastName
from dbo.EvaluationHead head
inner join dbo.Employee e1 on head.EmployeeID = e1.EmployeeID
inner join dbo.Employee e2 on head.SupervisorID = e2.EmployeeID
inner join dbo.Employee e3 on head.ManagerID = e3.EmployeeID

我的应用程序中有以下代码:

                Employee employeeAlias = null;
                Employee managerAlias = null;
                Employee supervisorAlias = null;

                EvaluationHead headAlias = null;

                var query = session.QueryOver<EvaluationHead>(() => headAlias)
                                    .JoinQueryOver(() => headAlias.Employee, () => employeeAlias)
                                    .JoinQueryOver(() => headAlias.Manager, () => managerAlias)
                                    .JoinQueryOver(() => headAlias.Supervisor, () => supervisorAlias)
                                    .WhereRestrictionOn(() => headAlias.KRAApprovedDate).IsNotNull
                                    .SelectList(l => l
                                        .Select(h => h.EvaluationHeadID).WithAlias(() => dto.EvaluationHeadID)

                                        .Select(h => h.Employee.EmployeeID).WithAlias(() => dto.EmployeeID)
                                        .Select(h => employeeAlias.EmployeeFirstName).WithAlias(() => dto.EmployeeFirstName)
                                        .Select(h => employeeAlias.EmployeeMidName).WithAlias(() => dto.EmployeeMidName)
                                        .Select(h => employeeAlias.EmployeeLastName).WithAlias(() => dto.EmployeeLastName)

                                        .Select(h => h.EvaluationStartPeriod).WithAlias(() => dto.EvaluationStartPeriod)
                                        .Select(h => h.EvaluationEndPeriod).WithAlias(() => dto.EvaluationEndPeriod)

                                        .Select(h => h.ManagerID).WithAlias(() => dto.ManagerID)
                                        .Select(h => managerAlias.EmployeeFirstName).WithAlias(() => dto.ManagerFirstName)
                                        .Select(h => managerAlias.EmployeeLastName).WithAlias(() => dto.ManagerLastName)

                                        .Select(h => h.SupervisorID).WithAlias(() => dto.SupervisorID)
                                        .Select(h => supervisorAlias.EmployeeFirstName).WithAlias(() => dto.SupervisorFirstName)
                                        .Select(h => supervisorAlias.EmployeeLastName).WithAlias(() => dto.SupervisorLastName)

                                        .Select(h => h.DateCreated).WithAlias(() => dto.DateCreated))
                                    .TransformUsing(Transformers.AliasToBean(typeof(EvaluationHeadDTO)))
                                    .List<EvaluationHeadDTO>();

映射:

public EvaluationHeadMap()
{
    Id(x => x.EvaluationHeadID).GeneratedBy.Identity();
    Map(x => x.EmployeeID).Not.Insert().Not.Update();
    Map(x => x.ManagerID).Not.Insert().Not.Update();
    Map(x => x.SupervisorID).Not.Insert().Not.Update();

//other properties

    References(x => x.Employee).Column("EmployeeID").Cascade.None();
    References(x => x.Manager).Column("EmployeeID").Cascade.None(); ;
    References(x => x.Supervisor).Column("EmployeeID").Cascade.None();
}

---

public EmployeeMap()
{
    Id(x => x.EmployeeID).GeneratedBy.Identity();
    //other properties
    HasMany(x => x.Employees).KeyColumn("EmployeeID").Inverse();
    HasMany(x => x.Managers).KeyColumn("ManagerID").Inverse();
    HasMany(x => x.Supervisors).KeyColumn("SupervisorID").Inverse();
}

---

实体

public class EvaluationHead
{
    public virtual long EvaluationHeadID { get; set; }

    public virtual long EmployeeID { get; set; }
    public virtual Employee Employee { get; set; }

    public virtual long SupervisorID { get; set; }
    public virtual Employee Supervisor { get; set; }

    public virtual long ManagerID { get; set; }
    public virtual Employee Manager { get; set; }
}

---

public class Employee
{

    public virtual long EmployeeID { get; set; }
    public virtual string EmployeeFirstName { get; set; }
    public virtual string EmployeeMidName { get; set; }
    public virtual string EmployeeLastName { get; set; }

    public virtual ICollection<EvaluationHead> Employees { get; set; }
    public virtual ICollection<EvaluationHead> Supervisors { get; set; }
    public virtual ICollection<EvaluationHead> Managers { get; set; }

    public Employee()
    {
        Employees = new List<EvaluationHead>();
        Supervisors = new List<EvaluationHead>();
        Managers = new List<EvaluationHead>();
    }
}

Answer 1

映射似乎是罪魁祸首。您正在寻找的场景是最常见的场景之一。

所以，如果我们有这样的 EvaluationHead C#:

public class EvaluationHead
{
    ...
    // Employee
    public virtual Employee Employee   { get; set; }
    public virtual int      EmployeeID { get; set; }

    // Supervisor 
    public virtual Employee Supervisor   { get; set; }
    public virtual int      SupervisorID { get; set; }

    // Manager
    public virtual Employee Manager   { get; set; }
    public virtual int      ManagerID { get; set; }
}

EvaluationHead 的 (xml) 映射如下:

<class ...

 //<!-- Employee --> 
 <many-to-one name="Employee"   column="EmployeeID"   class="Employee" />
 <property  name="EmployeeID"   column="EmployeeID"   insert="false" update="false" />

 //<!-- Supervisor --> 
 <many-to-one name="Supervisor" column="SupervisorID" class="Employee" />
 <property  name="SupervisorID" column="SupervisorID" insert="false" update="false" />

 //<!-- Manager --> 
 <many-to-one name="Manager"    column="ManagerID"    class="Employee" />
 <property  name="ManagerID"    column="ManagerID"    insert="false" update="false" />

流畅的映射

public EvaluationHeadMap()
{
    Id(x => x.EvaluationHeadID).GeneratedBy.Identity();
    Map(x => x.EmployeeID).Not.Insert().Not.Update();
    Map(x => x.ManagerID).Not.Insert().Not.Update();
    Map(x => x.SupervisorID).Not.Insert().Not.Update();

//other properties

    References(x => x.Employee).Column("EmployeeID").Cascade.None();

    // Instead of this
    // References(x => x.Manager).Column("EmployeeID").Cascade.None(); ;
    // References(x => x.Supervisor).Column("EmployeeID").Cascade.None();

    // use this
    References(x => x.Manager).Column("ManagerID").Cascade.None(); ;
    References(x => x.Supervisor).Column("SupervisorID").Cascade.None();
}

那么，我们现在拥有的是:

1. 完全映射EvaluationHead(流畅的映射会类似)，
2. 映射表示 ID 列的 int 属性和
3. 对Employee表的真实引用(Employee、Supervisor、Manager)

我们可以像这样调整查询:

var query = session
  .QueryOver<EvaluationHead>(() => headAlias)
  .JoinQueryOver(() => headAlias.Employee, () => employeeAlias)

  // alias without EmployeeID
  .JoinQueryOver(() => headAlias.Manager, () => managerAlias)
  .JoinAlias(() => headAlias.Supervisor, () => supervisorAlias)

  // this is done via mapping
  //.Where(() => headAlias.SupervisorID == supervisorAlias.EmployeeID
  //                            && headAlias.ManagerID == managerAlias.EmployeeID)

  // .Where instead of And
  //.AndRestrictionOn(() => headAlias.KRAApprovedDate).IsNotNull
  .WhereRestrictionOn(() => headAlias.KRAApprovedDate).IsNotNull

  .SelectList(l => l
      .Select(h => h.EvaluationHeadID).WithAlias(() => dto.EvaluationHeadID)
      .Select(h => h.Employee.EmployeeID).WithAlias(() => dto.EmployeeID)
      .Select(h => employeeAlias.EmployeeFirstName).WithAlias(() => dto.EmployeeFirstName)
      .Select(h => employeeAlias.EmployeeMidName).WithAlias(() => dto.EmployeeMidName)
      .Select(h => employeeAlias.EmployeeLastName).WithAlias(() => dto.EmployeeLastName)
      .Select(h => h.EvaluationStartPeriod).WithAlias(() => dto.EvaluationStartPeriod)
      .Select(h => h.EvaluationEndPeriod).WithAlias(() => dto.EvaluationEndPeriod)
      .Select(h => h.ManagerID).WithAlias(() => dto.ManagerID)
      .Select(h => managerAlias.EmployeeFirstName).WithAlias(() => dto.ManagerFirstName)
      .Select(h => managerAlias.EmployeeLastName).WithAlias(() => dto.ManagerLastName)
      .Select(h => supervisorAlias.EmployeeFirstName).WithAlias(() => dto.SupervisorFirstName)
      .Select(h => supervisorAlias.EmployeeLastName).WithAlias(() => dto.SupervisorLastName)
      .Select(h => h.SupervisorID).WithAlias(() => dto.SupervisorID)
      .Select(h => h.DateCreated).WithAlias(() => dto.DateCreated))
  .TransformUsing(Transformers.AliasToBean(typeof(EvaluationHeadDTO)))

JOIN 是通过映射为我们完成的。在 ORM 世界中，它是如何连接 (基于映射) 的唯一方法。查询的其余部分主要是简化