EntityFrameworkCore + MySQL 读写分离 （code first）

创建 Repository.MySql 项目 、Data.Model、core mvc 项目 

在Data.Model项目创建管理员表 关连 角色表

public class Manager
    {
        /// <summary>
        /// 主键
        /// </summary>
        public int ManagerId { get; set; }

        /// <summary>
        /// 
        /// </summary>
        [MaxLength(100)]
        public string? LoginName { get; set; }
        /// <summary>
        /// 昵称
        /// </summary>
        [MaxLength(100)]
        public string? NikeName { get; set; }
        /// <summary>
        /// 电话
        /// </summary>
        [MaxLength(50)]
        public string? Phone { get; set; }

        /// <summary>
        /// 密码
        /// </summary>
        [MaxLength(50)]
        public string? PassWord { get; set; }

        /// <summary>
        /// 角色 
        /// </summary>
        public virtual Role? Role { get; set; }

        /// <summary>
        /// 头像
        /// </summary>
        [MaxLength(255)]
        public string? Logo { get; set; }

        /// <summary>
        /// 最后登录时间
        /// </summary>
        public DateTime? LastLogin { get; set; }

        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime? CreateTime { get; set; }

        /// <summary>
        /// 是否启用
        /// </summary>
        public bool IsUse { get; set; }
    }

 

   public class Role
    {
        /// <summary>
        /// 
        /// </summary>
        public int RoleId { get; set; }
        /// <summary>
        /// 名称
        /// </summary>
        [MaxLength(100)]
        public string? RoleName { get; set; }

        /// <summary>
        /// 权限
        /// </summary>
        [Column(TypeName = "TEXT")]
        [MaxLength(1000)]
        public string? Permission { get; set; }

        /// <summary>
        /// 创建时间
        /// </summary>
        public DateTime? CreateTime { get; set; }
    }

在Repository.MySql 项目中引用 Data.Model

并添加ef等引用 

Pomelo.EntityFrameworkCore.MySql

Microsoft.EntityFrameworkCore

Microsoft.EntityFrameworkCore.Tools

Microsoft.VisualStudio.Web.CodeGeneration.Design

创建 DbContextFactory 

 public class DbContextFactory 
    {
        static Random r;
        private static string[]? _readConn ;
        static int dbcount ;

        /// <summary>
        /// 获取DbContext的Options
        /// </summary>
        /// <param name="writeRead"></param>
        /// <returns></returns>
        public static DbContextOptions<DiabloMySqlDbContext> GetOptions(WriteAndRead writeRead)
        {
            r = new Random();
            _readConn = ConfigManager.ReadAppSettings("ConnectionString", "ReadStr").Split("|");
            //这里获取的读库 没做心跳检测 与无效链接 剔除等操作 
            dbcount = int.Parse(ConfigManager.ReadAppSettings("DbCount"));
            string masterConnectionString = ConfigManager.ReadAppSettings("ConnectionString", "WriteConnStr"); 

            //随机选择读数据库节点
            var optionsBuilder = new DbContextOptionsBuilder<DiabloMySqlDbContext>();
            if (writeRead == WriteAndRead.Read)
            {
                int i = r.Next(1, dbcount);
                var slaveConnectionString = GetReadConn();
                optionsBuilder.UseMySql(slaveConnectionString, new MySqlServerVersion(new Version()));
            }
            else
            {
                optionsBuilder.UseMySql(masterConnectionString, new MySqlServerVersion(new Version()));
            }
            return optionsBuilder.Options;
        }
        /// <summary>
        /// 创建ReadDbContext实例
        /// </summary>
        /// <returns></returns>
        public static DiabloMySqlDbContext CreateReadDbContext()
        {
            //先从线程获取实例，保证线程安全
            DiabloMySqlDbContext dbContext = (DiabloMySqlDbContext)CallContext.GetData("ReadDbContext");
            if (dbContext == null)
            {
                if (dbcount == 1)//如果数据库数量为1，则不启用读写分离
                {
                    dbContext = new DiabloMySqlDbContext(WriteAndRead.Write);
                }
                else
                {
                    dbContext = new DiabloMySqlDbContext(WriteAndRead.Read);
                }
                CallContext.SetData("ReadDbContext", dbContext);
            }
            return dbContext;
        }
        /// <summary>
        /// 创建WriteDbContext实例
        /// </summary>
        /// <returns></returns>
        public static DiabloMySqlDbContext CreateWriteDbContext()
        {
            //先从线程获取实例，保证线程安全
            DiabloMySqlDbContext dbContext = (DiabloMySqlDbContext)CallContext.GetData("WriteDbContext");
            if (dbContext == null)
            {
                dbContext = new DiabloMySqlDbContext(WriteAndRead.Write);
                CallContext.SetData("WriteDbContext", dbContext);
            }
            return dbContext;
        }
        /// <summary>
        /// 从线程获取实例
        /// </summary>
        public class CallContext
        {
            static ConcurrentDictionary<string, AsyncLocal<object>> state = new ConcurrentDictionary<string, AsyncLocal<object>>();

            public static void SetData(string name, object data) =>
                state.GetOrAdd(name, _ => new AsyncLocal<object>()).Value = data;

            public static object GetData(string name) =>
                state.TryGetValue(name, out AsyncLocal<object> data) ? data.Value : null;
        }

        /// <summary>
        /// 获取读库链接字符串
        /// </summary>
        /// <returns>读库链接字符串</returns>
        private static string GetReadConn()
        {
            // 读库策略
            int index = new Random().Next(0, _readConn.Length - 1);
            return _readConn[index];
        }

读写枚举

    /// <summary>
    /// 读库/写库
    /// </summary>
    public enum WriteAndRead
    {
        Write,
        Read
    }

创建数据库上下文

    /// <summary>
    /// 数据上下文
    /// </summary>
    public partial class DiabloMySqlDbContext : DbContext
    {
        private static WriteAndRead _writeRead = WriteAndRead.Write;
        public DiabloMySqlDbContext() : base()
        {
        }

        public DiabloMySqlDbContext(WriteAndRead writeRead) : base(DbContextFactory.GetOptions(writeRead))
        {
            _writeRead = writeRead;
        }

        public DbSet<Manager> Managers { get; set; }
        public DbSet<Role> Roles { get; set; }
     
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseMySql(ConfigManager.ReadAppSettings("ConnectionString", "WriteConnStr"),
                new MySqlServerVersion(new Version()), b => { b.MigrationsAssembly("DiabloCore.Repository.MySql"); });
            }

        }

    }

core mvc ：

Program 添加：

#region EFCore6 整合ASP.NET Core6.0
builder.Services.AddScoped<DbContext, DiabloMySqlDbContext>();
#endregion

配置文件appsettings.json：

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionString": {
    "WriteConnStr": "Server=localhost;port=3306;database=test_db;uid=root;pwd=4792!@Abc11436",
    "ReadStr": "Server=localhost;port=3306;database=test_db;uid=root;pwd=4792!@Abc11436|Server=localhost;port=3307;database=test_db;uid=root;pwd=4792!@Abc11436",
  },
  "DbCount": 2 //从库数量
}

 都配置完了别忘了运行  migration 创建库与表 

具体使用的时候在service层：

 public Manager Get(int id)
        {
            using var appDbContext = new DiabloMySqlDbContext(WriteAndRead.Read);
            return appDbContext.Managers.Include(x => x.Role).FirstOrDefault(x => x.ManagerId == id);
        }

        public bool Update(Manager m)
        {
            using (var appDbContext = new DiabloMySqlDbContext(WriteAndRead.Write))
            {
                var old = appDbContext.Managers.Find(m.ManagerId);
                if (old == null)
                    return false;

                old.Phone = m.Phone;
                old.NikeName = m.NikeName;
                old.PassWord = m.PassWord;
                old.LoginName = m.LoginName;
                appDbContext.Entry(old).State = EntityState.Modified;
                return appDbContext.SaveChanges() > 0;
            }
        }

简单例子。