问题记录：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 找到一个，但评论中的解决方式试过不行。

针对这种情况，如果有更好的实现方式，欢迎告知。

个人收藏笔记记录

开通VIP