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

末蓝、 2022-12-21 10:56 152阅读 0赞

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

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

*  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所示。

![f1ba4a77edcba4881bd153103980875d.png][]

图37.1

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

![3cf9bbdfe61fcee41d3136f42477a720.png][]

图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所示。

![b1eed7fe70b12505b194b422dc0bdb86.png][]

图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所示。

![8e05d299fd48557985c9b3ea33c700c1.png][]

图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所示。

![c5be18fe5eb27a2cc15ab3133ab01a0e.png][]

图37.5

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

![6b353da2138ddb9624200e0372c0aa3f.png][]

图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》**

![70c84e363bd86ac2e3058bf2250c81cc.png][]

本书是一本系统地介绍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应用程序的方法，并能够在对新项目进行技术选型时做出战略决策。

[f1ba4a77edcba4881bd153103980875d.png]: /images/20221120/167fdbffa1d14d7a9d0e5d9fe53adc06.png
[3cf9bbdfe61fcee41d3136f42477a720.png]: /images/20221120/dcb62c0aa0544a57bcf56a87f68043cf.png
[b1eed7fe70b12505b194b422dc0bdb86.png]: /images/20221120/b928c58eaf7d4c97bacfe4099ca78ce9.png
[8e05d299fd48557985c9b3ea33c700c1.png]: /images/20221120/fd54a23adda0461e8408add8211fec77.png
[c5be18fe5eb27a2cc15ab3133ab01a0e.png]: /images/20221120/7e9271b0961e4fc6a29a2fc975981815.png
[6b353da2138ddb9624200e0372c0aa3f.png]: /images/20221120/e0f7b82844ac4e539ce6dd4bd44cfff2.png
[70c84e363bd86ac2e3058bf2250c81cc.png]: /images/20221120/11df37520dc44ad9bb1885dcace961c8.png