【EFCore7.0读写分离实现方案】

前言

实现读写分离方法很多，我这里主要是讲代码层面，数据库层面的配置各位小伙伴还请自行探索。

思路

基于EFCore的拦截器，判断是读是写，读则切换读库

具体实现

1.创建拦截器类

public class DbMasterSlaveCommandInterceptor : DbCommandInterceptor
{
    public DbMasterSlaveCommandInterceptor(IConfiguration configuration)
    {
        this.Configuration = configuration;
    }
    private IConfiguration Configuration { get; }

    private static IConfigurationSection[]? readArray = null;
    private string GetSlaveConnectionString()
    {
        readArray ??= this.Configuration.GetSection(this.Configuration.GetSection("UseSQL").Value!).GetChildren().ToArray();
        return readArray[new Random().Next(0, readArray.Length)].Value ?? throw new Exception("请配置读库");
    }
    private void UpdateToSlave(DbCommand command)
    {
        if (command.CommandText.ToLower().StartsWith("SELECT", StringComparison.InvariantCultureIgnoreCase))
        {
            bool isDistributedTran = Transaction.Current != null &&
                                     Transaction.Current.TransactionInformation.Status !=
                                     TransactionStatus.Committed;
            bool isDbTran = command.Transaction != null;
            if (!isDbTran && !isDistributedTran && command.Connection != null)
            {
                command.Connection.Close();
                command.Connection.ConnectionString = GetSlaveConnectionString();
                command.Connection.Open();

            }
        }
    }
    public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
    {
        this.UpdateToSlave(command);
        return base.ReaderExecuting(command, eventData, result);
    }
    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default)
    {
        this.UpdateToSlave(command);
        return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
    }
    public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result)
    {
        this.UpdateToSlave(command);
        return base.ScalarExecuting(command, eventData, result);
    }
    public override ValueTask<InterceptionResult<object>> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object> result, CancellationToken cancellationToken = default)
    {
        this.UpdateToSlave(command);
        return base.ScalarExecutingAsync(command, eventData, result, cancellationToken);
    }
}

核心就在UpdateToSlave方法，下面重写的方法会在增删改查时进入，判断是读的时候改变字符串

2.创建EFCore上下文时添加拦截器

public partial class BlogDBContext : DbContext
{
    public BlogDBContext(DbContextOptions options)
        : base(options)
    {
    }
}
public class EFCoreFactory : IDbContextFactory<BlogDBContext>
{
    public EFCoreFactory(IConfiguration configuration)
    {
        this.Configuration = configuration;
    }
    private IConfiguration Configuration { get; }

    public BlogDBContext CreateDbContext()
    {
        var useSql = this.Configuration.GetSection("UseSQL").Value ?? throw new Exception("请配置数据库连接");
        var dbContextOptionsBuilder = new DbContextOptionsBuilder<BlogDBContext>();
        dbContextOptionsBuilder.AddInterceptors(new DBlogCommandInterceptor());
        if (this.Configuration.GetSection("IsReadWriteSeparation").Value.ToBool())
        {
            dbContextOptionsBuilder
                .AddInterceptors(new DbMasterSlaveCommandInterceptor(this.Configuration));
        }
        dbContextOptionsBuilder = useSql switch
        {
            "MySQL" => dbContextOptionsBuilder.UseMySQL(this.Configuration.GetConnectionString("MySQL") ?? throw new Exception("请配置mysql数据库连接")),
            "SQLSever" => dbContextOptionsBuilder.UseSqlServer(this.Configuration.GetConnectionString("SQLSever") ?? throw new Exception("请配置sqlsever数据库连接")),
            "SQLite" => dbContextOptionsBuilder.UseSqlite(this.Configuration.GetConnectionString("SQLite") ?? throw new Exception("请配置sqlite数据库连接")),
            _ => dbContextOptionsBuilder.UseSqlServer(this.Configuration.GetConnectionString("Default") ?? throw new Exception("请配置数据库连接"))
        };
        return new BlogDBContext(dbContextOptionsBuilder.Options);
    }
}

因为我使用的工厂，大家更具情况自己改动，如有不懂可以评论区发言
3.依赖注入，或者new，看大家管理对象的方式

 public static IServiceCollection AddIOC(this IServiceCollection services)
      => services.AddScoped<EFCoreFactory>()
                .AddScoped(sp => sp.GetRequiredService<EFCoreFactory>().CreateDbContext());
                

4.appsettings.json配置文件

{
  "ConnectionStrings": {
    "SQLSever": "Data Source=127.0.0.1;Initial Catalog=blog;User ID=sa;password=123456;TrustServerCertificate=true",
    "MySQL": "server=127.0.0.1;userid=root;pwd=123456;port=3306;database=blog;SslMode=none;CharSet=utf8",
    "SQLite": "Data Source=blog.db;",
    "Oracle": "Provider=127.0.0.1;Data Source=blog;User Id=sa;Password=123456;",
    "PostgreSQL": "Host=127.0.0.1;Port=5432;Database=blog;Username=sa;Password=123456;",
    "Default": "Data Source=127.0.0.1;Initial Catalog=blog;User ID=sa;password=123456;TrustServerCertificate=true"
  },
  "IsReadWriteSeparation": true,
  "SQLSever": [
    "Data Source=127.0.0.1;Initial Catalog=blogRead01;User ID=sa;password=123456",
    "Data Source=127.0.0.1;Initial Catalog=blogRead02;User ID=sa;password=123456"
  ],
  "MySQL": [
    "server=127.0.0.1;userid=root;pwd=123456;port=3306;database=blogRead01;SslMode=none;CharSet=utf8",
   "server=127.0.0.1;userid=root;pwd=123456;port=3306;database=blogRead02;SslMode=none;CharSet=utf8"
  ],
  "SQLite": [
    "Data Source=blogRead01.db;",
    "Data Source=blogRead02.db;"
  ],
  "UseSQL": "SQLite"
}

结尾

没说太多原理，懂得自然懂，读写分离引发的问题也很多，功力不深的小伙伴量力而行。