.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,依赖包:

一:准备数据实体类

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)//注意这里要有默认值等于null
{
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.在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

四:总结

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

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

博客园精华区
我还没有学会写个人说明!
下一篇

RocketMQ源码分析 broker启动,commitlog、consumequeue、indexfile、MappedFileQueue、MappedFile之间...

你也可能喜欢

评论已经被关闭。

插入图片