不使用主键的映射中流畅的 NHibernate 连接表

Question

我正在尝试从 2 个与主键无关的表创建一个实体

表：

CREATE TABLE [employees](
    [ssn] [nvarchar](9) NULL,
    [active] [bit] NULL,    
    [employee_id] [int] IDENTITY(1,1) NOT NULL 
)


CREATE TABLE [sam_employees](
    [ssn] [nvarchar](9) NULL,
    [first_name] [nvarchar](50) NULL,
    [last_name] [nvarchar](50) NULL,
    [skill] [nvarchar](50) NULL,
    ....
)

要生成的 SQL：

SELECT this_.employee_id  as employee1_0_0_,
       this_.ssn          as ssn0_0_,
       this_.active       as active0_0_,
       this_1_.first_name as first2_1_0_,
       this_1_.last_name  as last3_1_0_,
       this_1_.skill      as skill1_0_
FROM   employees this_
       inner join sam_employees this_1_
         on this_.ssn = this_1_.ssn
WHERE  this_.active = 1

我当前的映射：

public class EmployeeMap : ClassMap<Employee>
{
    public EmployeeMap()
    {
        Table("employees");
        Id(x => x.Id, "employee_id");
        Map(x => x.SSN, "ssn");
        Map(x => x.IsActive, "active");

        Join("sam_employees", mm =>
                                  {                                          
                                      mm.KeyColumn("ssn");
                                      mm.Map(xx => xx.FirstName, "first_name");
                                      mm.Map(xx => xx.LastName, "last_name");
                                      mm.Map(xx => xx.Skill, "skill");
                                  });            
    }
}

但是通过这个映射，我在 this_.employee_id = this_1_.ssn 上有这样的连接条件

我知道这个问题之前被问过，但我没有找到一个好的答案，只是在数据库端使用视图而不是表的解决方法。

Answer 1

您可以尝试将 sam_employees 映射为实体，并使用带有连接类型获取的引用映射。