.Net 常用ORM框架对比：EF Core、FreeSql、SqlSuger (下篇)

前言： 　　本篇文章接上篇继续讲解： 　　接下来就是具体的业务逻辑实现了； 一：在Api中如何使用 　　1. 添加EfCoreController 空Api控制器，用于实现EF Core的代码，编写如下代码

 1     [Route("api/[controller]")]
 2     [ApiController]
 3     public class EfCoreController : ControllerBase
 4     {
 5         private readonly ILogger _logger;
 6         private readonly IDbContextFactory _efDbContext;
 7         public EfCoreController(ILogger logger, IDbContextFactory efDbContext)
 8         {
 9             this._logger = logger;
10             this._efDbContext = efDbContext;
11         }
12     }

　　2.添加FreeSqlController 空Api控制器，用于实现FreeSql的代码，编写如下代码（FreeSql支持两种方式：IFreeSql，DbContext）

 1     [Route("api/[controller]")]
 2     [ApiController]
 3     public class FreeSqlController : ControllerBase
 4     {
 5         private readonly IFreeSql _freeSql;
 6         private readonly FreeSqlContext _freeSqlContext;
 7         private readonly ILogger _logger;
 8         public FreeSqlController(FreeSqlContext freeSqlContext, IFreeSql freeSql, ILogger logger)
 9         {
10             this._freeSql = freeSql;
11             this._freeSqlContext = freeSqlContext;
12             this._logger = logger;
13         }
14     }

　　3.添加SqlSugerController 空Api控制器，用于实现SqlSuger的代码，编写如下代码

 1     [Route("api/[controller]")]
 2     [ApiController]
 3     public class SqlSugerController : ControllerBase
 4     {
 5         private readonly StudentService _studentService;
 6         private readonly ClassGradeService _classGradeService;
 7         private readonly CourseService _courseService;
 8         private readonly MiddleStudentCourseService _middleStudentCourse;
 9         private readonly MiddleClassCourseCervice _middleClassCourse;
10         private readonly ILogger _logger;
11         public SqlSugerController(ILogger logger,
12             StudentService studentService,
13             CourseService courseService,
14             MiddleStudentCourseService middleStudentCourse,
15             MiddleClassCourseCervice middleClassCourse,
16             ClassGradeService classGradeService)
17         {
18             this._studentService = studentService;
19             this._courseService = courseService;
20             this._classGradeService = classGradeService;
21             this._middleStudentCourse = middleStudentCourse;
22             this._middleClassCourse = middleClassCourse;
23             this._logger = logger;
24         }
25     }

二 ：添加

 　　1.EfCoreController中添加Action

  1         /// 

  2         /// 添加班级
  3         /// 
  4         /// 
  5         /// 
  6         [HttpPost("AddClass/{name}")]
  7         public IActionResult AddClass(string name)
  8         {
  9             var db = _efDbContext.ConnWriteOrRead(WriteAndReadEnum.Write);
 10             ClassGrade classGrade = new ClassGrade { Name = name };
 11             db.Add(classGrade);
 12             db.SaveChanges();
 13             return Ok(classGrade.Id);
 14         }
 15         /// 

 16         /// 添加课程和老师
 17         /// 
 18         /// 
 19         /// 
 20         [HttpPost("AddCourse/{name}")]
 21         public IActionResult AddCourse(string name)
 22         {
 23             var db = _efDbContext.ConnWriteOrRead(WriteAndReadEnum.Write);
 24             Course classGrade = new Course { Name = name, Teacher = name + "老师" };
 25             db.Add(classGrade);
 26             db.SaveChanges();
 27             return Ok(classGrade.Id);
 28         }
 29         /// 

 30         /// 给班级添加课程
 31         /// 
 32         /// 
 33         /// 
 34         [HttpPost("ClassAddCourse")]
 35         public IActionResult ClassAddCourse([FromBody] ClassAddCourse classAddCourse)
 36         {
 37             var db = _efDbContext.ConnWriteOrRead(WriteAndReadEnum.Write);
 38             var date = db.Classs.Include(t => t.Classs)
 39                 .Where(t => t.Id == classAddCourse.ClassId).First();
 40             foreach (var i in classAddCourse.CourseIds)
 41             {
 42                 if (!date.Classs.Select(t => t.Id).Contains(i))
 43                 {
 44                     date.Classs.Add(new MiddleClassCourse
 45                     {
 46                         ClassId = date.Id,
 47                         CourseId = i
 48                     });
 49                 }
 50             }
 51             return Ok(db.SaveChanges());
 52         }
 53         /// 

 54         /// 插入学生
 55         /// 
 56         /// 
 57         /// 
 58         [HttpPost("AddStudent")]
 59         public IActionResult AddStudent([FromBody] AddStudent student)
 60         {
 61             try
 62             {
 63                 StringBuilder str = new StringBuilder();
 64                 Stopwatch stopwatch2 = new Stopwatch();
 65                 stopwatch2.Start();
 66                 var db = _efDbContext.ConnWriteOrRead(WriteAndReadEnum.Write);
 67                 List Students11 = new List();
 68                 for (int i = 0; i < student.Count; i++)
 69                 {
 70                     Students11.Add(new Student
 71                     {
 72                         ClassId = student.ClassId,
 73                         Name = student.Count.ToString(),
 74                         Age = 20,
 75                         Sex = 1,
 76                     });
 77                 }
 78                 db.Students.AddRange(Students11);
 79                 db.SaveChanges();
 80                 stopwatch2.Stop();
 81                 str.Append($"DbContext插入{student.Count}条数据耗时：" + stopwatch2.ElapsedMilliseconds.ToString());
 82 
 83                 _logger.LogInformation(str.ToString());
 84                 return Ok(str.ToString());
 85             }
 86             catch (Exception ex)
 87             {
 88                 return Ok(ex.Message);
 89             }
 90         }
 91         /// 

 92         /// 插入学生-带选修课程
 93         /// 
 94         /// 
 95         /// 
 96         [HttpPost("AddStudentCourse")]
 97         public IActionResult AddStudentCourse([FromBody] AddStudentCourse course)
 98         {
 99             try
100             {
101                 StringBuilder str = new StringBuilder();
102                 Stopwatch stopwatch1 = new Stopwatch();
103                 stopwatch1.Start();
104                 var db = _efDbContext.ConnWriteOrRead(WriteAndReadEnum.Write);
105                 for (int i = 0; i < course.StudentCount; i++)
106                 {
107                     db.Students.Add(new Student
108                     {
109                         ClassId = course.ClassId,
110                         Name = i.ToString(),
111                         Age = 22,
112                         Sex = 0,
113                         Courses = new List { new MiddleStudentCourse { CourseId = course.CourseId } }
114                     });
115                 }
116                 db.SaveChanges();
117                 stopwatch1.Stop();
118                 str.Append($"方法1插入{course.StudentCount * 2}条数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());
119 
120                 _logger.LogInformation(str.ToString());
121                 return Ok(str.ToString());
122             }
123             catch (Exception ex)
124             {
125                 return Ok(ex.Message);
126             }
127         }

　　2.FreeSqlController中添加Action

  1         /// 

  2         /// 添加班级
  3         /// 
  4         /// 
  5         /// 
  6         [HttpPost("AddClass/{name}")]
  7         public IActionResult AddClass(string name)
  8         {
  9             ClassGrade classGrade = new ClassGrade { Name = name };
 10             var classid = _freeSql.Insert(classGrade).ExecuteIdentity();
 11             return Ok(classid);
 12         }
 13         /// 

 14         /// 添加课程和老师
 15         /// 
 16         /// 
 17         /// 
 18         [HttpPost("AddCourse/{name}")]
 19         public IActionResult AddCourse(string name)
 20         {
 21             Course classGrade = new Course { Name = name, Teacher = name + "老师" };
 22             var classid = _freeSql.Insert(classGrade).ExecuteIdentity();
 23             return Ok(classid);
 24         }
 25         /// 

 26         /// 给班级添加课程
 27         /// 
 28         /// 
 29         /// 
 30         [HttpPost("ClassAddCourse")]
 31         public IActionResult ClassAddCourse([FromBody] ClassAddCourse classAddCourse)
 32         {
 33             var date = _freeSql.Select()
 34                 .IncludeMany(t => t.Classs)
 35                 .Where(t => t.Id == classAddCourse.ClassId).First();
 36             var Classs = new List();
 37             foreach (var i in classAddCourse.CourseIds)
 38             {
 39                 if (!date.Classs.Select(t => t.Id).Contains(i))
 40                 {
 41                     Classs.Add(new MiddleClassCourse
 42                     {
 43                         ClassId = date.Id,
 44                         CourseId = i
 45                     });
 46                 }
 47             }
 48             return Ok(_freeSql.Insert(Classs).ExecuteAffrows());
 49         }
 50         /// 

 51         /// 插入学生
 52         /// 
 53         /// 
 54         /// 
 55         [HttpPost("AddStudent")]
 56         public IActionResult AddStudent([FromBody] AddStudent student)
 57         {
 58             try
 59             {
 60                 StringBuilder str = new StringBuilder();
 61                 Stopwatch stopwatch2 = new Stopwatch();
 62                 stopwatch2.Start();
 63                 List Students11 = new List();
 64                 for (int i = 0; i < student.Count; i++)
 65                 {
 66                     Students11.Add(new Student
 67                     {
 68                         ClassId = student.ClassId,
 69                         Name = student.Count.ToString(),
 70                         Age = 20,
 71                         Sex = 1,
 72                     });
 73                 }
 74                 _freeSqlContext.Students.AddRange(Students11);
 75                 _freeSqlContext.SaveChanges();
 76                 stopwatch2.Stop();
 77                 str.Append($"DbContext插入{student.Count}条数据耗时：" + stopwatch2.ElapsedMilliseconds.ToString());
 78 
 79                 //Stopwatch stopwatch3 = new Stopwatch();
 80                 //stopwatch3.Start();
 81                 //List Students = new List();
 82                 //for (int i = 0; i < student.count; i++)
 83                 //{
 84                 //    Students.Add(
 85                 //        new Student
 86                 //        {
 87                 //            ClassId = student.classId,
 88                 //            Name = student.count.ToString(),
 89                 //            Age = 20,
 90                 //            Sex = 1,
 91                 //        });
 92                 //}
 93                 //_freeSql.Insert(Students).ExecuteAffrows();
 94                 //stopwatch3.Stop();
 95                 //str.AppendLine($"批量插入{student.count}条数据耗时：" + stopwatch3.ElapsedMilliseconds.ToString());
 96 
 97                 _logger.LogInformation(str.ToString());
 98                 return Ok(str.ToString());
 99             }
100             catch (Exception ex)
101             {
102                 return Ok(ex.Message);
103             }
104         }
105         /// 

106         /// 插入学生-带选修课程
107         /// 
108         /// 
109         /// 
110         [HttpPost("AddStudentCourse")]
111         public IActionResult AddStudentCourse([FromBody] AddStudentCourse course)
112         {
113             try
114             {
115                 StringBuilder str = new StringBuilder();
116                 #region 自增主键限制，不能使用
117                 //Stopwatch stopwatch1 = new Stopwatch();
118                 //stopwatch1.Start();
119                 //for (int i = 0; i < course.StudentCount; i++)
120                 //{
121                 //    _freeSqlContext.Students.Add(new Student
122                 //    {
123                 //        ClassId = course.ClassId,
124                 //        Name = i.ToString(),
125                 //        Age = 22,
126                 //        Sex = 0,
127                 //        Courses =new List { new MiddleStudentCourse { CourseId = course.CourseId } }
128                 //    });
129                 //}
130                 //_freeSqlContext.SaveChanges();
131                 //stopwatch1.Stop();
132                 //str.AppendLine($"方法1插入{course.StudentCount * 2}条数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());
133                 #endregion
134 
135                 Stopwatch stopwatch2 = new Stopwatch();
136                 stopwatch2.Start();
137                 for (int i = 0; i < course.StudentCount; i++)
138                 {
139                     var id = _freeSql.Insert(new Student
140                     {
141                         ClassId = course.ClassId,
142                         Name = i.ToString(),
143                         Age = 22,
144                         Sex = 0,
145                     }).ExecuteIdentity();
146 
147                     MiddleStudentCourse msc = new MiddleStudentCourse { StudentId = (int)id, CourseId = course.CourseId };
148                     _freeSql.Insert(msc).ExecuteAffrows();
149                 }
150                 stopwatch2.Stop();
151                 str.Append($"方法2插入{course.StudentCount * 2}条数据耗时：" + stopwatch2.ElapsedMilliseconds.ToString());
152 
153                 _logger.LogInformation(str.ToString());
154                 return Ok(str.ToString());
155             }
156             catch (Exception ex)
157             {
158                 return Ok(ex.Message);
159             }
160         }

　　3.SqlSugerController 中添加Action

  1         /// 

  2         /// 添加班级
  3         /// 
  4         /// 
  5         /// 
  6         [HttpPost("AddClass/{name}")]
  7         public IActionResult AddClass(string name)
  8         {
  9             ClassGrade classGrade = new ClassGrade { Name = name };
 10             var classid = _classGradeService.Insert(classGrade);
 11             return Ok(classid);
 12         }
 13         /// 

 14         /// 添加课程和老师
 15         /// 
 16         /// 
 17         /// 
 18         [HttpPost("AddCourse/{name}")]
 19         public IActionResult AddCourse(string name)
 20         {
 21             Course classGrade = new Course { Name = name, Teacher = name + "老师" };
 22             return Ok(_courseService.Insert(classGrade));
 23         }
 24         /// 

 25         /// 给班级添加课程
 26         /// 
 27         /// 
 28         /// 
 29         [HttpPost("ClassAddCourse")]
 30         public IActionResult ClassAddCourse([FromBody] ClassAddCourse classAddCourse)
 31         {
 32 
 33             var eds = _middleClassCourse.AsQueryable().Where(t => t.ClassId == classAddCourse.ClassId).ToList();
 34             var Classs = new List();
 35             foreach (var i in classAddCourse.CourseIds)
 36             {
 37                 if (!eds.Select(t => t.CourseId).Contains(i))
 38                 {
 39                     Classs.Add(new MiddleClassCourse
 40                     {
 41                         ClassId = classAddCourse.ClassId,
 42                         CourseId = i
 43                     });
 44                 }
 45             }
 46             return Ok(_middleClassCourse.InsertRange(Classs));
 47         }
 48         /// 

 49         /// 插入学生
 50         /// 
 51         /// 
 52         /// 
 53         [HttpPost("AddStudent")]
 54         public IActionResult AddStudent([FromBody] AddStudent student)
 55         {
 56             try
 57             {
 58                 StringBuilder str = new StringBuilder();
 59                 Stopwatch stopwatch3 = new Stopwatch();
 60                 stopwatch3.Start();
 61                 List Students = new List();
 62                 for (int i = 0; i < student.Count; i++)
 63                 {
 64                     Students.Add(
 65                         new Student
 66                         {
 67                             ClassId = student.ClassId,
 68                             Name = student.Count.ToString(),
 69                             Age = 20,
 70                             Sex = 1,
 71                         });
 72                 }
 73                 _studentService.InsertRange(Students);
 74                 stopwatch3.Stop();
 75                 str.Append($"批量插入{student.Count}条数据耗时：" + stopwatch3.ElapsedMilliseconds.ToString());
 76 
 77                 _logger.LogInformation(str.ToString());
 78                 return Ok(str.ToString());
 79             }
 80             catch (Exception ex)
 81             {
 82                 return Ok(ex.Message);
 83             }
 84         }
 85         /// 

 86         /// 插入学生-带选修课程
 87         /// 
 88         /// 
 89         /// 
 90         [HttpPost("AddStudentCourse")]
 91         public IActionResult AddStudentCourse([FromBody] AddStudentCourse course)
 92         {
 93             try
 94             {
 95                 StringBuilder str = new StringBuilder();
 96                 Stopwatch stopwatch2 = new Stopwatch();
 97                 stopwatch2.Start();
 98                 var service2 = _studentService.AsSugarClient();
 99                 var service3 = _middleStudentCourse.AsSugarClient();
100                 for (int i = 0; i < course.StudentCount; i++)
101                 {
102                     var id = service2.Insertable(new Student()
103                     {
104                         Age = 18,
105                         ClassId = course.ClassId,
106                         Name = i.ToString(),
107                         Sex = 1,
108                         Id = 0,//自增列
109 
110                     }).ExecuteReturnIdentity();
111                     var Courses = new List() { new MiddleStudentCourse
112                               {
113                                    CourseId = course.CourseId,
114                                    StudentId =id
115                               }
116                      };
117                     service3.Insertable(Courses).ExecuteCommand();
118                 }
119                 stopwatch2.Stop();
120                 str.Append($"方法1 单条插入{course.StudentCount * 2}条数据耗时：" + stopwatch2.ElapsedMilliseconds.ToString());
121 
122                 //Stopwatch stopwatch1 = new Stopwatch();
123                 //stopwatch1.Start();
124                 //var service = _studentService.AsSugarClient();
125                 //for (int i = 0; i < course.StudentCount; i++)
126                 //{
127                 //    service.Insertable(new Student()
128                 //    {
129                 //        Age = 18,
130                 //        ClassId = course.ClassId,
131                 //        Name = i.ToString(),
132                 //        Sex = 1,
133                 //        Id = 0,//自增列
134                 //        Courses = new List() { new MiddleStudentCourse
135                 //              {
136                 //                   CourseId = course.CourseId,
137                 //                   StudentId =0//需要自动获取订单的自增列
138                 //              }
139                 //        }
140                 //    })
141                 //    .AddSubList(it => it.Courses.First().StudentId)//设置item表的OrderId等于订单自增列
142                 //    .ExecuteReturnPrimaryKey();
143                 //}
144                 //stopwatch1.Stop();
145                 //str.AppendLine($"方法2 单条插入{course.StudentCount * 2}条数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());
146 
147                 _logger.LogInformation(str.ToString());
148                 return Ok(str.ToString());
149             }
150             catch (Exception ex)
151             {
152                 return Ok(ex.Message);
153             }
154         }

　　4.运行结果对比： 　　 三：查询

 　　1.EfCoreController中添加Action

  1         /// 

  2         /// 查询学生
  3         /// 
  4         /// 
  5         /// 
  6         [HttpGet("GetStudent/{id}")]
  7         public IActionResult GetStudent(int id)
  8         {
  9             try
 10             {
 11                 StringBuilder str = new StringBuilder();
 12                 Stopwatch stopwatch1 = new Stopwatch();
 13                 stopwatch1.Start();
 14                 var db = _efDbContext.ConnWriteOrRead(WriteAndReadEnum.Read);
 15                 var student1 = db.Students
 16                     .Include(a => a.Class)
 17                     .Include(a => a.Courses).ThenInclude(t => t.Course)
 18                     .Where(a => a.Id == id)
 19                     .Select(a => new StudentDto
 20                     {
 21                         ClassName = a.Class.Name,
 22                         //CourseIds = a.Courses.Select(t => t.CourseId).ToList(),
 23                         //Courses = a.Courses.Select(t => t.Course).ToList()
 24                     }).AsNoTracking()
 25                     .First();
 26 
 27                 stopwatch1.Stop();
 28                 str.Append($"查询数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());
 29 
 30                 _logger.LogInformation(str.ToString());
 31                 return Ok(student1);
 32             }
 33             catch (Exception ex)
 34             {
 35                 return Ok(ex.Message);
 36             }
 37         }
 38         /// 

 39         /// 查询班级中的所有学生
 40         /// 
 41         /// 
 42         /// 
 43         [HttpGet("GetClassStudent/{id}")]
 44         public IActionResult GetClassStudent(int id)
 45         {
 46             try
 47             {
 48                 StringBuilder str = new StringBuilder();
 49                 Stopwatch stopwatch2 = new Stopwatch();
 50                 stopwatch2.Start();
 51                 var db = _efDbContext.ConnWriteOrRead(WriteAndReadEnum.Read);
 52                 var students = db.Classs
 53                     .Include(a => a.Students)
 54                     .Where(a => a.Id == id)
 55                     .AsNoTracking()
 56                     .First();
 57                 stopwatch2.Stop();
 58                 str.Append($"查询数据耗时：" + stopwatch2.ElapsedMilliseconds.ToString());
 59 
 60                 _logger.LogInformation(str.ToString());
 61                 return Ok(str.ToString());
 62             }
 63             catch (Exception ex)
 64             {
 65                 return Ok(ex.Message);
 66             }
 67         }
 68         /// 

 69         /// 查询学生的所有课程
 70         /// 
 71         /// 
 72         /// 
 73         [HttpGet("GetStudentCourse/{id}")]
 74         public IActionResult GetStudentCourse(int id)
 75         {
 76             try
 77             {
 78                 StringBuilder str = new StringBuilder();
 79                 Stopwatch stopwatch1 = new Stopwatch();
 80                 stopwatch1.Start();
 81                 var db = _efDbContext.ConnWriteOrRead(WriteAndReadEnum.Read);
 82                 var student = db.Students
 83                     .Where(t => t.Id == id).First();
 84 
 85                 var item1 = (from a in db.Set()
 86                             join b in db.Set() on a.CourseId equals b.Id
 87                             select new {a.ClassId, b.Id,b.Name})
 88                             .Where(t => t.ClassId == student.ClassId)
 89                             .Select(a => new { a.Id,a.Name})
 90                             .ToList();
 91 
 92                 var item2 = (from a in db.Set()
 93                             join b in db.Set() on a.CourseId equals b.Id
 94                             select new {a.StudentId, b.Id, b.Name })
 95                             .Where(t => t.StudentId == id)
 96                             .Select(a => new { a.Id, a.Name })
 97                             .ToList();
 98 
 99                 item1.AddRange(item2);
100                 stopwatch1.Stop();
101                 str.Append($"查询数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());
102 
103                 _logger.LogInformation(str.ToString());
104                 return Ok(str.ToString());
105             }
106             catch (Exception ex)
107             {
108                 return Ok(ex.Message);
109             }
110         }

　　2.FreeSqlController中添加Action

  1         /// 

  2         /// 查询学生
  3         /// 
  4         /// 
  5         /// 
  6         [HttpGet("GetStudent/{id}")]
  7         public IActionResult GetStudent(int id)
  8         {
  9             try
 10             {
 11                 StringBuilder str = new StringBuilder();
 12                 Stopwatch stopwatch1 = new Stopwatch();
 13                 stopwatch1.Start();
 14                 var student1 = _freeSql.Select()
 15                     .Include(a => a.Class)
 16                     .IncludeMany(a => a.Courses, then => then.Include(t => t.Course))
 17                     .Where(a => a.Id == id)
 18                     .First(a => new StudentDto
 19                     {
 20                         ClassName = a.Class.Name,
 21                         //CourseIds = a.Courses.AsSelect().ToList(a => a.CourseId)//一对多失败
 22                         //Courses = a.Courses.AsSelect().ToList(a => a.Course),//一对多失败
 23                         //Courses = a.Courses.AsSelect().ToList(t => new Course { Id = t.Course.Id, Name = t.Course.Name })//一对多失败
 24                     });
 25 
 26                 stopwatch1.Stop();
 27                 str.Append($"IFreeSql查询数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());
 28 
 29                 {
 30                     //Stopwatch stopwatch2 = new Stopwatch();
 31                     //stopwatch2.Start();
 32                     //var student = _freeSqlContext.Students.Select
 33                     //    .Include(a => a.Class)
 34                     //    .IncludeMany(a => a.Courses, then => then.Include(t => t.Course))
 35                     //    .Where(a => a.Id == id + 1)
 36                     //    .First(a => new StudentDto
 37                     //    {
 38                     //        ClassName = a.Class.Name,
 39                     //    });
 40                     //stopwatch2.Stop();
 41                     //str.AppendLine($"DbContext查询数据耗时：" + stopwatch2.ElapsedMilliseconds.ToString());
 42                 }
 43 
 44                 _logger.LogInformation(str.ToString());
 45                 return Ok(student1);
 46             }
 47             catch (Exception ex)
 48             {
 49                 return Ok(ex.Message);
 50             }
 51         }
 52         /// 

 53         /// 查询班级中的所有学生
 54         /// 
 55         /// 
 56         /// 
 57         [HttpGet("GetClassStudent/{id}")]
 58         public IActionResult GetClassStudent(int id)
 59         {
 60             try
 61             {
 62                 StringBuilder str = new StringBuilder();
 63                 Stopwatch stopwatch2 = new Stopwatch();
 64                 stopwatch2.Start();
 65                 var students = _freeSqlContext.ClassGrades.Select
 66                     .IncludeMany(a => a.Students)
 67                     .Where(a => a.Id == id)
 68                     .First();
 69                 stopwatch2.Stop();
 70                 str.Append($"DbContext查询数据耗时：" + stopwatch2.ElapsedMilliseconds.ToString());
 71 
 72                 {
 73                     //Stopwatch stopwatch1 = new Stopwatch();
 74                     //stopwatch1.Start();
 75                     //var students1 = _freeSql.Select()
 76                     //    .IncludeMany(a => a.Students)
 77                     //    .Where(a => a.Id == id)
 78                     //    .ToSql();
 79                     //stopwatch1.Stop();
 80                     //str.AppendLine($"IFreeSql查询数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());
 81                 }
 82 
 83                 _logger.LogInformation(str.ToString());
 84                 return Ok(str.ToString());
 85             }
 86             catch (Exception ex)
 87             {
 88                 return Ok(ex.Message);
 89             }
 90         }
 91         /// 

 92         /// 查询学生的所有课程
 93         /// 
 94         /// 
 95         /// 
 96         [HttpGet("GetStudentCourse/{id}")]
 97         public IActionResult GetStudentCourse(int id)
 98         {
 99             try
100             {
101                 StringBuilder str = new StringBuilder();
102                 Stopwatch stopwatch1 = new Stopwatch();
103                 stopwatch1.Start();
104                 var student = _freeSql.Select().Where(t => t.Id == id).First();
105                 var item1 = _freeSql.Select().From((a, b) => a
106                     .LeftJoin(a => a.CourseId == b.Id)
107                     .Where(a => a.ClassId == student.ClassId))
108                     .ToList((a, b) => new { b.Id, b.Name });
109 
110                 var item2 = _freeSql.Select().From((a, b) => a
111                     .LeftJoin(a => a.CourseId == b.Id)
112                     .Where(a => a.StudentId == id))
113                     .ToList((a, b) => new { b.Id, b.Name });
114 
115                 item1.AddRange(item2);
116 
117                 stopwatch1.Stop();
118                 str.Append($"查询数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());
119 
120                 _logger.LogInformation(str.ToString());
121                 return Ok(str.ToString());
122             }
123             catch (Exception ex)
124             {
125                 return Ok(ex.Message);
126             }
127         }

　　3.SqlSugerController 中添加Action

  1         /// 

  2         /// 查询学生
  3         /// 
  4         /// 
  5         /// 
  6         [HttpGet("GetStudent/{id}")]
  7         public IActionResult GetStudent(int id)
  8         {
  9             try
 10             {
 11                 StringBuilder str = new StringBuilder();
 12                 Stopwatch stopwatch2 = new Stopwatch();
 13                 stopwatch2.Start();
 14                 var student1 = _studentService.AsQueryable()
 15                     .Mapper((it, cache) =>
 16                     {
 17                         var allCourses = cache.Get(ol =>
 18                         {
 19                             var olIds = ol.Select(t => t.Id).ToList();
 20                             return _middleStudentCourse.AsQueryable()
 21                                 .In(a => a.StudentId, olIds)
 22                                 .Mapper(b => b.Course, b => b.CourseId).ToList();
 23                         });
 24                         it.Courses = allCourses.Where(i => i.StudentId == it.Id).ToList();
 25                     })
 26                     .Mapper(a => a.Class, a => a.ClassId)
 27                     .Where(a => a.Id == id)
 28                     .Select(t => new StudentDto())
 29                     .First();
 30                 stopwatch2.Stop();
 31                 str.Append($"方法2查询数据耗时：" + stopwatch2.ElapsedMilliseconds.ToString());
 32 
 33                 {
 34                     //Stopwatch stopwatch1 = new Stopwatch();
 35                     //stopwatch1.Start();
 36                     //var student = _studentService.AsQueryable()
 37                     //    //.Mapper((it, cache) =>
 38                     //    //{
 39                     //    //    var allCourses = cache.GetListByPrimaryKeys(vmodel => vmodel.Id);
 40                     //    //    it.Courses = allCourses.Where(i => i.StudentId == it.Id).ToList();//一对多加载失败
 41                     //    //})
 42                     //    .Mapper(a => a.Class, a => a.ClassId)
 43                     //    .Where(a => a.Id == id)
 44                     //    //.Select(t => new StudentDto())
 45                     //    //.Select(t => new StudentDto
 46                     //    //{
 47                     //    //    Age = t.Age,
 48                     //    //    //ClassName = t.Class.Name//无法实现
 49                     //    //})
 50                     //    .First();
 51                     //stopwatch1.Stop();
 52                     //str.AppendLine($"方法1查询数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());
 53                 }
 54 
 55                 _logger.LogInformation(str.ToString());
 56                 return Ok(student1);
 57             }
 58             catch (Exception ex)
 59             {
 60                 return Ok(ex.Message);
 61             }
 62         }
 63 
 64         /// 

 65         /// 查询班级中的所有学生
 66         /// 
 67         /// 
 68         /// 
 69         [HttpGet("GetClassStudent/{id}")]
 70         public IActionResult GetClassStudent(int id)
 71         {
 72             try
 73             {
 74                 StringBuilder str = new StringBuilder();
 75                 Stopwatch stopwatch1 = new Stopwatch();
 76                 stopwatch1.Start();
 77 
 78                 var students = _classGradeService.AsQueryable()
 79                     .Mapper((it, cache) =>
 80                     {
 81                         List allStudents = cache.Get(ol =>
 82                         {
 83                             var allStudentIds = ol.Select(x => x.Id).ToList();
 84                             return _studentService.AsQueryable()
 85                                 .In(a => a.ClassId, allStudentIds)
 86                                 .ToList();
 87                         });
 88                         it.Students = allStudents.Where(a => a.ClassId == it.Id).ToList();
 89                     })
 90                     .Where(a => a.Id == id)
 91                     .First();
 92                 stopwatch1.Stop();
 93                 str.Append($"查询数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());
 94 
 95                 _logger.LogInformation(str.ToString());
 96                 return Ok(str.ToString());
 97             }
 98             catch (Exception ex)
 99             {
100                 return Ok(ex.Message);
101             }
102         }
103         /// 

104         /// 查询学生的所有课程
105         /// 
106         /// 
107         /// 
108         [HttpGet("GetStudentCourse/{id}")]
109         public IActionResult GetStudentCourse(int id)
110         {
111             try
112             {
113                 StringBuilder str = new StringBuilder();
114                 Stopwatch stopwatch1 = new Stopwatch();
115                 stopwatch1.Start();
116 
117                 var student = _studentService.AsQueryable().Where(t => t.Id == id).First();
118                 var db = _studentService.AsSugarClient();
119                 var item1 = db.Queryable((a, b) => new JoinQueryInfos(
120                       JoinType.Left, a.CourseId == b.Id
121                   ))
122                  .Where(a => a.ClassId == student.ClassId)
123                  .Select((a, b) => new
124                  {
125                      b.Id,
126                      b.Name
127                  }).ToList();
128                 var item2 = db.Queryable((a, b) => new JoinQueryInfos(
129                     JoinType.Left, a.CourseId == b.Id
130                 ))
131                 .Where(a => a.StudentId == id)
132                 .Select((a, b) => new
133                 {
134                     b.Id,
135                     b.Name
136                 }).ToList();
137                 item1.AddRange(item2);
138 
139                 stopwatch1.Stop();
140                 str.Append($"查询数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());
141 
142                 _logger.LogInformation(str.ToString());
143                 return Ok(str.ToString());
144             }
145             catch (Exception ex)
146             {
147                 return Ok(ex.Message);
148             }
149         }

　　4.运行结果对比： 　　 四：修改

 　　1.EfCoreController中添加Action

 1         /// 

 2         /// 修改学生名
 3         /// 
 4         /// 
 5         /// 
 6         [HttpPost("UpdateStudent")]
 7         public IActionResult UpdateStudent([FromBody] UpdateStudent student)
 8         {
 9             try
10             {
11                 StringBuilder str = new StringBuilder();
12                 Stopwatch stopwatch1 = new Stopwatch();
13                 stopwatch1.Start();
14                 var db = _efDbContext.ConnWriteOrRead(WriteAndReadEnum.Write);
15                 var date = new Student { Id = student.Id, Age = student.Age, Sex = student.Sex, Name = student.Name };
16                 db.Students.Attach(date);
17                 db.Entry(date).Property(p => p.Name).IsModified = true;
18                 db.Entry(date).Property(p => p.Sex).IsModified = true;
19                 db.Entry(date).Property(p => p.Age).IsModified = true;
20                 db.SaveChanges();
21                 stopwatch1.Stop();
22                 str.Append($"指定列修改数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());
23 
24                 //Stopwatch stopwatch3 = new Stopwatch();
25                 //stopwatch3.Start();
26                 //var db = _efDbContext.ConnWriteOrRead(WriteAndReadEnum.Write);
27                 //var date = db.Students.Where(a => a.Id == student.Id).First();
28                 //date.Name = student.Name;
29                 //date.Sex = student.Sex;
30                 //date.Age = student.Age;
31                 //db.SaveChanges();
32                 //stopwatch3.Stop();
33                 //str.AppendLine($"DbContext修改数据耗时：" + stopwatch3.ElapsedMilliseconds.ToString());
34 
35 
36                 //方法3：使用EntityFrameworkCore.Extensions
37                 _logger.LogInformation(str.ToString());
38                 return Ok(str.ToString());
39             }
40             catch (Exception ex)
41             {
42                 return Ok(ex.Message);
43             }
44         }

　　2.FreeSqlController中添加Action

 1         /// 

 2         /// 修改学生名
 3         /// 
 4         /// 
 5         /// 
 6         [HttpPost("UpdateStudent")]
 7         public IActionResult UpdateStudent([FromBody] UpdateStudent student)
 8         {
 9             try
10             {
11                 StringBuilder str = new StringBuilder();
12                 Stopwatch stopwatch3 = new Stopwatch();
13                 stopwatch3.Start();
14                 _freeSqlContext.Students
15                     .Where(a => a.Id == student.Id)
16                     .ToUpdate()
17                     .Set(a => a.Name, student.Name)
18                     .Set(a => a.Age, student.Age)
19                     .Set(a => a.Sex, student.Sex)
20                     .ExecuteAffrows();
21                 _freeSqlContext.SaveChanges();
22                 stopwatch3.Stop();
23                 str.Append($"DbContext修改数据耗时：" + stopwatch3.ElapsedMilliseconds.ToString());
24 
25                 {
26                     //Stopwatch stopwatch1 = new Stopwatch();
27                     //stopwatch1.Start();
28                     //_freeSql.Update()
29                     //    .Set(a => a.Name, student.Name)
30                     //    .Set(a => a.Sex, student.Sex)
31                     //    .Set(a => a.Age, student.Age)
32                     //    .Where(a => a.Id == student.Id)
33                     //    .ExecuteAffrows();
34                     //stopwatch1.Stop();
35                     //str.AppendLine($"IFreeSql 方法1 修改数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());
36 
37                     //Stopwatch stopwatch4 = new Stopwatch();
38                     //stopwatch4.Start();
39                     //var repo = _freeSql.GetRepository();
40                     //var item = new Student { Id = student.Id };
41                     //repo.Attach(item); //此时快照 item
42                     //item.Name = student.Name;
43                     //item.Sex = student.Sex;
44                     //item.Age = student.Age;
45                     //repo.Update(item); //对比快照时的变化
46                     //stopwatch4.Stop();
47                     //str.AppendLine($"Repository修改数据耗时：" + stopwatch4.ElapsedMilliseconds.ToString());
48                 }
49 
50                 _logger.LogInformation(str.ToString());
51                 return Ok(str.ToString());
52             }
53             catch (Exception ex)
54             {
55                 return Ok(ex.Message);
56             }
57         }

　　3.SqlSugerController 中添加Action

 1         /// 

 2         /// 修改学生名
 3         /// 
 4         /// 
 5         /// 
 6         [HttpPost("UpdateStudent")]
 7         public IActionResult UpdateStudent([FromBody] UpdateStudent student)
 8         {
 9             try
10             {
11                 StringBuilder str = new StringBuilder();
12                 Stopwatch stopwatch1 = new Stopwatch();
13                 stopwatch1.Start();
14                 _studentService.Update(t => new Student
15                 {
16                     Name = student.Name,
17                     Age = student.Age,
18                     Sex = student.Sex
19                 }, t => t.Id == student.Id);
20                 stopwatch1.Stop();
21                 str.Append($"方法1 修改数据耗时：" + stopwatch1.ElapsedMilliseconds.ToString());
22 
23                 {
24                     //Stopwatch stopwatch2 = new Stopwatch();
25                     //stopwatch2.Start();
26                     //_studentService.AsSugarClient().Updateable()
27                     //    .SetColumns(t => t.Name == student.Name)
28                     //    .SetColumns(t => t.Age == student.Age)
29                     //    .SetColumns(t => t.Sex == student.Sex)
30                     //    .Where(a => a.Id == student.Id)
31                     //    .ExecuteCommand();
32                     //stopwatch2.Stop();
33                     //str.AppendLine($"方法2 修改数据耗时：" + stopwatch2.ElapsedMilliseconds.ToString());
34                 }
35 
36                 _logger.LogInformation(str.ToString());
37                 return Ok(str.ToString());
38             }
39             catch (Exception ex)
40             {
41                 return Ok(ex.Message);
42             }
43         }

 　　4.运行结果对比：

 五：总结

　　还是那句话：EF Core 最复杂学习成本高，同时Code First功能也是最强的，SqlSuger最轻量，也能有不错的性能，且容易上手

　　最后我决定选择了使用FreeSql，算是折中方案吧。

 源码下载：https://download.csdn.net/download/u012647470/13752435