EFCore 从入门到精通-6（详谈查询)

1.初始准备

1.1 工具准备

	开发软件：VisualStudio2022，EFCore6.0
	.net Core版本：.Net6.0
	数据库：mysql8.0
	数据库管理软件：Navicat

1.2 程序准备

按照上一个项目，创建相关的实体类和EFLearnDbContext，修改EFLearnDbContext的内容，这里介绍下简单的配置。

  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseMySql("your database url", Microsoft.EntityFrameworkCore.ServerVersion.Parse("8.0.28-mysql"));

            optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information);//设置日志显示
        }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
        {

            #region Student基本配置

            modelBuilder.Entity<Student>(entity =>
            {
                entity.Property(x => x.StudentId).ValueGeneratedOnAdd();//设置Id自增
                entity.Property(x => x.Name).HasMaxLength(50).IsUnicode().IsRequired();//设置姓名最大长度为50，字符为unicode,不能为空                                                   
                entity.Property(x => x.Sex).HasMaxLength(5).IsUnicode().IsRequired();//设置性别最大长度为5 字符为Unicode，不能为空                                                                   
                entity.HasOne(x => x.Address).WithOne(x => x.Student).HasForeignKey<StudentAddress>(ad => ad.StudentId);//一对一只需要配置一个类就行了，
            });

            #endregion

            #region  Course配置

            modelBuilder.Entity<Course>(entity =>
            {
                entity.Property(x => x.Name).HasMaxLength(50).IsUnicode();
                entity.HasOne(x => x.Teacher).WithMany(t => t.Courses);// //课程一对一
                entity.HasMany(x => x.Students).WithMany(st => st.Courses);//多对多
            });
            #endregion

            #region Teacher配置

            modelBuilder.Entity<Teacher>(entity =>
            {
                entity.Property(x => x.Name).HasMaxLength(50).IsUnicode();
                entity.Property(x => x.Title).HasMaxLength(50).IsUnicode();
            });

            #endregion

            #region 配置地址

            modelBuilder.Entity<StudentAddress>(entity =>
            {
                entity.Property(x => x.City).HasMaxLength(100).IsRequired().IsUnicode();
                entity.Property(x => x.Address).HasMaxLength(500).IsRequired().IsUnicode();
            });
            #endregion

        

        }

1.3 准备数据

准备一下数据，在Program里如下添加这些基础数据，运行后注释下面的代码。

using (EFLearnDbContext dbContext=new EFLearnDbContext())
{


    #region 添加一些数据

    Course course1 = new Course() { CourseId = 3001, Name = "高等数学" };
    Course course2 = new Course() { CourseId = 3002, Name = "计算机原理" };
    Course course3 = new Course() { CourseId = 3003, Name = "操作系统原理" };
    Course course4 = new Course() { CourseId = 3004, Name = "编译原理" };


    Teacher teacher1 = new Teacher() { TeacherId = 10001, Name = "张教授", Title = "教授",Age=55 };
    Teacher teacher2 = new Teacher() { TeacherId = 10002, Name = "王讲师", Title = "讲师",Age=58 };

    teacher1.Courses = new Course[] { course1, course2 };
    teacher2.Courses = new Course[] { course3, course4 };

    dbContext.Teachers.AddRange(teacher1, teacher2);

    StudentAddress Address1 = new StudentAddress()
    {
        StudentAddressId = 37001,
        Address = "北京朝阳区",
        City = "北京",
        
    };

    StudentAddress Address2 = new StudentAddress()
    {
        StudentAddressId = 37002,
        Address = "上海徐汇区",
        City = "上海",
    };

    StudentAddress Address3 = new StudentAddress()
    {
        StudentAddressId = 37003,
        Address = "广州白云区",
        City = "广州",
    };



    Student student1 = new Student()
    {
        StudentId = 2022001,
        Name = "王二小",
        Age = 19,
        Sex = "男",
        Address = Address1,
        Courses = new Course[] { course1, course2, course3 },
        Hobby="篮球"

    };

    Student student2 = new Student()
    {
        StudentId = 2022002,
        Name = "张小五",
        Age = 20,
        Sex = "男",
        Address = Address2,
        Courses = new Course[] { course1, course2 },
        Hobby="打游戏"
    };

    Student student3 = new Student()
    {
        StudentId = 2022003,
        Name = "刘小花",
        Age = 20,
        Sex = "女",
        Address = Address3,
        Courses = new Course[] { course1, course3 },
        Hobby="看书"

    };

    dbContext.Students.AddRange(student1, student2, student3);


    dbContext.SaveChanges();
    #endregion




}

2.基础回顾以及探寻

2.1 单个查询

单个查询，主要三种方式:

• 采用Single方法
• 采用Find方法,注意Find方法必须查询的是键值
• 采用where后的FirstOrDefault

    #region 查询单个数据
    var teacher = dbContext.Teachers.Single(x => x.Name == "张教授");
    var techer = dbContext.Teachers.Find(new object[]{ 10001,10002});
    var techear = dbContext.Teachers.Where(x => x.Age > 45).FirstOrDefault();
    #endregion

2.2 查询所有的数据

采用 ToList()/ToArray()查询所有的数据。

 var teachers=dbContext.Teachers.ToList();

 var courses = dbContext.Courses.ToArray();

2.3 筛选和过滤查询

采用Where查询

  var techear = dbContext.Teachers.Where(x => x.Age > 45).FirstOrDefault();

2.4 探究原理

我们可以看到

 var techear = dbContext.Teachers.Where(x => x.Age > 45).FirstOrDefault();

中的linq语句被翻译成了Sql语句如下：

      SELECT `t`.`TeacherId`, `t`.`Age`, `t`.`Name`, `t`.`Title`
      FROM `Teachers` AS `t`
      WHERE `t`.`Age` > 45
      LIMIT 1

EF Core 就是将 LINQ 查询的表示形式翻译成Sql语句，传给数据库提供程序，然后进行数据的查询，将程序好的数据封装成实体类返回。

3.客户端评估和服务端评估

客户端评估： 将数据在内存中进行过滤筛选，就是所谓的客户端评估。
服务端评估: 把查询操作翻译成SQL语句在数据库服务器里进行过滤筛选的操作，就是服务端评估。

3.1 IEnumerable And IQueryable

在EFCore中，客户端评估，主要是采用 IEnumerable的扩展方法，服务端评估只要采用的是IQueryable的扩展方法。

如上图，查看采用服务端评估和客户端评估，分别采用了IQueryable和IEnumerable的扩展方法。

注意:
1.在某些情况服务端筛选是无法完成的这个时候必须转为客户端筛选例如：

将上述代码改成下面的形势就能完美通过

 var courese = dbContext.Courses.ToList().Where(x => JsonConvert.SerializeObject(x.Name).Length>5).ToArray();

2.将服务端评估转为客户端评估的方式有下面两种方式：

• 通过调用 AsEnumerable 方式 dbContext.Course…AsEnumerable().Where(xxxxxx);
• 通过调用ToList()/ToArray()等方法，但使用 ToList 将通过创建列表来进行缓冲，因此也会占用额外的内存。但是通过AsEnumerable方法不会。

3.尽管客户端评估由于方法无法在数据库上执行，所有数据都将被拉取到内存中，然后在客户端应用过滤。这就会导致内存过高，造成性能下降。

3.2 IQueryable的延迟性

在构建查询表达式的时候，会有延迟性，就是虽然构建了表达式，但是不会立刻生成Sql发到数据库中，如下代码，运行的时候，可以看到并没有生成相关的sql查询指令。

  var CourseIds = dbContext.Courses.Where(x => x.Name.Length > 4).Select(p => new { p.CourseId, p.Name });

只有调用以下方法的时候，才会执行正常的查询。
调用 LINQ 运算符时，只会构建查询在内存中的表示形式。 使用结果时，查询只会发送到数据库。

导致查询发送到数据库的最常见操作如下：

• 使用 for 遍历结果的时候
• 使用 ToList、ToArray、Single、First 等操作或者异步形式的操作的时候。
• 使用Count、Sum、Average等聚合或者异步形式聚合操作方式的时候。

4.跟踪与非跟踪查询

在一次普通的查询中，EFCore主要经历以下几个步骤：

1. 连接数据库创建实体类，并填充非导航属性、字段等。
   2.填入主键和外键的关系，描述数据是如何关联的，然后配置导航属性。
   3.给返回的实体类创建一个跟踪快照，这个跟踪快照是实体类的副本，等这个实体类被修改时候，这些改变将在调用SaveChange的时候写入到数据库中

而EFCore默认是跟踪查询，从上可以知道，采用跟踪查询的效率没有采用非跟踪查询的效率高。
**注意：**无键实体类型，DbContext不会进行跟踪，因此不会对数据库进行插入、更新或删除这些操作

查看数据库 teacher表，原始数据如下：

  //跟踪查询
    var trackingTeacher = dbContext.Teachers.Single(x => x.TeacherId == 10001);
    trackingTeacher.Age = 67;
    trackingTeacher.Title = "叫兽";
    dbContext.SaveChanges();

    //非跟踪查询
    trackingTeacher=dbContext.Teachers.AsNoTracking().Single(x => x.TeacherId == 10001);
    trackingTeacher.Age = 65;
    trackingTeacher.Title = "高级教授";

经过上述查询后，修改数据如下：

可以看到跟踪查询可以修改数据，但是非跟踪查询不行。
修改代码如下，就可以显式修改数据。

  var trackingTeacher = dbContext.Teachers.AsNoTracking().Single(x => x.TeacherId == 10001);
    trackingTeacher.Age = 65;
    trackingTeacher.Title = "高级教授";
    dbContext.Teachers.Update(trackingTeacher);
    dbContext.SaveChanges();

4.1 全局的跟踪和非跟踪

在 Dbcontext类的OnConfiguring里进行设置。

//开启追踪，默认是TrackAll，可以显示声明
optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.TrackAll);
//请勿尝试，会禁止追踪所有实体
//optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);

在跟踪查询中，EFCore进行查询时， 如果结果中多次包含相同的实体，则每次会返回相同的实例。 非跟踪查询不会使用更改跟踪器，也不会执行标识解析。 因此会返回实体的新实例，即使结果中多次包含相同的实体也是如此。

从 EF Core 5.0 开始，可以在同一个查询中结合使用上述两种行为。 也就是说，可以使用非跟踪查询并对结果执行标识解析。 我们添加了另一个运算符AsNoTrackingWithIdentityResolution()，就像添加 AsNoTracking() 可查询运算符一样。 简单来说，就是采用 AsNoTrackingWithIdentityResolution()后，虽然没有追踪但是查询到相同的实体实例，是相同的。

 var trackingTeacher1 = dbContext.Teachers.AsNoTrackingWithIdentityResolution().Single(x => x.TeacherId == 10001);
    var trackingTeacher2 = dbContext.Teachers.AsNoTrackingWithIdentityResolution().First(x=>x.TeacherId==10001);

    Console.WriteLine("teacher1:"+trackingTeacher1.GetHashCode());
    Console.WriteLine("teacher2:" + trackingTeacher1.GetHashCode());
    
    trackingTeacher1.Title = "特级教授";
    trackingTeacher2.Title = "名誉叫兽";
    dbContext.SaveChanges();

运行上述代码后，结果如下，可以看到两个查询结果返回的实例是相同的，但是修改数据没有起作用。

4.2 特殊情况的跟踪和非跟踪

即使查询的结果类型不是实体类型，默认情况下 EF Core 也会跟踪结果中包含的实体类型。 在以下返回匿名类型的查询中

 var studentsCourse = dbContext.Students.Select(x => new {Student=x, CourseCount=x.Courses.Count() }).First();

    Console.WriteLine($"StudentId:{studentsCourse.Student.StudentId}," +
        $"Name:{studentsCourse.Student.Name},Age:{studentsCourse.Student.Age},Hobby:{studentsCourse.Student.Hobby}");
    studentsCourse.Student.Age = 21;
    studentsCourse.Student.Hobby = "打游戏";

    dbContext.SaveChanges();

运行上述代码后，查询结果如下，可以看到我们在匿名类中修改了Student的信息后，调用
SaveChange()后，可以看出修改可以被写入数据库。

但是如果查询的是不包含实体类的匿名类，那么，我们就无法将修改的信息写入数据库，实际上这个新的匿名类实例不会被EFCore追踪。**实际上新生成的匿名类，会被设置为只读类是无法被修改的。**查看如下代码：

 var  studentCoureseInfo = dbContext.Students.Select(x => new { StudentId = x.StudentId, Name=x.Name,Age=x.Age,Hobby=x.Hobby, CourseCount = x.Courses.Count() }).First();

    studentCoureseInfo.Age = 22; //无法通过编译
    studentCoureseInfo.Hobby = "打Dota";//无法通过编译
    dbContext.SaveChanges();

5. 原始SQL查询

FromSqlRaw 只能在直接位于 DbSet<> 上的查询根上使用。

   int age = 20;
    var students = dbContext.Students.FromSqlRaw($"Select * from Students where age> {0}", age);
    //var students = dbContext.Database.ExecuteSqlRaw($"Select * from Students where age> {0}",age);

执行结果如下，可以进行数据查询：

执行原始的Sql语句主要有两种方式：

1. 使用FromSqlRaw必须从DbSet<>上使用,可以返回结果集。
2. 使用ExecuteSqlRaw需要从DbContext上的Database上执行，无法返回查询结果，可以用于修改、删除这些没有结果集返回的操作。

注意： 在使用原始 SQL 查询引入任何用户提供的值时，必须注意防范 SQL 注入攻击。
具体来说不要使用$(“”)的值直接传入FromSqlRaw中，而建议采用类似 String.Format 语法，但提供的值包装在 DbParameter 中，且生成的参数名称插入到指定 {0} 占位符的位置。、

如下，直接采用插值内表达式会造成Sql注入如下代码

  string age = "100 or 1=1";
    var students = dbContext.Students.FromSqlRaw($"Select * from Students where age> {age}").ToList();
    Console.WriteLine("查询的数据个数:"+students.Count);

为了避免上述错误，可以使用FromSqlInterpolated 或者ExecuteSqlInterpolated 能够避免上述错误

 string age = "100 or 1=1";
    var students = dbContext.Students.FromSqlInterpolated($"Select * from Students where age> {age}").ToList();

    Console.WriteLine("查询的数据个数:"+students.Count);

查询显示结果如下：

除此之外，还可以采用如下的方法，在使用FromSqlRaw时的时候，采用SqlParameter参数的形式，如下：

    var age = new SqlParameter("age", 20);
    var students = dbContext.Students.FromSqlRaw($"Select * from Students where age>@age",age).ToList();
  Console.WriteLine("查询的数据个数:" + students.Count);

采用原始Sql方式进行查询还可以和Linq结合如下：

  var age = new SqlParameter("age", 20);
  var students = dbContext.Students.FromSqlRaw($"Select * from Students where age>@age",age).Where(s=>s.Name.Length>2).Include(s=>s.Courses).ToList();
  Console.WriteLine("查询的数据个数:" + students.Count);

EFCore 从入门到精通-5（谈谈迁移)
EFCore 从入门到精通-7（高级查询)