問題記錄：EntityFramework 一對一關係映射

EntityFramework 一對一關係映射有很多種，比如主鍵作為關聯，配置比較簡單，示例代碼：

public class Teacher
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual Student Student { get; set; }
}

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual Teacher Teacher { get; set; }
}

上面代碼表示 Teacher 和 Student 一對一關係，Fluent API 配置如下：

modelBuilder.Entity<Teacher>()
    .HasRequired(x => x.Student)
    .WithOptional(x => x.Teacher);
modelBuilder.Entity<Student>();

測試代碼：

var teachers = await _teacherRepository.GetAll().Include(x => x.Student).ToListAsync()

生成 SQL 代碼：

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    [Extent2].[Id] AS [Id1], 
    [Extent2].[Name] AS [Name1]
    FROM  [dbo].[Teachers] AS [Extent1]
    INNER JOIN [dbo].[Students] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]

另一種 Fluent API 配置如下：

modelBuilder.Entity<Teacher>();
modelBuilder.Entity<Student>()
    .HasRequired(x => x.Teacher)
    .WithOptional(x => x.Student);

執行同樣測試代碼，生成 SQL 代碼：

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    [Extent2].[Id] AS [Id1], 
    [Extent2].[Name] AS [Name1]
    FROM  [dbo].[Teachers] AS [Extent1]
    LEFT INNER JOIN [dbo].[Students] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]

根據上面的測試情況，我們可以得到一些信息，首先測試代碼查詢 Teacher，然後 Inclue Student，Fluent API 配置的不同，生成的 SQL 代碼也不同：

• Fluent API 配置 Teacher，HasRequired Student 對應 INNER JOIN
• Fluent API 配置 Student，HasRequired Teacher 對應 LEFT INNER JOIN

我們可以得出，一對一關係，Fluent API 只需要配置一個實體就可以了，根據查詢關聯的不同，配置對應的 HasRequired 和 WithOptional。

一對一關係，除了兩個實體主鍵映射外，還有一種情況就是主鍵和外鍵映射，可以理解為主表和子表映射，示例代碼：

public class Teacher
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int TeacherId { get; set; }
    public virtual Teacher Teacher { get; set; }
}

Student 中有一個 TeacherId 屬性，對應 Teacher 中的主鍵 Id，在微軟的官方示例中，Student 是作為主表，Teacher 作為子表，也就是說，我們在查詢的時候是查詢的 Student，然後 Include Teacher，Fluent API 配置：

modelBuilder.Entity<Teacher>();
modelBuilder.Entity<Student>()
    .HasRequired(x => x.Teacher)
    .WithMany()
    .HasForeignKey(x => x.TeacherId);

測試代碼：

var students = await _studentRepository.GetAll().Include(x => x.Teacher).ToListAsync();

生成 SQL 代碼：

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[TeacherId] AS [TeacherId], 
    [Extent2].[Id] AS [Id1], 
    [Extent2].[Name] AS [Name1]
    FROM  [dbo].[Students] AS [Extent1]
    INNER JOIN [dbo].[Teachers] AS [Extent2] ON [Extent1].[TeacherId] = [Extent2].[Id]

這是沒有什麼問題的，需要註意的是 Teacher 中並沒有 Student 的導航屬性，如果直接添加的話，運行會直接報錯（Teachers 表預設生成的 Student_Id 欄位找不到），解決方式是需要配置 Teacher 的相關 Fluent API。

上面的一對一關係，其實就是主表的一個子表擴展，在主表中存儲子表的主鍵作為外鍵，查詢的時候直接 Include 子表就可以了，但還有一種情況是，我查詢子表，然後 Include 主表，主表的主鍵存儲在子表中作為外鍵，這裡的主表和子表概念只是相對的。

比如上面場景中，我查詢 Teacher 然後把 Student Include 包含進來，如果是上面的配置是沒有辦法的，因為 Teacher 並沒有配置導航屬性，所以，我們需要改一下代碼：

public class Teacher
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual Student Student { get; set; }
}

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int TeacherId { get; set; }
    public virtual Teacher Teacher { get; set; }
}

上面說過，Teacher 增加 Student 導航屬性會直接報錯，然後我們再修改下 Fluent API 配置：

modelBuilder.Entity<Teacher>()
    .HasRequired(x => x.Student)
    .WithOptional(x => x.Teacher)
    .Map(x => x.MapKey("TeacherId"));
modelBuilder.Entity<Student>();

測試代碼：

var teachers = await _teacherRepository.GetAll().Include(x => x.Student).ToListAsync();

生成 SQL 代碼：

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[StudentCount] AS [StudentCount], 
    [Extent2].[Id] AS [Id1], 
    [Extent2].[Name] AS [Name1], 
    [Extent2].[TeacherId] AS [TeacherId]
    FROM  [dbo].[Teachers] AS [Extent1]
    INNER JOIN [dbo].[Students] AS [Extent2] ON [Extent1].[TeacherId] = [Extent2].[Id]

上面這段代碼會執行報錯的，因為[Extent1].[TeacherId] = [Extent2].[Id]的 Id 順序錯了，MapKey 配置的是 Teacher，而不是 Student，所以，我們再修改下 Fluent API 配置：

modelBuilder.Entity<Teacher>();
modelBuilder.Entity<Student>()
    .HasRequired(x => x.Teacher)
    .WithOptional(x => x.Student)
    .Map(x => x.MapKey("TeacherId"));

需要註意的是，因為 Teacher 中有了 Student 導航屬性，所以我們沒有辦法再進行 HasForeignKey 的配置。

再次執行測試代碼，並沒有生成 SQL 代碼，而是直接報錯：Each property name in a type must be unique. Property name 'TeacherId' is already defined.

根據錯誤提示，我們去除 Student 中的 TeacherId 屬性，重新執行測試代碼。

生成的 SQL 代碼：

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[StudentCount] AS [StudentCount], 
    [Extent3].[Id] AS [Id1], 
    [Extent3].[Name] AS [Name1], 
    [Extent3].[TeacherId] AS [TeacherId]
    FROM   [dbo].[Teachers] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Students] AS [Extent2] ON [Extent1].[Id] = [Extent2].[TeacherId]
    LEFT OUTER JOIN [dbo].[Students] AS [Extent3] ON [Extent1].[Id] = [Extent3].[TeacherId]

結果是沒有什麼問題的，但 LEFT OUTER JOIN 了兩次，不知道具體是什麼原因。

網上找了相關的資料，但一對一關係示例都是那種：子表沒有導航屬性，主表存儲子表的主鍵作為外鍵，並有子表的導航屬性，上面的類似示例，stackoverflow 找到一個，但評論中的解決方式試過不行。

針對這種情況，如果有更好的實現方式，歡迎告知。