.Net 常用ORM框架对比:EF Core、FreeSql、SqlSuger (下篇)
前言:
本篇文章接上篇继续讲解:https://www.cnblogs.com/xl-tf/p/14163360.html
接下来就是具体的业务逻辑实现了;
一:在Api中如何使用
1. 添加EfCoreController 空Api控制器,用于实现EF Core的代码,编写如下代码
1 [Route("api/[controller]")] 2 [ApiController] 3 public class EfCoreController : ControllerBase 4 { 5 private readonly ILogger<EfCoreController> _logger; 6 private readonly IDbContextFactory _efDbContext; 7 public EfCoreController(ILogger<EfCoreController> 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<FreeSqlController> _logger; 8 public FreeSqlController(FreeSqlContext freeSqlContext, IFreeSql freeSql, ILogger<FreeSqlController> 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<SqlSugerController> _logger; 11 public SqlSugerController(ILogger<SqlSugerController> 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 /// <summary> 2 /// 添加班级 3 /// </summary> 4 /// <param name="name"></param> 5 /// <returns></returns> 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 /// <summary> 16 /// 添加课程和老师 17 /// </summary> 18 /// <param name="name"></param> 19 /// <returns></returns> 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 /// <summary> 30 /// 给班级添加课程 31 /// </summary> 32 /// <param name="classAddCourse"></param> 33 /// <returns></returns> 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 /// <summary> 54 /// 插入学生 55 /// </summary> 56 /// <param name="student"></param> 57 /// <returns></returns> 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<Student> Students11 = new List<Student>(); 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 /// <summary> 92 /// 插入学生-带选修课程 93 /// </summary> 94 /// <param name="course"></param> 95 /// <returns></returns> 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<MiddleStudentCourse> { 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 /// <summary> 2 /// 添加班级 3 /// </summary> 4 /// <param name="name"></param> 5 /// <returns></returns> 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 /// <summary> 14 /// 添加课程和老师 15 /// </summary> 16 /// <param name="name"></param> 17 /// <returns></returns> 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 /// <summary> 26 /// 给班级添加课程 27 /// </summary> 28 /// <param name="classAddCourse"></param> 29 /// <returns></returns> 30 [HttpPost("ClassAddCourse")] 31 public IActionResult ClassAddCourse([FromBody] ClassAddCourse classAddCourse) 32 { 33 var date = _freeSql.Select<ClassGrade>() 34 .IncludeMany(t => t.Classs) 35 .Where(t => t.Id == classAddCourse.ClassId).First(); 36 var Classs = new List<MiddleClassCourse>(); 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 /// <summary> 51 /// 插入学生 52 /// </summary> 53 /// <param name="student"></param> 54 /// <returns></returns> 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<Student> Students11 = new List<Student>(); 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<Student> Students = new List<Student>(); 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<Student>(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 /// <summary> 106 /// 插入学生-带选修课程 107 /// </summary> 108 /// <param name="course"></param> 109 /// <returns></returns> 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<MiddleStudentCourse> { 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<Student>(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<MiddleStudentCourse>(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 /// <summary> 2 /// 添加班级 3 /// </summary> 4 /// <param name="name"></param> 5 /// <returns></returns> 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 /// <summary> 14 /// 添加课程和老师 15 /// </summary> 16 /// <param name="name"></param> 17 /// <returns></returns> 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 /// <summary> 25 /// 给班级添加课程 26 /// </summary> 27 /// <param name="classAddCourse"></param> 28 /// <returns></returns> 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<MiddleClassCourse>(); 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 /// <summary> 49 /// 插入学生 50 /// </summary> 51 /// <param name="student"></param> 52 /// <returns></returns> 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<Student> Students = new List<Student>(); 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 /// <summary> 86 /// 插入学生-带选修课程 87 /// </summary> 88 /// <param name="course"></param> 89 /// <returns></returns> 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<MiddleStudentCourse>() { 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<MiddleStudentCourse>() { 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 }