EF core 实现读写分离解决方案
我们公司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
上一篇: Hive的一些学习内容
下一篇: python实现傅里叶级数展开的实现
推荐阅读
-
sql server2005实现数据库读写分离介绍
-
从零开始搭建前后端分离的NetCore2.2(EF Core CodeFirst+Autofac)+Vue的项目框架之九如何进行用户权限控制
-
原理解密 → Spring AOP 实现动态数据源(读写分离),底层原理是什么
-
EF core 实现读写分离解决方案
-
读写分离很难吗?springboot结合aop简单就实现了
-
Yii实现多数据库主从读写分离的方法
-
EF Core 迁移过程遇到EF Core tools version版本不相符的解决方案
-
MySQL集群读写分离的自定义实现
-
使用PHP实现Mysql读写分离
-
Windows中使用Mysql-Proxy实现Mysql读写分离