按ID将实体映射到自联接表 [英] Map Entities to self-joined Table by Id

问题描述

我的旧表"AllData"具有以下列:Id, Title, LookupColumn1Id 我的实体:

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

我想这样生成SELECT:

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)       

推荐答案

让我告诉你，选择之一.我希望对于该草案，具有LookupColumn1Id NULL的记录将扮演Department的角色，其余的将扮演Employee的角色.

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 
}

映射可能是这样的:

<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>

此映射用于读取访问(必需的SELECT)正在工作.现在，我们可以创建一个查询:

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

通常，此方案将起作用，但是我们所做的是仅在C#中继承(均从BaseEntity派生)，而在映射中则不行.

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.

原因是，缺少的列将扮演鉴别符"角色.这就是为什么我们使用具有WHERE属性的映射(请参见xml中的类元素)，通过查找列的存在来区分Department和Employee的原因

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

这篇关于按ID将实体映射到自联接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！