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