I would like to achieve the desired sql using Nhibernate QueryOver (below) using the nHibernate code (below). The query generated does not produces any error, however, it does not do the inner join and as a result, the dataset return null for those properties using the managerAlias and supervisorAlias.
EDIT: I have included all relevant mapping/entity classes.
how can I achieve this?
desired 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
I have the following code in my application:
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>();
Mapping:
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>();
}
}
The mapping seems to be the culprit. A scenario, you are looking for, is one of the most usual.
So, if we would have EvaluationHead C# like this:
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; }
}
The (xml) mapping of the EvaluationHead like this:
<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" />
The fluent mapping
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();
}
So, what we do have now, is:
EvaluationHead (fluent mapping would be similar), int properties representing the ID columns and Employee table (Employee, Supervisor, Manager)We can adjust the query, like this:
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)))
The JOIN is done for us by the mapping. In ORM world, it is the only way how to JOIN (based on mapping). The rest of the query is mostly simplification