.Net常用ORM框架对比：EFCore、FreeSql、SqlSuger [数据库教程]

• Z时代
• 2024-01-10
• 分类：综合

　　前言：

　　最近由于工作需要，需要选用一种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>
 4publicclass 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类型默认自增)
 9publicint Id { get; set; }
10publicstring Name { get; set; }
11         [SugarColumn(IsIgnore = true)]
12publicvirtual ICollection<Student> Students { get; set; }
13         [SugarColumn(IsIgnore = true)]
14publicvirtual ICollection<MiddleClassCourse> Classs { get; set; }//
15    }
16///<summary>
17/// 课程
18///</summary>
19publicclass 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类型默认自增)
24publicint Id { get; set; }
25publicstring Name { get; set; }
26publicvirtualstring Teacher { get; set; }
27         [SugarColumn(IsIgnore = true)]
28publicvirtual ICollection<MiddleClassCourse> ClassStudents { get; set; }//班级学生
29         [SugarColumn(IsIgnore = true)]
30publicvirtual ICollection<MiddleStudentCourse> Students { get; set; }//选修学生
31    }
32///<summary>
33/// 学生
34///</summary>
35publicclass 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类型默认自增)
40publicint Id { get; set; }
41publicstring Name { get; set; }
42publicint Age { get; set; }
43publicint Sex { get; set; }
44publicint ClassId { get; set; }
45         [SugarColumn(IsIgnore = true)]
46publicvirtual ClassGrade Class { get; set; }
47         [SugarColumn(IsIgnore = true)]
48publicvirtual ICollection<MiddleStudentCourse> Courses { get; set; }//辅修课、自选课
49    }
50{
51///<summary>
52/// 中间表（班级-课程）
53///</summary>
54publicclass 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类型默认自增)
59publicint Id { get; set; }
60publicint ClassId { get; set; }
61         [SugarColumn(IsIgnore = true)]
62publicvirtual ClassGrade Class { get; set; }
63publicint CourseId { get; set; }
64         [SugarColumn(IsIgnore = true)]
65publicvirtual Course Course { get; set; }
66    }
67///<summary>
68/// 中间表（学生-课程）
69///</summary>
70publicclass 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类型默认自增)
75publicint Id { get; set; }
76publicint CourseId { get; set; }
77         [SugarColumn(IsIgnore = true)]
78publicvirtual Course Course { get; set; }
79publicint StudentId { get; set; }
80         [SugarColumn(IsIgnore = true)]
81publicvirtual Student Student { get; set; }
82     }

二:Code First

1. EF的流程相对比较复杂，但是功能也更强大，具体流程我在这里就不仔细叙述了，下面是EF的DbContext类

publicclass EfDbContext : DbContext
    {
///<summary>
/// 指定静态ILoggerFactory
///</summary>
publicstaticreadonly ILoggerFactory MyLoggerFactory = LoggerFactory.Create(builder => { builder.AddConsole(); });
public EfDbContext() { }
public EfDbContext(DbContextOptions<EfDbContext> options)
            : base(options)
        {
        }
privatestring Conn = null;
public DbContext ToWriteOrRead(string conn)
        {
            Conn = conn;
returnthis;
        }
protectedoverridevoid OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseLoggerFactory(MyLoggerFactory)
//.UseLazyLoadingProxies()
                     .UseSqlServer(Conn);
            }
            optionsBuilder.UseLoggerFactory(MyLoggerFactory);
        }
protectedoverridevoid 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很相似。

publicclass 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 只触发一次
protectedoverridevoid 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为实体类

publicclass SqlSugerContext<T>: SimpleClient<T> where T : class, new()
    {
public SqlSugerContext(SqlSugarClient context) : base(context)//注意这里要有默认值等于null
        {
            context.CodeFirst.SetStringDefaultLength(200).InitTables(typeof(T));//这样一个表就能成功创建了
        }
    }
publicclass ClassGradeService: SqlSugerContext<ClassGrade>
    {
public ClassGradeService(SqlSugarClient context):base(context)
        {
        }
    }
publicclass CourseService: SqlSugerContext<Course>
    {
public CourseService(SqlSugarClient context) : base(context)
        {
        }
    }
　　publicclass StudentService: SqlSugerContext<Student>
    {
public StudentService(SqlSugarClient context) : base(context)
        {
        }
    }
　　publicclass MiddleClassCourseCervice : SqlSugerContext<MiddleClassCourse>
    {
public MiddleClassCourseCervice(SqlSugarClient context) : base(context)
        {
        }
    }
　　publicclass 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数据库读写分离的实现：

publicenum 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，只是能够创建表而已。

.Net 常用ORM框架对比：EF Core、FreeSql、SqlSuger

以上是 .Net常用ORM框架对比：EFCore、FreeSql、SqlSuger [数据库教程] 的全部内容， 来源链接： utcz.com/z/535297.html