問題記錄：EntityFramework 一對一關系映射 - xishuai－IT工程師數位筆記本

文章出處

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]