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

最新推荐文章于 2024-08-12 23:13:44 发布

weixin_39805409

最新推荐文章于 2024-08-12 23:13:44 发布

阅读量255

点赞数

文章标签：原生sql

本文链接：https://blog.csdn.net/weixin_39805409/article/details/112607962

版权

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

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

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    {        ///         /// 主修科目        ///         public MajorEnum?Major{get;set;}        public string PhotoPath{get;set;}        [NotMapped]        public string EncryptedId{get;set;}        ///         /// 入学时间        ///         [DataType(DataType.Date)]        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}"，ApplyFormatInEditMode = true)]        public DateTime EnrollmentDate{get;set;}        public ICollection StudentCourses{get;set;}    }

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

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

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

public class AppDbContext:IdentityDbContext    {        //注意:将ApplicationUser作为泛型参数传递给IdentityDbContext类        public AppDbContext(DbContextOptions options)          :base(options)        {        }        public DbSet Students{get;set;}        public DbSet Courses{get;set;}        public DbSet StudentCourses{get;set;}        public DbSet Departments{get;set;}        public DbSet Teachers{get;set;}        public DbSet OfficeLocations{get;set;}        public DbSet CourseAssignments{get;set;}        public DbSet People{get;set;}}

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

  public static void Seed(this ModelBuilder modelBuilder)        {            ///指定实体在数据库中生成的名称  modelBuilder.Entity().ToTable("Course"，"School");modelBuilder.Entity().ToTable("StudentCourse"，"School");            modelBuilder.Entity().ToTable("Person");               modelBuilder.Entity()                   .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类型实体的查询。在Departments Controller.cs的Details()方法中，使用FromSqlRaw()方法来替换学院列表的结果，代码如下。

   public async Task 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 About()        {            List groups = new List();            //获取数据库的上下文连接            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 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 = "修改课程学分信息";}

`修改课程学分`

@if(ViewBag.RowsAffected == null){

输入一个数字，我会把每门课程乘以这个系数:

}@if(ViewBag.RowsAffected!= null){

更新了 @ViewBag.RowsAffected门课程信息的学分

}

返回

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

图37.5

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

图37.6

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

请输入名称: | 返回所有列表| 添加 | 修改学分

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