业务需要 配置一主多从数据库 读写分离 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
利用这个生命周期 在每次请求时 通过配置文件获取不同的上下文实例 即实现了 多主多从 读写分离功能