EF Core中的继承与原生SQL语句使用

从本章开始不会再增加系统涉及的业务功能了，增加的内容更多的是与纯技术案例有关的内容。

本章主要向读者介绍如下内容。

• EF Core中如何实现实体之间的继承。
• EF Core中如何执行原生SQL语句。

37.1　继承

继承是面向对象编程的三大特征之一，通过继承可以复用基类的属性。目前我们在一些视图模型和实体中已经使用过继承了，如StudentEditViewModel继承了StudentCreateViewModel。在本章我们通过将Student与Teacher实体的公共属性提取到Person类中，来实现对Person类的继承。

在EF Core中继承有如下3种不同的实现方式。

• TPH（Table Per Hierarchy）：所有的数据都放在同一个表内，但是使用辨别标志（Discriminator）的方式来区分，即通过Discriminator与DiscriminatorID来进行区分。
• TPC（Table Per Concrete-Type）:由具体类型的表来存放各自的数据，而各自没有任何关联，继承的实体会包含基类中的所有属性。
• TPT（Table Per Type）：表示每个对象各自独立产生表，这样各表之间就没有直接关联，要额外实现关联性才能产生关联，子实体通过实体ID关联DiscriminatorID找到父类。

TPC和TPH继承模式的性能通常比TPT继承模式好，因为TPT模式会导致复杂的联接查询。但是截止到Entity Framework Core 3.1仅支持TPH继承。

37.1.1　实现TPH继承

在Models文件夹中创建Person.cs并添加如下代码。

    public abstract class Person
    {
        public int Id{get;set;}
        [Required]
        [Display(Name = "姓名")]
        [StringLength(50)]
        public string Name{get;set;}

        [Display(Name = "电子邮箱")]
        public string Email{get;set;}
    }

请注意，Person类是一个抽象类，它不允许实例化，也不能直接创建对象，必须要通过子类创建才能使用abstract类的方法。

Student实体与Teacher实体均继承自Person类，它们不用单独声明ID主键及Name与Email属性值，而是直接复用Person类中的属性代码。

   public class Student:Person
    {
        /// <summary>
        /// 主修科目
        /// </summary>
        public MajorEnum?Major{get;set;}

        public string PhotoPath{get;set;}

        [NotMapped]
        public string EncryptedId{get;set;}

        /// <summary>
        /// 入学时间
        /// </summary>
        [DataType(DataType.Date)]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}"，ApplyFormatInEditMode = true)]
        public DateTime EnrollmentDate{get;set;}

        public ICollection<StudentCourse> StudentCourses{get;set;}
    }

在Teacher.cs中进行相同的更改，代码如下。

    /// <summary>
    /// 教师信息
    /// </summary>
    public class Teacher:Person
    {
        [DataType(DataType.Date)]
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}"，ApplyFormatInEditMode = true)]
        [Display(Name = "聘用时间")]
        public DateTime HireDate{get;set;}

        public ICollection<CourseAssignment> CourseAssignments{get;set;}

        public OfficeLocation OfficeLocation{get;set;}
    }

将Person.cs添加到数据库上下文连接池AppDbContext.cs中，代码如下。

public class AppDbContext:IdentityDbContext<ApplicationUser>
    {
        //注意:将ApplicationUser作为泛型参数传递给IdentityDbContext类
        public AppDbContext(DbContextOptions<AppDbContext> options)
          :base(options)
        {
        }
        public DbSet<Student> Students{get;set;}
        public DbSet<Course> Courses{get;set;}
        public DbSet<StudentCourse> StudentCourses{get;set;}
        public DbSet<Department> Departments{get;set;}
        public DbSet<Teacher> Teachers{get;set;}
        public DbSet<OfficeLocation> OfficeLocations{get;set;}
        public DbSet<CourseAssignment> CourseAssignments{get;set;}
        public DbSet<Person> People{get;set;}
}

我们希望数据库中的表名称依然是Person（而不是People），因此在modelBuilder的扩展方法Seed()中添加以下配置。

  public static void Seed(this ModelBuilder modelBuilder)
        {
            ///指定实体在数据库中生成的名称
  modelBuilder.Entity<Course>().ToTable("Course"，"School");
modelBuilder.Entity<StudentCourse>().ToTable("StudentCourse"，"School");
            modelBuilder.Entity<Person>().ToTable("Person");               modelBuilder.Entity<CourseAssignment>()
                   .HasKey(c => new{c.CourseID，c.TeacherID});
        }

这里请删除Student的表映射声明，否则会报错。

37.1.2　执行数据库迁移

保存修改的文件并编译生成解决方案，随后打开SQL Server对象资源管理器，删除旧的MockSchoolDB数据库。

重新执行迁移命令update-database生成一个新数据库。执行添加迁移命令add-migration AddPersonEntity，添加一条新的迁移记录后，再执行命令update-database。同步数据库表结果到数据库中，运行项目后初始化种子数据，打开Person表，效果如图37.1所示。

 

图37.1

导航到http://localhost:13380/Teacher/Index/5？Sorting=Id&CurrentPage=1&courseID=1045可以看到完整的视图数据，页面如图37.2所示。

 

图37.2

37.2　执行原生SQL语句

目前我们通过EF Core完成了一个较为完整的学校管理系统，在此期间我们没有像传统的开发者一样通过SQL语句来实现业务逻辑，但是并不是说EF Core不支持SQL语句。EF Core的优点之一是它可避免读者编写和数据库过于耦合的代码，它会动态生成SQL查询和命令（也称为动态SQL）。但有一些特殊情况，还是需要执行原生SQL语句。对于这些情况，EF Core 1.0提供了相关的API，可以帮助我们执行原生SQL语句。从EF Core 1.0开始就支持原生SQL语句的执行方法，而具体的方式有以下两种。

• 使用DbSet.FromSql返回实体类型的查询方法。返回的对象必须是DbSet对象期望的类型，并且它们会自动跟踪数据库上下文，除非读者手动关闭跟踪。
• 对于非查询命令使用Database.ExecuteSqlComma。
• 如果返回类型不是实体本身，而是视图模型，那么可以使用由EF Core提供的ADO.NET来进行数据库连接。请注意ADO.NET的数据库上下文不会跟踪返回的数据，而EF Core会，这是两者的不同。

37.2.1　DbSet.FromSqlRaw的使用

DbSet<TEntity> 类提供了一种方法，用于执行返回TEntity类型实体的查询。在Departments Controller.cs的Details()方法中，使用FromSqlRaw()方法来替换学院列表的结果，代码如下。

   public async Task<IActionResult> Details(int Id)
        {
            string query = "SELECT * FROM dbo.Departments WHERE DepartmentID={0}";
            var model = await _dbcontext.Departments.FromSqlRaw(query，Id).Include(d => d.Administrator)
                      .AsNoTracking()
                      .FirstOrDefaultAsync();
            if(model == null)
            {
                ViewBag.ErrorMessage = $"部门ID{Id}的信息不存在，请重试。";
                return View("NotFound");
            }

            return View(model);
        }

请注意，在EF Core早期的版本中我们调用的是FromSql()方法，而不是FromSqlRaw()方法。

从ASP.NET Core 3.0的版本开始，FromSql()就被官方弃用了，而是推荐采用FromSqlRaw()与FromSqlInterpolated()，它们是之前FromSql()的重载方法。

• 若要使用纯字符串从SQL查询返回对象，请改用FromSqlRaw()。
• 若要使用插值字符串语法从SQL查询返回对象以创建参数，请改用FromSqlInterpolated()。

读者需要根据业务情况来选择，导航学院详情页效果如图37.3所示。

 

图37.3

37.2.2　Database.ExecuteSqlComma的使用

接下来，我们在EF Core中执行ADO.NET的ExecuteSqlComma()方法来执行SQL语句。在HomeController的About()操作方法中，我们之前通过LINQ配合仓储模式进行分组，实现了学生信息的统计，接下来我们使用原生SQL语句的分组查询来实现该功能。

修改HomeController中的About()操作方法，代码如下。

public async Task<ActionResult> About()
        {
            List<EnrollmentDateGroupDto> groups = new List<EnrollmentDateGroupDto>();
            //获取数据库的上下文连接
            var conn = _dbcontext.Database.GetDbConnection();
            try
            {    //打开数据库连接
                await conn.OpenAsync();
                //建立连接，因为非委托资源，所以需要使用using进行内存资源的释放
                using(var command = conn.CreateCommand())
                {
                    string query = "SELECT EnrollmentDate，COUNT(*)AS StudentCount   FROM Person  WHERE Discriminator = ‘Student’  GROUP BY EnrollmentDate";
                    command.CommandText = query;//赋值需要执行的SQL语句
                    DbDataReader reader = await command.ExecuteReaderAsync();
                    //执行命令
                    if(reader.HasRows)//判断是否有返回行
                    {       //读取行数据，将返回值填充到视图模型中
                        while(await reader.ReadAsync())
                        {
                            var row = new EnrollmentDateGroupDto
                            {EnrollmentDate = reader.GetDateTime(0)，
                            StudentCount = reader.GetInt32(1) };
                            groups.Add(row);
                        }
                    }
                    //释放使用的所有资源
                    reader.Dispose();
                }
            }
            finally
            {  //关闭数据库连接
                conn.Close();
            }
            return View(groups);
        }

运行项目，导航到http://localhost:13380/home/About，可以看到返回值的结果与修改代码前的结果一致，如图37.4所示。

 

图37.4

37.2.3　执行原生SQL语句实现更新

我们通过修改课程管理中的所有课程的学分功能，来使用ExecuteSqlRawAsync命令执行更新的SQL命令。为了使此功能完整，我们在CoursesController.cs中为HttpGet和HttpPost添加UpdateCourseCredits()方法，代码如下。

        #region修改课程学分
        public IActionResult UpdateCourseCredits()
        {
            return View();
        }
        [HttpPost]
        public async Task<IActionResult> UpdateCourseCredits(int?multiplier)
        {
            if(multiplier!= null)
            {
                ViewBag.RowsAffected =
                    //通过ExecuteSqlRawAsync()方法执行SQL语句
                    await _dbcontext.Database.ExecuteSqlRawAsync(
                        "UPDATE School.Course SET Credits = Credits * {0}"，
                        parameters:multiplier);
            }
            return View();
        }
        #endregion

在Views/Courses中添加UpdateCourseCredits.cshtml文件，代码如下。

@{
    ViewBag.Title = "修改课程学分信息";
}

<h2>
    修改课程学分
</h2>

@if(ViewBag.RowsAffected == null)
{
    <form asp-action="UpdateCourseCredits">

        <div class="form-group row">
            <label for="multiplier" class="col-sm-4 col-form-label"> 输入一个数字，我会把每门课程乘以这个系数:</label>
            <div class="col-sm-8">
                <input type="text" id="multiplier" name="multiplier" class="form-control" placeholder="请输入学分" />
            </div>
        </div>

        <div class="form-group row">
            <div class="col-sm-10">
                <input type="submit" value="创建" class="btn btn-primary" />
            </div>
        </div>
    </form>
}
@if(ViewBag.RowsAffected!= null)
{
    <p>
        更新了 @ViewBag.RowsAffected门课程信息的学分
    </p>
}
<div class="form-group  ">

    <a class="btn btn-info" asp-action="Index">返回</a>
</div>

通过ViewBag.RowsAffected的值来判断是显示输入文本框还是结果，运行项目后导航到http://localhost:13380/Course/UpdateCourseCredits，我们输入2将所有的学分值都乘以2，如图37.5所示。

 

图37.5

我们可以通过SQL Server对象资源管理器查看Course表中的数据，如图37.6所示。

 

图37.6

修改Index文件中的导航菜单栏，添加一个导航链接到UpdateCourseCredits视图，代码如下。

  <div class="form-actions no-color">
        <input type="hidden" name="CurrentPage" value="@Model.CurrentPage" />
        <input type="hidden" name="Sorting" value="@Model.Sorting" />
        <p>
            请输入名称:<input type="text" name="FilterText" value="@Model.FilterText" />
            <input type="submit" value="查询" class="btn btn-outline-dark" /> |
            <a asp-action="Index">返回所有列表</a>| <a asp-action="Create">
                添加
            </a>| <a asp-action="UpdateCourseCredits">
                修改学分
            </a>
        </p>
    </div>

37.3　小结

在本章中我们了解了EF Core中的实体继承与原生SQL语句的使用，在实际开发过程中，采用继承的场景比较少，因为大多数的业务不需要采用继承来实现，许多开发者认为大量使用继承会让项目不好维护。

对于我们而言，继承是一个需要掌握的技能，毕竟有些业务情况，使用继承可以快速交付。而原生SQL语句的使用则是我们经常需要的，过去几年因为EF Core相关学习资料的缺乏，很多开发者对EF Core存在比较多的误解，认为它无法进行原生SQL语句的调用，本章中我们知晓了在EF Core中同样可以采用ADO.NET的形式来实现原生SQL命令的执行。

本文摘自《深入浅出 ASP.NET Core》

 

本书是一本系统地介绍ASP.NET Core、Entity Framework Core以及ASP.NET Core Identity框架技术的入门图书，旨在帮助读者循序渐进地了解和掌握ASP.NET Core。本书使用ASP.NET Core从零开始搭建一个实际的项目。从基本的控制台应用程序开始，介绍ASP.NET Core基本的启动流程，涵盖ASP.NET Core框架中各个技术的实际应用。同时，本书也会介绍一些ASP.NET Core的高级概念。在本书中，我们会开发一个学校管理系统，其中包含清晰的操作步骤和大量的实际代码，以帮助读者学以致用，将ASP.NET Core的知识运用到实际的项目开发当中，最后我们会将开发的项目部署到生产环境中。通过阅读本书，读者将掌握使用ASP.NET Core开发Web应用程序的方法，并能够在对新项目进行技术选型时做出战略决策。