EF Core反向导航属性解决多对一关系

多对一是一种很常见的关系，例如：一个班级有一个学生集合属性，同时，班级有班长、语文课代表、数学课代表等单个学生属性，如果定义2个实体类，班级SchoolClass和学生Student，那么，班级SchoolClass类有多个学生Student类的导航属性，学生Student类有一个班级SchoolClass类的导航属性。此时就需要使用InverseProperty反向导航属性去指定通过哪个属性建立引用关系，否则数据库建不起来。

通过一个小DEMO做试验。

新建Asp.Net Core MVC网站项目，添加2个实体类如下所示

    //班级
    public class SchoolClass
    {
        //主键
        public int ID { get; set; }

        //班级名字
        public string ClassTitle { get; set; }

        //本班级的学生集合
        public List<Student> Students { get; set; }

        //班长
        public Student ClassMonitor { get; set; }

        //语文课代表
        public Student Chinese { get; set; }

        //数学课代表
        public Student Mathematics { get; set; }
    }

    //学生
    public class Student
    {
        //主键
        public int ID { get; set; }

        //姓名
        public string Name { get; set; }

        //学生所在的班级
        public SchoolClass MyClass { get; set; }
    }

　　

然后通过右键菜单添加SchoolClass实体类的控制器，让系统自动创建数据库上下文代码

然后会收到一个错误。

Unable to determine the relationship represented by navigation property 'SchoolClass.Students' of type 'List<Student>'. Either manually configure the relationship, or ignore this property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'. StackTrace:

系统无法判断SchoolClass多个Student导航属性的关系，此时可以在Students属性上面添加反向导航属性[InverseProperty("MyClass")]，就可以完成自动化创建控制器了。

[InverseProperty("MyClass")]
public List<Student> Students { get; set; }

　　

然后在软件启动时创建一组测试数据。

        public static void Main(string[] args)
        {
            //CreateWebHostBuilder(args).Build().Run();

            IWebHost webHost = CreateWebHostBuilder(args).Build();

            //系统初始化
            AppInit(webHost.Services);

            webHost.Run();
        }

        //系统初始化
        private static void AppInit(IServiceProvider serviceProvider)
        {
            //初始化数据库
            using (var scope = serviceProvider.CreateScope())
            {
                var context = scope.ServiceProvider.GetRequiredService<StudentWebContext>();

                //确保创建数据库
                context.Database.EnsureCreated();

                if (context.SchoolClass.Any())
                    return;

                var schoolClass61 = new SchoolClass()
                {
                    ClassTitle = "六一班"
                };

                //先保存班级，否则报错
                //Unable to save changes because a circular dependency was detected in the data to be saved: 'SchoolClass [Added] <- Students MyClass { 'MyClassID' } Student [Added] <- Chinese { 'ChineseID' } SchoolClass [Added]'.
                context.Add(schoolClass61);
                int rows = context.SaveChanges();
                Console.WriteLine($"添加了班级{schoolClass61.ClassTitle}, 影响记录{rows}");

                var student1 = new Student()
                {
                    Name = "张三",
                };

                var student2 = new Student()
                {
                    Name = "李四",
                };

                var student3 = new Student()
                {
                    Name = "王五",
                };

                var student4 = new Student()
                {
                    Name = "赵六",
                };

                schoolClass61.Students = new List<Student>()
                {
                    student1,
                    student2,
                    student3,
                    student4
                };

                //设置同学的职位
                schoolClass61.ClassMonitor = student1;
                schoolClass61.Chinese = student2;
                schoolClass61.Mathematics = student3;

                //保存到数据库
                rows = context.SaveChanges();
                Console.WriteLine($"添加了{schoolClass61.Students.Count}位同学, 影响记录{rows}");

            }
        }

　　

然后修改控制器的Details方法，显示班级详细信息时Include加载全部学生集合Students，不需要再加载Chinese等各个课代表导航属性，因为已经加载了班上的全部学生，EF Core会自动处理这些Student类型的导航属性。

public async Task<IActionResult> Details(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }

            var schoolClass = await _context.SchoolClass
                .Include(x => x.Students)
                .FirstOrDefaultAsync(m => m.ID == id);
            if (schoolClass == null)
            {
                return NotFound();
            }

            return View(schoolClass);
        }

　　

修改Details页面显示班级学生和各个职务的学生。

<dt class="col-sm-2">
            班上的同学
        </dt>
        <dd class="col-sm-10">
            @foreach (var student in Model.Students)
            {
                @student.Name<br />
            }
        </dd>
        <dt class="col-sm-2">
            班长
        </dt>
        <dd class="col-sm-10">
            @Html.DisplayFor(model => model.ClassMonitor.Name)
        </dd>
        <dt class="col-sm-2">
            语文课代表
        </dt>
        <dd class="col-sm-10">
            @Html.DisplayFor(model => model.Chinese.Name)
        </dd>
        <dt class="col-sm-2">
            数学课代表
        </dt>
        <dd class="col-sm-10">
            @Html.DisplayFor(model => model.Mathematics.Name)
        </dd>

　　

运行成功。

打开数据库连接，可以查看系统自动创建的外键引用，完全符合预期。

CREATE TABLE [dbo].[Student] (
    [ID]        INT            IDENTITY (1, 1) NOT NULL,
    [Name]      NVARCHAR (MAX) NULL,
    [MyClassID] INT            NULL,
    CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [FK_Student_SchoolClass_MyClassID] FOREIGN KEY ([MyClassID]) REFERENCES [dbo].[SchoolClass] ([ID])
);

CREATE TABLE [dbo].[SchoolClass] (
    [ID]             INT            IDENTITY (1, 1) NOT NULL,
    [ClassTitle]     NVARCHAR (MAX) NULL,
    [ClassMonitorID] INT            NULL,
    [ChineseID]      INT            NULL,
    [MathematicsID]  INT            NULL,
    CONSTRAINT [PK_SchoolClass] PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [FK_SchoolClass_Student_ChineseID] FOREIGN KEY ([ChineseID]) REFERENCES [dbo].[Student] ([ID]),
    CONSTRAINT [FK_SchoolClass_Student_ClassMonitorID] FOREIGN KEY ([ClassMonitorID]) REFERENCES [dbo].[Student] ([ID]),
    CONSTRAINT [FK_SchoolClass_Student_MathematicsID] FOREIGN KEY ([MathematicsID]) REFERENCES [dbo].[Student] ([ID])
);

　　

继续试验，再增加一个老师实体类Teacher

    //老师
    public class Teacher
    {
        //主键
        public int ID { get; set; }

        //姓名
        public string Name { get; set; }

        //老师作为班主任管理的班级
        public SchoolClass AdminClass { get; set; }
    }

　　

给班级SchoolClass增加班主任、语文老师、数学老师属性

        //班主任
        public Teacher HeadTeacher { get; set; }

        //语文老师
        public Teacher ChineseTeacher { get; set; }

        //数学老师
        public Teacher MathTeacher { get; set; }

　　

修改Details方法，加载老师属性对象

            var schoolClass = await _context.SchoolClass
                .Include(x => x.Students)
                .Include(x => x.HeadTeacher)
                .Include(x => x.ChineseTeacher)
                .Include(x => x.MathTeacher)
                .FirstOrDefaultAsync(m => m.ID == id);

　　

修改Details页面增加显示老师

        <dt class="col-sm-2">
            班主任
        </dt>
        <dd class="col-sm-10">
            @Html.DisplayFor(model => model.HeadTeacher.Name)
        </dd>
        <dt class="col-sm-2">
            语文老师
        </dt>
        <dd class="col-sm-10">
            @Html.DisplayFor(model => model.ChineseTeacher.Name)
        </dd>
        <dt class="col-sm-2">
            数学老师
        </dt>
        <dd class="col-sm-10">
            @Html.DisplayFor(model => model.MathTeacher.Name)
        </dd>

　　

补全StudentWebContext的数据表。

    public class StudentWebContext : DbContext
    {
        public StudentWebContext (DbContextOptions<StudentWebContext> options)
            : base(options)
        {
        }

        public DbSet<SchoolClass> SchoolClass { get; set; }

        public DbSet<Student> Student { get; set; }

        public DbSet<Teacher> Teacher { get; set; }

    }

　　

项目启动时增加老师的测试数据

                //添加老师
                var teacher1 = new Teacher()
                {
                    Name = "孔子"
                };

                var teacher2 = new Teacher()
                {
                    Name = "李白"
                };

                var teacher3 = new Teacher()
                {
                    Name = "祖冲之"
                };

                //设置老师的职位
                schoolClass61.HeadTeacher = teacher1;
                schoolClass61.ChineseTeacher = teacher2;
                schoolClass61.MathTeacher = teacher3;

                //保存到数据库
                rows = context.SaveChanges();
                Console.WriteLine($"添加了老师同学, 影响记录{rows}");

　　

打开VS2017的SQL Server对象管理器，通过右键菜单粗暴删除SchoolClass、Student数据表，再次运行项目，再次收到类似的错误

Unable to determine the relationship represented by navigation property 'SchoolClass.HeadTeacher' of type 'Teacher'. Either manually configure the relationship, or ignore this property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.

参照上述方法，给班级SchoolClass的班主任属性HeadTeacher增加反向导航属性[InverseProperty("AdminClass")]，这个问题就解决了。

        //班主任
        [InverseProperty("AdminClass")]
        public Teacher HeadTeacher { get; set; }

　　

再次运行，会收到新的错误

The child/dependent side could not be determined for the one-to-one relationship between 'Teacher.AdminClass' and 'SchoolClass.HeadTeacher'. To identify the child/dependent side of the relationship, configure the foreign key property. If these navigations should not be part of the same relationship configure them without specifying the inverse. See http://go.microsoft.com/fwlink/?LinkId=724062 for more details.

访问http://go.microsoft.com/fwlink/?LinkId=724062，自动跳转到https://docs.microsoft.com/zh-cn/ef/core/modeling/relationships#one-to-one，看介绍：

一对一

一对一关系两端具有引用导航属性。 它们遵循相同的约定作为一个对多关系，但在外键属性，以确保只有一个依赖于与每个主体上引入了唯一索引。

不好理解，有点绕？看示例的代码，大约是把其中一个实体类的导航属性改造为外键ID和导航属性相结合的方式。照办：

        public int AdminClassID { get; set; }

        //老师作为班主任管理的班级
        public SchoolClass AdminClass { get; set; }

　　

再次运行，可以创建数据库了，但是报错：

SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Teacher_SchoolClass_AdminClassID". The conflict occurred in database "StudentWebContext", table "dbo.SchoolClass", column 'ID'.

大意是AdminClassID属性不允许为空。看数据库设计器Teacher的代码，AdminClassID是非空的：

CREATE TABLE [dbo].[Teacher] (
    [ID]           INT            IDENTITY (1, 1) NOT NULL,
    [Name]         NVARCHAR (MAX) NULL,
    [AdminClassID] INT            NOT NULL,
    CONSTRAINT [PK_Teacher] PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [FK_Teacher_SchoolClass_AdminClassID] FOREIGN KEY ([AdminClassID]) REFERENCES [dbo].[SchoolClass] ([ID]) ON DELETE CASCADE

　　

实际上，一位老师，是可以不担当任何一个班级的班主任的，因此AdminClassID属性应该是可空的。再改一下

        public int? AdminClassID { get; set; }

　　

删除数据表，再次运行，没有任何问题了，数据库Teacher代码是正确的，

CREATE TABLE [dbo].[Teacher] (
    [ID]           INT            IDENTITY (1, 1) NOT NULL,
    [Name]         NVARCHAR (MAX) NULL,
    [AdminClassID] INT            NULL,
    CONSTRAINT [PK_Teacher] PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [FK_Teacher_SchoolClass_AdminClassID] FOREIGN KEY ([AdminClassID]) REFERENCES [dbo].[SchoolClass] ([ID])

　　

Details页面数据显示也是正确的。

小结

EF Core多对一关系配置要点：

1. A实体引用多个B导航属性，B实体引用一个A导航属性；
2. A实体类注明其中一个B导航属性为InverseProperty；
3. B实体类定义A导航属性的可空外键AID?；

代码：https://github.com/woodsun2018/StudentWeb