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

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





本文转自田园里的蟋蟀博客园博客,原文链接:http://www.cnblogs.com/xishuai/p/ef-one-to-one-fluent-api.html,如需转载请自行联系原作者

相关文章
|
数据库
Entity Framework 一对多关系映射
Entity Framework 一对多关系映射
94 0
Entity Framework 一对多关系映射
|
数据库 .NET 开发框架
【译】EntityFramework6与EntityFrameworkCore的区别
EntityFramework6 EF6 是一个久经考验的数据库访问技术,发展多年,拥有许多特性,并且成熟稳定。2008年EF作为 .Net 3.5 Sp1 和Visual Studio 2008 SP1 的一部分首次发布。
1377 0
|
数据库 .NET 开发框架