Map Entities to self-joined Table by Id

Question

My legacy table "AllData" has those columns:Id, Title, LookupColumn1Id My entities:

public class BaseEntity
{ 
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
}
public class Employee: BaseEntity
{ 
    public virtual int DepartmentId { get; set; }
    public virtual string DepartmentName { get; set; }
}
public class Department: BaseEntity
{
    public virtual int HeadManagerId { get; set; }
}

I want to generate SELECT like this:

SELECT EmployeeTable.Title, DepartmentTable.Id, DepartmentTable.Title
FROM AllData EmployeeTable left outer join AllData DepartmentTable on EmployeeTable.LookupColumn1Id=DepartmentTable.Id       
WHERE EmployeeTable.tp_ListId = @p0 and (DepartmentTable.Title = @p1)       

Answer 1

Let me show you, one of the options. For this draft, I'd expect, that records which do have LookupColumn1Id NULL will play the role of the Department, the rest will play the role of Employee.

The Entities could look like this:

public class BaseEntity
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; } 
}
public class Employee : BaseEntity
{
    public virtual Department Department { get; set; } // to lookup record
}
public class Department : BaseEntity
{
    public virtual IList<Employee> Employees { get; set; } // the way back 
}

The mapping could be like this:

<class name="Department" table="[dbo].[AllData]" lazy="true" batch-size="25" 
    where="LookupColumn1Id IS NULL" >
  <id name="Id" column="Id" generator="native" />

  <property not-null="true"  name="Name" column="Title" />

  <bag name="Employees" >
    <key column="LookupColumn1Id" />
    <one-to-many class="Employee"/>
  </bag>

</class>

<class name="Employee1" table="[dbo].[AllData]" lazy="true" batch-size="25"
  where="LookupColumn1Id IS NOT NULL" >
  <id name="Id" column="Id" generator="native" />

  <property not-null="true"  name="Name" column="Title" />

  <many-to-one name="Department" class="Department" column="LookupColumn1Id " />
</class>

This mapping, for read access (the required SELECT) is working. Now, we can create a query:

[TestMethod]
public void TestAllData()
{
    var session = NHSession.GetCurrent();

    // the Employee Criteria
    var criteria = session.CreateCriteria<Employee>();
    // joined with the Department
    var deptCrit = criteria.CreateCriteria("Department", JoinType.LeftOuterJoin);

    // here we can filter Department
    deptCrit.Add(Restrictions.Eq("Name", "Dep Name"));
    // here we can filter Employee
    criteria.Add(Restrictions.Eq("Name", "Emp Name"));


    // the SELECT
    var results = criteria
        .List<Employee>();

    Assert.IsTrue(results.IsNotEmpty());

    var employee = results.First();

    // check if all data are injected into our properties
    Assert.IsTrue(employee.Name.IsNotEmpty());
    Assert.IsTrue(employee.Department.Name.IsNotEmpty());
}

This scenario in general will work, but what we did, is the inheritance only in C# (both derived from BaseEntity), while not in the mapping.

The reason is, the missing column which would play the Discriminator role. That's why we are using the mapping with a WHERE attribute (see class element in xml), distinguishing the Department and Employee by the lookup column presence