[大数据]EFCore 读写分离

目前社区成熟的、免费开源并且还在维护的中间件有mycat、shardingsphere-proxy、proxysql、maxscale。

1、SQL Server 发布订阅
*
*
2、环境
.NET 5.0
Microsoft.EntityFrameworkCore（5.0.15）
Microsoft.EntityFrameworkCore.SqlServer（5.0.15）

3、DBConnectionOption.cs【方式1】

using System.Collections.Generic;

namespace WebReadAndWrite
{
    /// <summary>
    /// 管理数据库连接
    /// </summary>
    public class DBConnectionOption
    {
        public string WriteConnection { get; set; }

        public List<string> ReadConnectionList { get; set; }
    }
}

4、DbContextExtend.cs【方式1】

using Microsoft.EntityFrameworkCore;
using System;

namespace WebReadAndWrite
{
    public static class DbContextExtend
    {
        public static DbContext ToRead(this DbContext dbContext)
        {
            if (dbContext is CustomersDbContext)
                return ((CustomersDbContext)dbContext).ToRead();
            else
                throw new Exception();
        }

        public static DbContext ToWrite(this DbContext dbContext)
        {
            if (dbContext is CustomersDbContext)
                return ((CustomersDbContext)dbContext).ToWrite();
            else
                throw new Exception();
        }
    }
}

5、CustomersDbContext.cs【方式1】(纠结了很久，想了很长时间)

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Options;

namespace WebReadAndWrite
{
    public partial class CustomersDbContext : DbContext
    {
        private DBConnectionOption _readAndWrite = null;

        private string conn = string.Empty;

        public CustomersDbContext(IOptionsMonitor<DBConnectionOption> options)
        {
            this._readAndWrite = options.CurrentValue;
        }

        private static int _iSeed = 0;
        public DbContext ToRead()
        {
            //轮询 方式1【OK】
            //conn = this._readAndWrite.ReadConnectionList[_iSeed++ % this._readAndWrite.ReadConnectionList.Count];

            //随机
            //int num = new Random(_iSeed++).Next(0, this._readAndWrite.ReadConnectionList.Count);

            //轮询 方式2【OK】
            this.Database.GetDbConnection().ConnectionString =
                this._readAndWrite.ReadConnectionList[_iSeed++ %
                this._readAndWrite.ReadConnectionList.Count];

            return this;
        }

        public DbContext ToWrite()
        {
            //方式1【OK】
            conn = this._readAndWrite.WriteConnection;

            //方式2【OK】
            this.Database.GetDbConnection().ConnectionString =
                this._readAndWrite.WriteConnection;

            return this;
        }

        public virtual DbSet<Users> Users { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            #region 方式1【OK】
            //if (!optionsBuilder.IsConfigured)
            //{
            //    //optionsBuilder.UseSqlServer("Server=.;Database=dbHuobi;User ID=sa;Password=000000;");
            //    optionsBuilder.UseSqlServer(conn);
            //}
            #endregion

            #region 方式2【OK】
            optionsBuilder.UseSqlServer(this._readAndWrite.WriteConnection);
            #endregion

            //base.OnConfiguring(optionsBuilder);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }
    }
}

6、DbContextFactory.cs【方式2】

using Microsoft.Extensions.Configuration;

namespace WebReadAndWrite
{
    public interface IDbContextFactory
    {
        EFCoreContext CreateContext(WriteAndReadEnum writeAndRead);
    }

    public class DbContextFactory: IDbContextFactory
    {
        private IConfiguration Configuration { get; }
        private string[] ReadConnectionStrings;

        public DbContextFactory(IConfiguration configuration)
        {
            Configuration = configuration;
            ReadConnectionStrings = Configuration.GetConnectionString("EFCoreTestToRead").Split(",");
        }

        public EFCoreContext CreateContext(WriteAndReadEnum writeAndRead)
        {
            string connectionString = string.Empty;
            switch (writeAndRead)
            {
                case WriteAndReadEnum.Write:
                    connectionString = Configuration.GetConnectionString("EFCoreTestToWrite");
                    break;
                case WriteAndReadEnum.Read:
                    connectionString = GetReadConnectionString();
                    break;
                default:
                    break;
            }
            return new EFCoreContext(connectionString);
        }

        private static int _iSeed = 0;

        private string GetReadConnectionString()
        {
            /*
             * 随机策略
             * 权重策略
             * 轮询策略
             */

            //随机策略
            //string connectionString = ReadConnectionStrings[new Random().Next(0, ReadConnectionStrings.Length)];

            //轮询策略
            string connectionString = ReadConnectionStrings[_iSeed++ % ReadConnectionStrings.Length];

            return connectionString;
        }
    }
}

7、EFCoreContext.cs【方式2】

using Microsoft.EntityFrameworkCore;

namespace WebReadAndWrite
{
    public class EFCoreContext : DbContext
    {
        public EFCoreContext(string connectionString)
        {
            ConnectionString = connectionString;
        }

        private string ConnectionString { get; }

        public DbSet<Users> Users { get; set; }

        /// <summary>
        /// 配置连接数据库
        /// </summary>
        /// <param name="optionsBuilder"></param>
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            //base.OnConfiguring(optionsBuilder);
            optionsBuilder.UseSqlServer(ConnectionString);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            //base.OnModelCreating(modelBuilder); 
        }
    }
}

8、WriteAndReadEnum.cs【方式2】

namespace WebReadAndWrite
{
    public enum WriteAndReadEnum
    {
        Write,
        Read
    }
}

9、Startup.cs

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.OpenApi.Models;

namespace WebReadAndWrite
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        public void ConfigureServices(IServiceCollection services)
        {

            services.AddControllers();

            #region 读写分离
            services.AddTransient<IUserService, UserService>();
            services.AddTransient<DbContext, CustomersDbContext>();
            //注入多个链接
            services.Configure<DBConnectionOption>(Configuration.GetSection("ConnectionStrings"));
            #endregion

            #region 读写分离2
            services.AddTransient<ICompanyService, CompanyService>();
            services.AddScoped<IDbContextFactory, DbContextFactory>();
            #endregion

            services.AddSwaggerGen(c =>
            {
                c.SwaggerDoc("v1", new OpenApiInfo { Title = "WebReadAndWrite", Version = "v1" });
            });
        }

        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
                app.UseSwagger();
                app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "WebReadAndWrite v1"));
            }

            app.UseRouting();

            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
        }
    }
}

10、UserService.cs

using Microsoft.EntityFrameworkCore;
using System.Linq;

namespace WebReadAndWrite
{
    public interface IUserService
    {
        Users FindUser(int id);

        int InsertUser(Users data);
    }

    public class UserService : IUserService
    {
        private DbContext _dbContext = null;

        public UserService(DbContext dbContext)
        {
            this._dbContext = dbContext;
        }

        public Users FindUser(int id)
        {
            this._dbContext = this._dbContext.ToRead();
            Users model = this._dbContext.Set<Users>().Where(x => x.id == id).FirstOrDefault();
            return model;
        }

        public int InsertUser(Users data)
        {
            _dbContext = this._dbContext.ToWrite();
            _dbContext.Set<Users>().Add(data);
            int id = _dbContext.SaveChanges();

            string conn = this._dbContext.Database.GetDbConnection().ConnectionString;


            return id;

        }
    }
}

11、CompanyService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace WebReadAndWrite
{
    public interface ICompanyService
    {
        Users FindUser(int id);

        int InsertUser(Users data);
    }

    public class CompanyService : ICompanyService
    {
        private IDbContextFactory _dbContext = null;

        public CompanyService(IDbContextFactory dbContext)
        {
            this._dbContext = dbContext;
        }

        public Users FindUser(int id)
        {
            EFCoreContext readContext = _dbContext.CreateContext(WriteAndReadEnum.Read);
            Users model = readContext.Users.Where(x => x.id == id).FirstOrDefault();
            return model;
        }

        public int InsertUser(Users data)
        {
            EFCoreContext writeContext = _dbContext.CreateContext(WriteAndReadEnum.Write);
            writeContext.Users.Add(data);
            int id = writeContext.SaveChanges();
            return id;
        }
    }
}

12、UsersController .cs

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace WebReadAndWrite.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class UsersController : ControllerBase
    {
        private IUserService _userService;

        public UsersController(IUserService userService)
        {
            _userService = userService;
        }

        [HttpGet]
        public IActionResult Create()
        {
            Users model = new Users
            {
                real_name = "1"
            };
            _userService.InsertUser(model);

            return Ok();
        }

        [HttpGet]
        public IActionResult Get()
        {
            Users model = null;
            while (model == null)
            {
                model = _userService.FindUser(1);
            }
            return Ok(model);
        }
    }
}

13、CompanyController.cs

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace WebReadAndWrite.Controllers
{
    [Route("api/[controller]/[action]")]
    [ApiController]
    public class CompanyController : ControllerBase
    {
        private ICompanyService _companyService;

        public CompanyController(ICompanyService companyService)
        {
            _companyService = companyService;
        }

        [HttpGet]
        public IActionResult Create()
        {
            Users model = new Users
            {
                real_name = "company"
            };
            
            _companyService.InsertUser(model);

            return Ok();
        }

        [HttpGet]
        public IActionResult Get()
        {
            Users model = null;
            while (model == null)
            {
                model = _companyService.FindUser(2);
            }
            return Ok(model);
        }
    }
}

14、appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "ConnectionStrings": {
    "WriteConnection": "Server=.;Database=dbABC;User ID=sa;Password=000;",
    "ReadConnectionList": [
      "Server=.;Database=dbCopy1;User ID=sa;password=000;",
      "Server=.;Database=dbCopy2;User ID=sa;password=000;",
      "Server=.;Database=dbCopy3;User ID=sa;password=000;"
    ],
    "EFCoreTestToWrite": "Server=.;Database=dbHuobi;User ID=sa;Password=000000;",
    "EFCoreTestToRead": "Server=.;Database=dbHuobiCopy1;User ID=sa;Password=000000;,Server=.;Database=dbHuobiCopy2;User ID=sa;Password=000000;"
  },
  "AllowedHosts": "*"
}

*
*
*
*
*
*
*
*
*