.Net 常用ORM框架对比:EF Core、FreeSql、SqlSuger (上篇)
前言:
最近由于工作需要,需要选用一种ORM框架,也因此对EF Core、FreeSql、SqlSuger作简单对比。个人认为各有有优势,存在即合理,不然早就被淘汰了是吧,所以如何选择因人而议、因项目而议,下面开始正题。
本篇文章不讲解基础知识,如有需要可移步到相应官网:EF Core官方文档:https://docs.microsoft.com/zh-cn/ef/,FreeSql官方文档:http://freesql.net/guide.html,SqlSuger官方文档:http://www.codeisbug.com/Home/Doc
环境说明:项目环境ASP .Net Core Web Api,目标框架:.Net 5,依赖包:
![](https://img2020.cnblogs.com/blog/1764015/202012/1764015-20201220132923722-643263118.png)
一:准备数据实体类
1 /// <summary> 2 /// 班级 3 /// </summary> 4 public class ClassGrade 5 { 6 [FreeSql.DataAnnotations.Column(IsIdentity = true, IsPrimary = true)]//FreeSql 7 [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]//Sugar 8 [Key,DatabaseGenerated(DatabaseGeneratedOption.Identity)] //Ef设置自增(int类型默认自增) 9 public int Id { get; set; } 10 public string Name { get; set; } 11 [SugarColumn(IsIgnore = true)] 12 public virtual ICollection<Student> Students { get; set; } 13 [SugarColumn(IsIgnore = true)] 14 public virtual ICollection<MiddleClassCourse> Classs { get; set; }// 15 } 16 /// <summary> 17 /// 课程 18 /// </summary> 19 public class Course 20 { 21 [FreeSql.DataAnnotations.Column(IsIdentity = true, IsPrimary = true)]//FreeSql 22 [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]//Sugar 23 [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)] //Ef设置自增(int类型默认自增) 24 public int Id { get; set; } 25 public string Name { get; set; } 26 public virtual string Teacher { get; set; } 27 [SugarColumn(IsIgnore = true)] 28 public virtual ICollection<MiddleClassCourse> ClassStudents { get; set; }//班级学生 29 [SugarColumn(IsIgnore = true)] 30 public virtual ICollection<MiddleStudentCourse> Students { get; set; }//选修学生 31 } 32 /// <summary> 33 /// 学生 34 /// </summary> 35 public class Student 36 { 37 [FreeSql.DataAnnotations.Column(IsIdentity = true, IsPrimary = true)]//FreeSql 38 [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]//Sugar 39 [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)] //Ef设置自增(int类型默认自增) 40 public int Id { get; set; } 41 public string Name { get; set; } 42 public int Age { get; set; } 43 public int Sex { get; set; } 44 public int ClassId { get; set; } 45 [SugarColumn(IsIgnore = true)] 46 public virtual ClassGrade Class { get; set; } 47 [SugarColumn(IsIgnore = true)] 48 public virtual ICollection<MiddleStudentCourse> Courses { get; set; }//辅修课、自选课 49 } 50 { 51 /// <summary> 52 /// 中间表(班级-课程) 53 /// </summary> 54 public class MiddleClassCourse 55 { 56 [FreeSql.DataAnnotations.Column(IsIdentity = true, IsPrimary = true)]//FreeSql 57 [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]//Sugar 58 [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)] //Ef设置自增(int类型默认自增) 59 public int Id { get; set; } 60 public int ClassId { get; set; } 61 [SugarColumn(IsIgnore = true)] 62 public virtual ClassGrade Class { get; set; } 63 public int CourseId { get; set; } 64 [SugarColumn(IsIgnore = true)] 65 public virtual Course Course { get; set; } 66 } 67 /// <summary> 68 /// 中间表(学生-课程) 69 /// </summary> 70 public class MiddleStudentCourse 71 { 72 [FreeSql.DataAnnotations.Column(IsIdentity = true, IsPrimary = true)]//FreeSql 73 [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]//Sugar 74 [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)] //Ef设置自增(int类型默认自增) 75 public int Id { get; set; } 76 public int CourseId { get; set; } 77 [SugarColumn(IsIgnore = true)] 78 public virtual Course Course { get; set; } 79 public int StudentId { get; set; } 80 [SugarColumn(IsIgnore = true)] 81 public virtual Student Student { get; set; } 82 }
二:Code First
1. EF的流程相对比较复杂,但是功能也更强大,具体流程我在这里就不仔细叙述了,下面是EF的DbContext类
public class EfDbContext : DbContext { /// <summary> /// 指定静态ILoggerFactory /// </summary> public static readonly ILoggerFactory MyLoggerFactory = LoggerFactory.Create(builder => { builder.AddConsole(); }); public EfDbContext() { } public EfDbContext(DbContextOptions<EfDbContext> options) : base(options) { } private string Conn = null; public DbContext ToWriteOrRead(string conn) { Conn = conn; return this; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { if (!optionsBuilder.IsConfigured) { optionsBuilder.UseLoggerFactory(MyLoggerFactory) //.UseLazyLoadingProxies() .UseSqlServer(Conn); } optionsBuilder.UseLoggerFactory(MyLoggerFactory); } protected override void OnModelCreating(ModelBuilder modelBuilder) { #region MyRegion { //指定主键 //modelBuilder.Entity<ClassGrade>().HasKey(a => a.Id); /////设置数据库架构 //modelBuilder.HasDefaultSchema("xl"); /////表名、属性名映射 //modelBuilder.Entity<UserInfo>().ToTable("UserInfos", "Zhaoxi").Property(p => p.UserAge).HasColumnName("Age"); ////设置联合主键 //modelBuilder.Entity<SysUserRoleMapping>().HasKey(p => new { p.SysUserId, p.SysRoleId }); ////初始化数据 //modelBuilder.Entity<Company>().HasData(new List<Company>() //{ //}); ///////表拆分:在数据库中是一整张表,在代码层面是多个实体与其对应; //modelBuilder.Entity<SysLog>(dob => //{ // dob.ToTable("SysLogInfo"); // dob.Property(o => o.LogType).HasColumnName("LogType");//配置两个实体的相同属性映射到表的同一列 // dob.HasOne(o => o.SysLogDetail).WithOne().HasForeignKey<SysLog>(o => o.Id); ; //配置两个实体的相同属性映射到表的同一列 //}); //modelBuilder.Entity<SysLogDetail>(dob => //{ // dob.ToTable("SysLogInfo"); // dob.Property(o => o.LogType).HasColumnName("LogType");//配置两个实体的相同属性映射到表的同一列 //}); } //设置一对多的关系 modelBuilder.Entity<Student>().HasOne(c => c.Class).WithMany(s => s.Students).HasForeignKey(b => b.ClassId); ////多对多关系 modelBuilder.Entity<MiddleStudentCourse>(eb => { eb.HasOne(p => p.Course).WithMany(u => u.Students).HasForeignKey(u => u.CourseId); eb.HasOne(p => p.Student).WithMany(r => r.Courses).HasForeignKey(s => s.StudentId); }); modelBuilder.Entity<MiddleClassCourse>(eb => { eb.HasOne(p => p.Course).WithMany(u => u.ClassStudents).HasForeignKey(u => u.CourseId); eb.HasOne(p => p.Class).WithMany(r => r.Classs).HasForeignKey(s => s.ClassId); }); #endregion } public DbSet<ClassGrade> Classs { get; set; } public DbSet<Student> Students { get; set; } public DbSet<Course> Courses { get; set; } }
2.FreeSql的流程相对EF就简单许多了,不需要执行“Add-Migration”、“Update-Database”命令,运行时检查没有表自动创建,下面是FreeSql的DbContext类,与EF很相似。
public class FreeSqlContext: DbContext { public DbSet<Student> Students { get; set; } public DbSet<Course> Courses { get; set; } public DbSet<ClassGrade> ClassGrades { get; set; } public DbSet<MiddleClassCourse> MiddleClassCourses { get; set; } public DbSet<MiddleStudentCourse> MiddleStudentCourses { get; set; } //每个 DbContext 只触发一次 protected override void OnModelCreating(ICodeFirst codefirst) { codefirst.Entity<Student>(eb => { eb.HasOne(a => a.Class).HasForeignKey(b => b.ClassId).WithMany(c => c.Students); }); codefirst.Entity<MiddleStudentCourse>(eb => { eb.HasOne(a => a.Student).WithMany(t => t.Courses).HasForeignKey(b => b.StudentId); eb.HasOne(a => a.Course).WithMany(t => t.Students).HasForeignKey(a => a.CourseId); }); codefirst.Entity<MiddleClassCourse>(eb => { eb.HasOne(a => a.Course).WithMany(t => t.ClassStudents).HasForeignKey(a => a.CourseId); eb.HasOne(a => a.Class).WithMany(t => t.Students).HasForeignKey(a => a.ClassId); }); } }
3.SqlSuger就更简单了,不需要配置DbContext,配置如下泛型类就可以了,T为实体类
public class SqlSugerContext<T>: SimpleClient<T> where T : class, new() { public SqlSugerContext(SqlSugarClient context) : base(context) { context.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(T));//这样一个表就能成功创建了 } } public class ClassGradeService: SqlSugerContext<ClassGrade> { public ClassGradeService(SqlSugarClient context):base(context) { } } public class CourseService: SqlSugerContext<Course> { public CourseService(SqlSugarClient context) : base(context) { } } public class StudentService: SqlSugerContext<Student> { public StudentService(SqlSugarClient context) : base(context) { } } public class MiddleClassCourseCervice : SqlSugerContext<MiddleClassCourse> { public MiddleClassCourseCervice(SqlSugarClient context) : base(context) { } } public class MiddleStudentCourseService : SqlSugerContext<MiddleStudentCourse> { public MiddleStudentCourseService(SqlSugarClient context) : base(context) { } }
三:配置声明
1.连接字符串(都实现了读写分离,由于只是测试,数据库主从都是同一个库,实际上不能这样写,不然没有读写分离的意义):
"EfConnectionStrings": { "WriteConnection": "Server=localhost;Database=DbEfCore;Trusted_Connection=True;", "ReadConnectionList": [ "Server=localhost;Database=DbEfCore;Trusted_Connection=True;" ] }, "FreeSqlConnectionStrings": "Server=localhost;Database=DbFreeSql;Trusted_Connection=True;", "SqlSugerConnectionStrings": "Server=localhost;Database=DbSqlSuger;Trusted_Connection=True;"
2.EF实现读写分离需要自行封装,另外两个只需要配置好连接字符就好了,下面是EF数据库读写分离的实现:
public enum WriteAndReadEnum { Write, //主库操作 Read //从库操作 }
public interface IDbContextFactory { public EfDbContext ConnWriteOrRead(WriteAndReadEnum writeAndRead); }
public class DBConnectionOption { public string WriteConnection { get; set; } public List<string> ReadConnectionList { get; set; } }
public class DbContextFactory : IDbContextFactory { private readonly EfDbContext _Context = new EfDbContext(); private static int _iSeed = 0; private readonly DBConnectionOption _readAndWrite = null; public DbContextFactory(IOptionsMonitor<DBConnectionOption> options) { _readAndWrite = options.CurrentValue; } public EfDbContext ConnWriteOrRead(WriteAndReadEnum writeAndRead) { //判断枚举,不同的枚举可以创建不同的Context 或者更换Context链接; switch (writeAndRead) { case WriteAndReadEnum.Write: ToWrite(); break; //选择链接//更换_Context链接 //选择链接 case WriteAndReadEnum.Read: ToRead(); break; //选择链接//更换_Context链接 default: break; } return _Context; } /// <summary> /// 更换成主库连接 /// </summary> /// <returns></returns> private void ToWrite() { string conn = _readAndWrite.WriteConnection; _Context.ToWriteOrRead(conn); } /// <summary> /// 更换成主库连接 /// /// ///策略---数据库查询的负载均衡 /// </summary> /// <returns></returns> private void ToRead() { var conn = this._readAndWrite.ReadConnectionList[_iSeed++ % this._readAndWrite.ReadConnectionList.Count];//轮询; _Context.ToWriteOrRead(conn); } }
3.在Startup.ConfigureServices方法中注入:
#region FreeSql//DbFreeSql var freestr = Configuration.GetSection("FreeSqlConnectionStrings").Value; IFreeSql fsql = new FreeSql.FreeSqlBuilder() .UseConnectionString(FreeSql.DataType.SqlServer, freestr) .UseSlave(freestr)//使用从数据库,支持多个 .UseAutoSyncStructure(true) //自动同步实体结构到数据库 .Build(); //请务必定义成 Singleton 单例模式 services.AddSingleton<IFreeSql>(fsql); services.AddFreeDbContext<FreeSqlContext>(options => options.UseFreeSql(fsql)); #endregion #region SqlSuger//DbSqlSuger var sugerstr = Configuration.GetSection("SqlSugerConnectionStrings").Value; services.AddScoped(options => new SqlSugarClient(new ConnectionConfig() { ConnectionString = sugerstr,//连接符字串 DbType = DbType.SqlServer, IsAutoCloseConnection = true, InitKeyType = InitKeyType.Attribute,//从特性读取主键自增信息 SlaveConnectionConfigs = new List<SlaveConnectionConfig>() {//使用从数据库,支持多个 new SlaveConnectionConfig() { HitRate=10, ConnectionString=sugerstr } } })); services.AddScoped<ClassGradeService>(); services.AddScoped<CourseService>(); services.AddScoped<StudentService>(); services.AddScoped<MiddleStudentCourseService>(); services.AddScoped<MiddleClassCourseCervice>(); #endregion #region EfCore//DbEfCore services.AddDbContext<EfDbContext>(options => options.UseSqlServer("name=EfConnectionStrings:WriteConnection")); services.Configure<DBConnectionOption>(Configuration.GetSection("EfConnectionStrings"));//注入多个链接 services.AddTransient<IDbContextFactory, DbContextFactory>(); #endregion
四:计时中间件
1.添加一个中间件,用于统计请求Api开始进入管道到结束管道全程耗时
1 public class CalculateExecutionTime 2 { 3 private readonly RequestDelegate _next;//下一个中间件 4 private readonly ILogger _logger; 5 Stopwatch stopwatch; 6 public CalculateExecutionTime(RequestDelegate next, ILoggerFactory loggerFactory) 7 { 8 if (loggerFactory == null) 9 { 10 throw new ArgumentNullException(nameof(loggerFactory)); 11 } 12 this._next = next ?? throw new ArgumentNullException(nameof(next)); 13 _logger = loggerFactory.CreateLogger<CalculateExecutionTime>(); 14 } 15 16 public async Task Invoke(HttpContext context) 17 { 18 stopwatch = new Stopwatch(); 19 _logger.LogInformation($@"====接口{context.Request.Path}开始计时===="); 20 stopwatch.Start();//启动计时器 21 await _next.Invoke(context); 22 stopwatch.Stop();//停止秒表。 23 _logger.LogInformation($@"====接口{context.Request.Path}耗时:{stopwatch.ElapsedMilliseconds}ms===="); 24 } 25 } 26 27 public static IApplicationBuilder UseCalculateExecutionTime(this IApplicationBuilder app) 28 { 29 if (app == null) 30 { 31 throw new ArgumentNullException(nameof(app)); 32 } 33 return app.UseMiddleware<CalculateExecutionTime>(); 34 }
2.使用中间件,Configure中加入如下代码
1 app.UseCalculateExecutionTime();//Api计时
五:总结
到此基本框架就搭建好了,下一篇将分别实现相同功能的三套API进行具体比较。
就目前来说,EF Core 最复杂学习成本高,同时Code First功能也是最强的,SqlSuger最简单容易上手,但是没有严格意义上的Code First,只是能够创建表而已。