我们公司2019年web开发已迁移至.NET core，目前有部分平台随着用户量增加，单一数据库部署已经无法满足我们的业务需求，一直在寻找EF CORE读写分离解决方案，目前在各大技术论坛上还没找到很好的方案，根据之前找到的读写分离方案，综合目前EF core 的能力，自己编写了一套EF core实现mysql读写分离的解决方案，目前以应用到正式生产环境(Linux)中，日活跃用户20W，木有发现明显BUG，推荐个大家使用，部分代码参考文章()，废话不多说直接上代码：
一、读写分离，采用的是一主多从，主库进行数据写操作，从库进行数据读操作；对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"
}
}
}本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
