freesql 架构_.Net 常用ORM框架对比：EF Core、FreeSql、SqlSuger (上篇)

前言：

最近由于工作需要，需要选用一种ORM框架，也因此对EF Core、FreeSql、SqlSuger作简单对比。个人认为各有有优势，存在即合理，不然早就被淘汰了是吧，所以如何选择因人而议、因项目而议,下面开始正题。

环境说明：项目环境ASP .Net Core Web Api，目标框架：.Net 5，依赖包：

一：准备数据实体类

1 ///

2 ///班级3 ///

4 public classClassGrade5 {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 Students { get; set; }13 [SugarColumn(IsIgnore = true)]14 public virtual ICollection Classs { get; set; }//15 }16 ///

17 ///课程18 ///

19 public classCourse20 {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 ClassStudents { get; set; }//班级学生

29 [SugarColumn(IsIgnore = true)]30 public virtual ICollection Students { get; set; }//选修学生

31 }32 ///

33 ///学生34 ///

35 public classStudent36 {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 Courses { get; set; }//辅修课、自选课

49 }50 {51 ///

52 ///中间表(班级-课程)53 ///

54 public classMiddleClassCourse55 {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 ///

68 ///中间表(学生-课程)69 ///

70 public classMiddleStudentCourse71 {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 classEfDbContext : DbContext

{///

///指定静态ILoggerFactory///

public static readonly ILoggerFactory MyLoggerFactory = LoggerFactory.Create(builder =>{ builder.AddConsole(); });publicEfDbContext() { }public EfDbContext(DbContextOptionsoptions)

:base(options)

{

}private string Conn = null;public DbContext ToWriteOrRead(stringconn)

{

Conn=conn;return this;

}protected override voidOnConfiguring(DbContextOptionsBuilder optionsBuilder)

{if (!optionsBuilder.IsConfigured)

{

optionsBuilder.UseLoggerFactory(MyLoggerFactory)//.UseLazyLoadingProxies()

.UseSqlServer(Conn);

}

optionsBuilder.UseLoggerFactory(MyLoggerFactory);

}protected override voidOnModelCreating(ModelBuilder modelBuilder)

{#region MyRegion{//指定主键//modelBuilder.Entity().HasKey(a => a.Id);

/设置数据库架构

//modelBuilder.HasDefaultSchema("xl");

/表名、属性名映射

//modelBuilder.Entity().ToTable("UserInfos", "Zhaoxi").Property(p => p.UserAge).HasColumnName("Age");

设置联合主键

//modelBuilder.Entity().HasKey(p => new { p.SysUserId, p.SysRoleId });

初始化数据

//modelBuilder.Entity().HasData(new List()//{//});

///表拆分:在数据库中是一整张表，在代码层面是多个实体与其对应；

//modelBuilder.Entity(dob =>//{//dob.ToTable("SysLogInfo");//dob.Property(o => o.LogType).HasColumnName("LogType");//配置两个实体的相同属性映射到表的同一列//dob.HasOne(o => o.SysLogDetail).WithOne().HasForeignKey(o => o.Id); ;//配置两个实体的相同属性映射到表的同一列//});//modelBuilder.Entity(dob =>//{//dob.ToTable("SysLogInfo");//dob.Property(o => o.LogType).HasColumnName("LogType");//配置两个实体的相同属性映射到表的同一列//});

}//设置一对多的关系

modelBuilder.Entity().HasOne(c => c.Class).WithMany(s => s.Students).HasForeignKey(b =>b.ClassId);多对多关系

modelBuilder.Entity(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(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 Classs { get; set; }public DbSet Students { get; set; }public DbSet Courses { get; set; }

}

2.FreeSql的流程相对EF就简单许多了，不需要执行“Add-Migration”、“Update-Database”命令，运行时检查没有表自动创建，下面是FreeSql的DbContext类，与EF很相似。

public classFreeSqlContext: DbContext

{public DbSet Students { get; set; }public DbSet Courses { get; set; }public DbSet ClassGrades { get; set; }public DbSet MiddleClassCourses { get; set; }public DbSet MiddleStudentCourses { get; set; }//每个 DbContext 只触发一次

protected override voidOnModelCreating(ICodeFirst codefirst)

{

codefirst.Entity(eb =>{

eb.HasOne(a=> a.Class).HasForeignKey(b => b.ClassId).WithMany(c =>c.Students);

});

codefirst.Entity(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(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: SimpleClient where T : class, new()

{public SqlSugerContext(SqlSugarClient context) : base(context)

{

context.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(T));//这样一个表就能成功创建了

}

}public class ClassGradeService: SqlSugerContext{public ClassGradeService(SqlSugarClient context):base(context)

{

}

}public class CourseService: SqlSugerContext{public CourseService(SqlSugarClient context) : base(context)

{

}

}public class StudentService: SqlSugerContext{public StudentService(SqlSugarClient context) : base(context)

{

}

}public class MiddleClassCourseCervice : SqlSugerContext{public MiddleClassCourseCervice(SqlSugarClient context) : base(context)

{

}

}public class MiddleStudentCourseService : SqlSugerContext{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 enumWriteAndReadEnum

{

Write,//主库操作

Read //从库操作

}

public interface IDbContextFactory

{

public EfDbContext ConnWriteOrRead(WriteAndReadEnum writeAndRead);

}

public class DBConnectionOption

{

public string WriteConnection { get; set; }

public List 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 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;

}

///

/// 更换成主库连接

///

///

private void ToWrite()

{

string conn = _readAndWrite.WriteConnection;

_Context.ToWriteOrRead(conn);

}

///

/// 更换成主库连接

///

/// ///策略---数据库查询的负载均衡

///

///

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= newFreeSql.FreeSqlBuilder()

.UseConnectionString(FreeSql.DataType.SqlServer, freestr)

.UseSlave(freestr)//使用从数据库，支持多个

.UseAutoSyncStructure(true) //自动同步实体结构到数据库

.Build(); //请务必定义成 Singleton 单例模式

services.AddSingleton(fsql);

services.AddFreeDbContext(options =>options.UseFreeSql(fsql));#endregion

#region SqlSuger//DbSqlSuger

var sugerstr = Configuration.GetSection("SqlSugerConnectionStrings").Value;

services.AddScoped(options=> new SqlSugarClient(newConnectionConfig()

{

ConnectionString= sugerstr,//连接符字串

DbType =DbType.SqlServer,

IsAutoCloseConnection= true,

InitKeyType= InitKeyType.Attribute,//从特性读取主键自增信息

SlaveConnectionConfigs = new List() {//使用从数据库，支持多个

new SlaveConnectionConfig() { HitRate=10, ConnectionString=sugerstr }

}

}));

services.AddScoped();

services.AddScoped();

services.AddScoped();

services.AddScoped();

services.AddScoped();#endregion

#region EfCore//DbEfCoreservices.AddDbContext(options => options.UseSqlServer("name=EfConnectionStrings:WriteConnection"));

services.Configure(Configuration.GetSection("EfConnectionStrings"));//注入多个链接

services.AddTransient();#endregion

四：计时中间件

1.添加一个中间件，用于统计请求Api开始进入管道到结束管道全程耗时

1 public classCalculateExecutionTime2 {3 private readonly RequestDelegate _next;//下一个中间件

4 private readonlyILogger _logger;5 Stopwatch stopwatch;6 publicCalculateExecutionTime(RequestDelegate next, ILoggerFactory loggerFactory)7 {8 if (loggerFactory == null)9 {10 throw newArgumentNullException(nameof(loggerFactory));11 }12 this._next = next ?? throw newArgumentNullException(nameof(next));13 _logger = loggerFactory.CreateLogger();14 }15

16 public asyncTask Invoke(HttpContext context)17 {18 stopwatch = newStopwatch();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(thisIApplicationBuilder app)28 {29 if (app == null)30 {31 throw newArgumentNullException(nameof(app));32 }33 return app.UseMiddleware();34 }

2.使用中间件，Configure中加入如下代码

1 app.UseCalculateExecutionTime();//Api计时

五：总结

到此基本框架就搭建好了，下一篇将分别实现相同功能的三套API进行具体比较。

就目前来说，EF Core 最复杂学习成本高，同时Code First功能也是最强的，SqlSuger最简单容易上手，但是没有严格意义上的Code First，只是能够创建表而已。