业务需要 配置一主多从数据库 读写分离 orm用的ef core , 把思路和代码写下
1. 配置2个数据库上下文 ETMasterContext ETSlaveContext(把增删改功能禁用掉)
public class ETMasterContext : DbContext
{ public ETMasterContext(DbContextOptions<ETMasterContext> options) : base(options) {}
public DbSet<User> Users { get; set; } protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder);modelBuilder.ApplyConfiguration(new UserConfiguration());
} }
public class ETSlaveContext : DbContext
{ public ETSlaveContext(DbContextOptions<ETSlaveContext> options) : base(options) {}
public DbSet<User> Users { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder);modelBuilder.ApplyConfiguration(new UserConfiguration());
} public override int SaveChanges() { throw new InvalidOperationException("只读数据库,不允许写入"); } public override int SaveChanges(bool acceptAllChangesOnSuccess) { throw new InvalidOperationException("只读数据库,不允许写入"); } public override Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = default) { throw new InvalidOperationException("只读数据库,不允许写入"); } public override Task<int> SaveChangesAsync(CancellationToken cancellationToken = default) { throw new InvalidOperationException("只读数据库,不允许写入"); }}
2. 定义2个Repository EfRepository(主) EfReadOnlyRepository(只读)
public class EfRepository<T> : IRepository<T> where T : EntityBase
{ protected readonly ETMasterContext Context;public EfRepository(ETMasterContext context)
{ Context = context; //Context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking; }public IQueryable<T> Table => Context.Set<T>().AsQueryable();
public IQueryable<T> TableNoTracking => Context.Set<T>().AsNoTracking();
public int Delete(T entity)
{ try { Context.Set<T>().Remove(entity); return 1; } catch (Exception) {return 0;
} }public int DeleteWhere(Expression<Func<T, bool>> criteria)
{ try { IQueryable<T> entities = Context.Set<T>().Where(criteria); foreach (var entity in entities) { Context.Entry(entity).State = EntityState.Deleted; } return 1; } catch (Exception) { return 0; }}
public T GetById(object id)
{ return Context.Set<T>().Find(id); }public int Insert(T entity)
{ try { Context.Set<T>().Add(entity); return 1; } catch (Exception ex) { return 0; }}
public int InsertMany(IEnumerable<T> list)
{ try { Context.Set<T>().AddRange(list); return 1; } catch (Exception ex) { return 0; } }public int Update(T entity)
{ try { Context.Entry(entity).State = EntityState.Modified; return 1; } catch (Exception) { return 0; } } }
public class EfReadOnlyRepository<T> : IReadOnlyRepository<T> where T : EntityBase
{ protected readonly ETSlaveContext Context;public EfReadOnlyRepository(ETSlaveContext context)
{ Context = context; //Context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking; }public IQueryable<T> Table => Context.Set<T>().AsQueryable();
public IQueryable<T> TableNoTracking => Context.Set<T>().AsNoTracking();
public T GetById(object id)
{ return Context.Set<T>().Find(id); } }
3. 用到的接口补上
public interface IReadOnlyRepository<T> where T : EntityBase
{ IQueryable<T> Table { get; }IQueryable<T> TableNoTracking { get; }
T GetById(object id); }public interface IRepository<T> : IReadOnlyRepository<T> where T : EntityBase
{ int Insert(T entity); int InsertMany(IEnumerable<T> list); int Update(T entity); int Delete(T entity); int DeleteWhere(Expression<Func<T, bool>> criteria); }
4. Startup ConfigureServices 中配置上下文
services.AddDbContext<ETMasterContext>(options =>
options.UseMySql(GetConnectionStringByRandom("MySql_Master"))); services.AddDbContext<ETSlaveContext>(options => options.UseMySql(GetConnectionStringByRandom("MySql_Slave")));
private string GetConnectionStringByRandom(string connectionString)
{ var connstr = Configuration.GetConnectionString(connectionString); if (string.IsNullOrEmpty(connstr)) { throw new Exception("数据库配置有误"); }var conList = connstr.Trim('|').Split('|');
var rand = new Random().Next(0, conList.Length); return conList[rand]; }
5. appsetting.json 配置多个连接字符串 | 分隔
"connectionStrings": {
"MySql_Master": "server=192.168.87.169;database=poker_games;uid=root;pwd=1$=6yuan;SslMode=None;", "MySql_Slave": "server=192.168.87.169;database=poker_games;uid=root;pwd=1$=6yuan;SslMode=None;|server=192.168.87.169;database=poker_games;uid=root;pwd=1$=6yuan;SslMode=None;" }
6. 实际应用
public class ReportService : IReportService
{ private readonly IHttpContextAccessor _httpContextAccessor; private readonly IDistributedCache _distributedCache; private readonly IUnitOfWork _unitOfWork; private readonly IMapper _mapper; private readonly IReadOnlyRepository<Bet> _betRepository;public ReportService(
IHttpContextAccessor httpContextAccessor, IDistributedCache distributedCache, IUnitOfWork unitOfWork, IMapper mapper, IReadOnlyRepository<Bet> betRepository ) { _httpContextAccessor = httpContextAccessor; _distributedCache = distributedCache; _unitOfWork = unitOfWork; _mapper = mapper; _betRepository = betRepository; }}
public ReturnValue GetAgentBetReportByRound(AgentBetReportByRoundCriteriaModel reportModel)
{var betList = _betRepository.TableNoTracking.Where(p => p.pay_out_date >= beginTime && p.pay_out_date < endTime);
return new ReturnValue(betList );}
7. 大概解释下
如图 DBContext的生命周期默认是Scoped,即整个reqeust请求的生命周期以内共用了一个Context
利用这个生命周期 在每次请求时 通过配置文件获取不同的上下文实例 即实现了 多主多从 读写分离功能