我们公司2019年web开发已迁移至.NET core，目前有部分平台随着用户量增加，单一数据库部署已经无法满足我们的业务需求，一直在寻找EF CORE读写分离解决方案，目前在各大技术论坛上还没找到很好的方案，根据之前找到的读写分离方案，综合目前EF core 的能力，自己编写了一套EF core实现mysql读写分离的解决方案，目前以应用到正式生产环境(Linux)中，日活跃用户20W，木有发现明显BUG，推荐个大家使用，部分代码参考文章(https://www.cnblogs.com/qtqq/p/6942312.html)，废话不多说直接上代码：
一、读写分离，采用的是一主多从，主库进行数据写操作，从库进行数据读操作；对DbContext基类进行改造，构造函数传入读或写枚举；新建一个类SyDbContext继承DbContext基类；构造函数传入WriteAndRead枚举，用来区别是读库还是写库
1 using Microsoft.EntityFrameworkCore; 2 3 4 5 namespace Sykj.Repository 6 7 { 8 9 /// <summary> 10 11 /// 数据库上下文类 12 13 /// </summary> 14 15 public partial class SyDbContext : DbContext 16 17 { 18 19 /// <summary> 20 21 /// 构造函数 22 23 /// </summary> 24 25 /// <param name="options"></param> 26 27 public SyDbContext(WriteAndRead writeRead) : base(DbContextFactory.GetOptions(writeRead)) 28 29 { 30 31 32 33 } 34 35 36 37 /// <summary> 38 39 /// 映射配置调用 40 41 /// </summary> 42 43 /// <param name="modelBuilder"></param> 44 45 protected override void OnModelCreating(ModelBuilder modelBuilder) 46 47 { 48 49 //应用映射配置 50 51 52 53 base.OnModelCreating(modelBuilder); 54 55 } 56 57 } 58 59 }
二、编写DbContextFactory工厂类，用于创建DbContext读/写实列(注意：DbContext在一个请求周期必须保证实例是唯一，所以编写一个CallContext类，先判断当前http请求线程是否有实例，没有则new一个，保证DbContext线程安全)；masterConnectionString是主库连接实列，用于数据的写操作，slaveConnectionString是从库连接实列，用于数据的读操作，从库可以有多个，我们这里采用一主多从机制，随机分配从库策略(参数在配置文件进行设置，放在文章最后贴出代码)具体实现代码如下：
1 using Microsoft.EntityFrameworkCore; 2 using System; 3 using System.Collections.Concurrent; 4 using System.Threading; 5 using Sykj.Infrastructure; 6 using Microsoft.Extensions.Logging; 7 using Microsoft.Extensions.Logging.Console; 8 9 namespace Sykj.Repository 10 { 11 /// <summary> 12 /// DbContext工厂 13 /// </summary> 14 public class DbContextFactory 15 { 16 static Random r = new Random(); 17 static int dbcount = ConfigurationManager.Configuration["DbCount"].ToInt(); 18 19 /// <summary> 20 /// EF日志输出到Console 21 /// </summary> 22 static readonly LoggerFactory LoggerFactory = new LoggerFactory(new[] { new ConsoleLoggerProvider((_, __) => true, true) }); 23 24 /// <summary> 25 /// 获取DbContext的Options 26 /// </summary> 27 /// <param name="writeRead"></param> 28 /// <returns></returns> 29 public static DbContextOptions<SyDbContext> GetOptions(WriteAndRead writeRead) 30 { 31 string masterConnectionString = ConfigurationManager.Configuration["ConnectionStrings:0:ConnectionString"]; 32 33 //随机选择读数据库节点 34 var optionsBuilder = new DbContextOptionsBuilder<SyDbContext>(); 35 if (writeRead == WriteAndRead.Read) 36 { 37 int i = r.Next(1, dbcount); 38 string slaveConnectionString = ConfigurationManager.Configuration[string.Format("ConnectionStrings:{0}:ConnectionString_{0}", i)]; 39 optionsBuilder.UseMySql(slaveConnectionString).UseLoggerFactory(LoggerFactory); 40 } 41 else 42 { 43 optionsBuilder.UseMySql(masterConnectionString).UseLoggerFactory(LoggerFactory); 44 } 45 return optionsBuilder.Options; 46 } 47 48 /// <summary> 49 /// 创建ReadDbContext实例 50 /// </summary> 51 /// <returns></returns> 52 public static SyDbContext CreateReadDbContext() 53 { 54 //先从线程获取实例，保证线程安全 55 SyDbContext dbContext = (SyDbContext)CallContext.GetData("ReadDbContext"); 56 if (dbContext == null) 57 { 58 if (dbcount==1)//如果数据库数量为1，则不启用读写分离 59 { 60 dbContext = new SyDbContext(WriteAndRead.Write); 61 } 62 else 63 { 64 dbContext = new SyDbContext(WriteAndRead.Read); 65 } 66 CallContext.SetData("ReadDbContext", dbContext); 67 } 68 return dbContext; 69 } 70 71 /// <summary> 72 /// 创建WriteDbContext实例 73 /// </summary> 74 /// <returns></returns> 75 public static SyDbContext CreateWriteDbContext() 76 { 77 //先从线程获取实例，保证线程安全 78 SyDbContext dbContext = (SyDbContext)CallContext.GetData("WriteDbContext"); 79 if (dbContext == null) 80 { 81 dbContext = new SyDbContext(WriteAndRead.Write); 82 CallContext.SetData("WriteDbContext", dbContext); 83 } 84 return dbContext; 85 } 86 } 87 88 /// <summary> 89 /// 读库/写库 90 /// </summary> 91 public enum WriteAndRead 92 { 93 Write, 94 Read 95 } 96 97 /// <summary> 98 /// 从线程获取实例 99 /// </summary> 100 public class CallContext 101 { 102 static ConcurrentDictionary<string, AsyncLocal<object>> state = new ConcurrentDictionary<string, AsyncLocal<object>>(); 103 104 public static void SetData(string name, object data) => 105 state.GetOrAdd(name, _ => new AsyncLocal<object>()).Value = data; 106 107 public static object GetData(string name) => 108 state.TryGetValue(name, out AsyncLocal<object> data) ? data.Value : null; 109 } 110 }
1 using Microsoft.EntityFrameworkCore; 2 3 4 5 namespace Sykj.Repository 6 7 { 8 9 /// <summary> 10 11 /// 数据库上下文类 12 13 /// </summary> 14 15 public partial class SyDbContext : DbContext 16 17 { 18 19 /// <summary> 20 21 /// 构造函数 22 23 /// </summary> 24 25 /// <param name="options"></param> 26 27 public SyDbContext(WriteAndRead writeRead) : base(DbContextFactory.GetOptions(writeRead)) 28 29 { 30 31 32 33 } 34 35 36 37 /// <summary> 38 39 /// 映射配置调用 40 41 /// </summary> 42 43 /// <param name="modelBuilder"></param> 44 45 protected override void OnModelCreating(ModelBuilder modelBuilder) 46 47 { 48 49 //应用映射配置 50 51 52 53 base.OnModelCreating(modelBuilder); 54 55 } 56 57 } 58 59 }
三、改造RepositoryBase仓储基类，具体代码如下：
1 using System; 2 3 using System.Collections.Generic; 4 5 using System.Linq; 6 7 using System.Linq.Expressions; 8 9 using System.Linq.Dynamic.Core; 10 11 12 13 namespace Sykj.Repository 14 15 { 16 17 /// <summary> 18 19 /// 仓储基类 20 21 /// </summary> 22 23 /// <typeparam name="T">实体类型</typeparam> 24 25 public abstract class RepositoryBase<T> : IRepository<T> where T : class 26 27 { 28 29 //定义数据访问上下文对象 30 31 private readonly Lazy<SyDbContext> _dbMaster = new Lazy<SyDbContext>(() => DbContextFactory.CreateWriteDbContext()); 32 33 private readonly Lazy<SyDbContext> _dbSlave = new Lazy<SyDbContext>(() => DbContextFactory.CreateReadDbContext()); 34 35 36 37 /// <summary> 38 39 /// 主库,写操作 40 41 /// </summary> 42 43 protected SyDbContext DbMaster => _dbMaster.Value; 44 45 46 47 /// <summary> 48 49 /// 从库,读操作 50 51 /// </summary> 52 53 protected SyDbContext DbSlave => _dbSlave.Value; 54 55 56 57 #region 同步 58 59 60 61 /// <summary> 62 63 /// 判断记录是否存在 64 65 /// </summary> 66 67 /// <param name="predicate">lambda表达式条件</param> 68 69 /// <returns></returns> 70 71 public bool IsExist(Expression<Func<T, bool>> predicate) 72 73 { 74 75 return DbSlave.Set<T>().Any(predicate); 76 77 } 78 79 80 81 /// <summary> 82 83 /// 新增实体 84 85 /// </summary> 86 87 /// <param name="entity">实体</param> 88 89 /// <param name="autoSave">是否立即执行保存</param> 90 91 /// <returns></returns> 92 93 public bool Add(T entity, bool autoSave = true) 94 95 { 96 97 int row = 0; 98 99 DbMaster.Set<T>().Add(entity); 100 101 if (autoSave) 102 103 row = Save(); 104 105 return (row > 0); 106 107 } 108 109 110 111 /// <summary> 112 113 /// 批量添加 114 115 /// </summary> 116 117 /// <param name="entities">实体列表</param> 118 119 /// <param name="autoSave">是否立即执行保存</param> 120 121 /// <returns></returns> 122 123 public bool AddRange(IEnumerable<T> entities, bool autoSave = true) 124 125 { 126 127 int row = 0; 128 129 DbMaster.Set<T>().AddRange(entities); 130 131 if (autoSave) 132 133 row = Save(); 134 135 return (row > 0); 136 137 } 138 139 140 141 /// <summary> 142 143 /// 更新实体 144 145 /// </summary> 146 147 /// <param name="entity">实体</param> 148 149 /// <param name="autoSave">是否立即执行保存</param> 150 151 public bool Update(T entity, bool autoSave = true) 152 153 { 154 155 int row = 0; 156 157 DbMaster.Update(entity); 158 159 if (autoSave) 160 161 row = Save(); 162 163 return (row > 0); 164 165 } 166 167 168 169 /// <summary> 170 171 /// 更新实体部分属性 172 173 /// </summary> 174 175 /// <param name="entity">实体</param> 176 177 /// <param name="autoSave">是否立即执行保存</param> 178 179 /// <param name="updatedProperties">要更新的字段</param> 180 181 /// <returns></returns> 182 183 public bool Update(T entity, bool autoSave = true, params Expression<Func<T, object>>[] updatedProperties) 184 185 { 186 187 int row = 0; 188 189 //告诉EF Core开始跟踪实体的更改， 190 191 //因为调用DbContext.Attach方法后，EF Core会将实体的State值 192 193 //更改回EntityState.Unchanged， 194 195 DbMaster.Attach(entity); 196 197 if (updatedProperties.Any()) 198 199 { 200 201 foreach (var property in updatedProperties) 202 203 { 204 205 //告诉EF Core实体的属性已经更改。将属性的IsModified设置为true后， 206 207 //也会将实体的State值更改为EntityState.Modified， 208 209 //这样就保证了下面SaveChanges的时候会将实体的属性值Update到数据库中。 210 211 DbMaster.Entry(entity).Property(property).IsModified = true; 212 213 } 214 215 } 216 217 218 219 if (autoSave) 220 221 row = Save(); 222 223 return (row > 0); 224 225 } 226 227 228 229 /// <summary> 230 231 /// 更新实体部分属性,泛型方法 232 233 /// </summary> 234 235 /// <param name="entity">实体</param> 236 237 /// <param name="autoSave">是否立即执行保存</param> 238 239 /// <param name="updatedProperties">要更新的字段</param> 240 241 /// <returns></returns> 242 243 public bool Update<Entity>(Entity entity, bool autoSave = true, params Expression<Func<Entity, object>>[] updatedProperties) where Entity : class 244 245 { 246 247 int row = 0; 248 249 //告诉EF Core开始跟踪实体的更改， 250 251 //因为调用DbContext.Attach方法后，EF Core会将实体的State值 252 253 //更改回EntityState.Unchanged， 254 255 DbMaster.Attach(entity); 256 257 if (updatedProperties.Any()) 258 259 { 260 261 foreach (var property in updatedProperties) 262 263 { 264 265 //告诉EF Core实体的属性已经更改。将属性的IsModified设置为true后， 266 267 //也会将实体的State值更改为EntityState.Modified， 268 269 //这样就保证了下面SaveChanges的时候会将实体的属性值Update到数据库中。 270 271 DbMaster.Entry(entity).Property(property).IsModified = true; 272 273 } 274 275 } 276 277 278 279 if (autoSave) 280 281 row = Save(); 282 283 return (row > 0); 284 285 } 286 287 288 289 /// <summary> 290 291 /// 批量更新实体 292 293 /// </summary> 294 295 /// <param name="entities">实体列表</param> 296 297 /// <param name="autoSave">是否立即执行保存</param> 298 299 public bool UpdateRange(IEnumerable<T> entities, bool autoSave = true) 300 301 { 302 303 int row = 0; 304 305 DbMaster.UpdateRange(entities); 306 307 if (autoSave) 308 309 row = Save(); 310 311 return (row > 0); 312 313 } 314 315 316 317 /// <summary> 318 319 /// 根据lambda表达式条件获取单个实体 320 321 /// </summary> 322 323 /// <param name="predicate">lambda表达式条件</param> 324 325 /// <returns></returns> 326 327 public T GetModel(Expression<Func<T, bool>> predicate) 328 329 { 330 331 return DbSlave.Set<T>().FirstOrDefault(predicate); 332 333 } 334 335 336 337 /// <summary> 338 339 /// 删除实体 340 341 /// </summary> 342 343 /// <param name="entity">要删除的实体</param> 344 345 /// <param name="autoSave">是否立即执行保存</param> 346 347 public bool Delete(T entity, bool autoSave = true) 348 349 { 350 351 int row = 0; 352 353 DbMaster.Set<T>().Remove(entity); 354 355 if (autoSave) 356 357 row = Save(); 358 359 return (row > 0); 360 361 } 362 363 364 365 /// <summary> 366 367 /// 批量删除 368 369 /// </summary> 370 371 /// <param name="T">对象集合</param> 372 373 /// <returns></returns> 374 375 public bool Delete(IEnumerable<T> entities) 376 377 { 378 379 DbMaster.Set<T>().RemoveRange(entities); 380 381 int row = DbMaster.SaveChanges(); 382 383 return (row > 0); 384 385 } 386 387 388 389 /// <summary> 390 391 /// 批量删除 392 393 /// </summary> 394 395 /// <param name="T">对象集合</param> 396 397 /// <param name="autoSave">是否立即执行保存</param> 398 399 /// <returns></returns> 400 401 public bool Delete(IEnumerable<T> entities, bool autoSave = true) 402 403 { 404 405 int row = 0; 406 407 DbMaster.Set<T>().RemoveRange(entities); 408 409 if (autoSave) 410 411 row = Save(); 412 413 return (row > 0); 414 415 } 416 417 418 419 /// <summary> 420 421 /// 获取实体集合 422 423 /// </summary> 424 425 /// <returns></returns> 426 427 public virtual IQueryable<T> GetList() 428 429 { 430 431 return DbSlave.Set<T>().AsQueryable(); 432 433 } 434 435 436 437 /// <summary> 438 439 /// 根据lambda表达式条件获取单个实体 440 441 /// </summary> 442 443 /// <param name="predicate">lambda表达式条件</param> 444 445 /// <returns></returns> 446 447 public virtual IQueryable<T> GetList(Expression<Func<T, bool>> predicate) 448 449 { 450 451 return DbSlave.Set<T>().Where(predicate); 452 453 } 454 455 456 457 /// <summary> 458 459 /// 根据lambda表达式条件获取实体集合 460 461 /// </summary> 462 463 /// <param name="top">前几条</param> 464 465 /// <param name="predicate">查询条件</param> 466 467 /// <param name="ordering">排序</param> 468 469 /// <param name="args">条件参数</param> 470 471 /// <returns></returns> 472 473 public virtual IQueryable<T> GetList(int top, string predicate, string ordering, params object[] args) 474 475 { 476 477 var result = DbSlave.Set<T>().AsQueryable(); 478 479 480 481 if (!string.IsNullOrWhiteSpace(predicate)) 482 483 result = result.Where(predicate, args); 484 485 486 487 if (!string.IsNullOrWhiteSpace(ordering)) 488 489 result = result.OrderBy(ordering); 490 491 492 493 if (top > 0) 494 495 { 496 497 result = result.Take(top); 498 499 } 500 501 return result; 502 503 } 504 505 506 507 /// <summary> 508 509 /// 分页查询,返回实体对象 510 511 /// </summary> 512 513 /// <param name="pageIndex">当前页</param> 514 515 /// <param name="pageSize">页大小</param> 516 517 /// <param name="predicate">条件</param> 518 519 /// <param name="ordering">排序</param> 520 521 /// <param name="args">条件参数</param> 522 523 /// <returns></returns> 524 525 public virtual IQueryable<T> GetPagedList(int pageIndex, int pageSize, string predicate, string ordering, params object[] args) 526 527 { 528 529 var result = (from p in DbSlave.Set<T>() 530 531 select p).AsQueryable(); 532 533 534 535 if (!string.IsNullOrWhiteSpace(predicate)) 536 537 result = result.Where(predicate, args); 538 539 540 541 if (!string.IsNullOrWhiteSpace(ordering)) 542 543 result = result.OrderBy(ordering); 544 545 546 547 return result.Skip((pageIndex - 1) * pageSize).Take(pageSize); 548 549 } 550 551 552 553 /// <summary> 554 555 /// 获取记录总数 556 557 /// </summary> 558 559 /// <param name="predicate">查询条件</param> 560 561 /// <param name="args">条件参数</param> 562 563 /// <returns></returns> 564 565 public virtual int GetRecordCount(string predicate, params object[] args) 566 567 { 568 569 if (string.IsNullOrWhiteSpace(predicate)) 570 571 { 572 573 return DbSlave.Set<T>().Count(); 574 575 } 576 577 else 578 579 { 580 581 return DbSlave.Set<T>().Where(predicate, args).Count(); 582 583 } 584 585 } 586 587 588 589 /// <summary> 590 591 /// 事务性保存 读库 592 593 /// </summary> 594 595 public int Save() 596 597 { 598 599 int result = DbMaster.SaveChanges(); 600 601 return result; 602 603 } 604 605 606 607 #endregion 608 609 } 610 611 }
四、配置文件参数配置：
appsetting.json
{
"urls": "http://*:5009",
"ConnectionStrings": [
//主库，用于写操作
{
"ConnectionString": "Server=.;UserId=xxx;PassWord=xxx;Database=xx;Charset=utf8;"
},
//从库1，用于读操作可以有n个
{
"ConnectionString_1":"Server=.;UserId=xxx;PassWord=xxx;Database=xx;Charset=utf8;"
},
//从库2，用于读操作可以有n个
{
"ConnectionString_2":"Server=.;UserId=xxx;PassWord=xxx;Database=xxx;Charset=utf8;"
}
],
"DbCount": 2,//从库数量
"RedisConnectionString": "ip:端口,defaultdatabase=1",//Redis缓存服务器
"IsRedis": true,//是否启用Redis缓存
"Logging": {
"IncludeScopes": false,
"LogLevel": {
"Default": "Warning"
}
}
}
五、以上就是全部内容，如有疑问或发现bug请移步QQ群：855531299共同讨论学习；
源码地址：https://gitee.com/shangyakejiwenhua/sykj
原文：https://www.cnblogs.com/liuxiang1986/p/11100985.html