/// <summary> /// order table /// </summary> public class Order { /// <summary> /// order Id /// </summary> public string Id { get; set; } /// <summary> /// payer id /// </summary> public string Payer { get; set; } /// <summary> /// pay money cent /// </summary> public long Money { get; set; } /// <summary> /// area /// </summary> public string Area { get; set; } /// <summary> /// order status /// </summary> public OrderStatusEnum OrderStatus { get; set; } /// <summary> /// CreationTime /// </summary> public DateTime CreationTime { get; set; } } public enum OrderStatusEnum { NoPay=1, Paying=2, Payed=3, PayFail=4 }

 public class MyDbContext:AbstractShardingDbContext,IShardingTableDbContext { public MyDbContext(DbContextOptions<MyDbContext> options) : base(options) { } protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.Entity<Order>(entity => { entity.HasKey(o => o.Id); entity.Property(o => o.Id).IsRequired().IsUnicode(false).HasMaxLength(50); entity.Property(o=>o.Payer).IsRequired().IsUnicode(false).HasMaxLength(50); entity.Property(o => o.Area).IsRequired().IsUnicode(false).HasMaxLength(50); entity.Property(o => o.OrderStatus).HasConversion<int>(); //really table name is Order_202101,Order_202102,Order_202103..... entity.ToTable(nameof(Order)); }); } /// <summary> /// empty implment if use sharding table /// </summary> public IRouteTail RouteTail { get; set; } }

//Route constructor support dependency injection,that's life time scope is `Singleton` public class OrderVirtualTableRoute:AbstractSimpleShardingMonthKeyDateTimeVirtualTableRoute<Order> { //private readonly IServiceProvider _serviceProvider; //public OrderVirtualTableRoute(IServiceProvider serviceProvider) //{ //_serviceProvider = serviceProvider; //} /// <summary> /// fixed value don't use DateTime.Now because if if application restart this value where change /// </summary> /// <returns></returns> public override DateTime GetBeginTime() { return new DateTime(2021, 1, 1); } /// <summary> /// configure sharding property /// </summary> /// <param name="builder"></param> public override void Configure(EntityMetadataTableBuilder<Order> builder) { builder.ShardingProperty(o => o.CreationTime); } /// <summary> /// enable auto create table job /// </summary> /// <returns></returns> public override bool AutoCreateTableByTime() { return true; } }

 public void ConfigureServices(IServiceCollection services) { //sharding config services.AddShardingDbContext<MyDbContext>() .UseRouteConfig(op => { op.AddShardingTableRoute<OrderVirtualTableRoute>(); }).UseConfig(op => { op.UseShardingQuery((connStr, builder) => { //connStr is delegate input param builder.UseSqlServer(connStr); }); op.UseShardingTransaction((connection, builder) => { //connection is delegate input param builder.UseSqlServer(connection); }); //use your data base connection string op.AddDefaultDataSource(Guid.NewGuid().ToString("n"), "Data Source=localhost;Initial Catalog=EFCoreShardingTableDB;Integrated Security=True;"); }).AddShardingCore(); } public void Configure(IApplicationBuilder app, IWebHostEnvironment env) { if (env.IsDevelopment()) { app.UseDeveloperExceptionPage(); } //not required, enable check table missing and auto create,非必须 启动检查缺少的表并且创建 app.ApplicationServices.UseAutoTryCompensateTable(); // other configure.... }

[Route("api/[controller]")] public class ValuesController : Controller { private readonly MyDbContext _myDbContext; public ValuesController(MyDbContext myDbContext) { _myDbContext = myDbContext; } [HttpGet] public async Task<IActionResult> Get() { //_myDbContext.Add(order); //_myDbContext.Udpate(order); //_myDbContext.Remove(order); //_myDbContext.SaveChanges(); var order = await _myDbContext.Set<Order>().FirstOrDefaultAsync(o => o.Id == "2"); return OK(order) } }

 public class SysUserMod { /// <summary> /// 用户Id用于分表 /// </summary> public string Id { get; set; } /// <summary> /// 用户名称 /// </summary> public string Name { get; set; } /// <summary> /// 用户姓名 /// </summary> public int Age { get; set; } } 

 public class SysUserModVirtualTableRoute : AbstractSimpleShardingModKeyStringVirtualRoute<SysUserMod> { //2 tail length:00,01,02......99 //3 hashcode % 3: [0,1,2] public SysUserModVirtualTableRoute() : base(2,3) { } public override void Configure(EntityMetadataTableBuilder<SysUserMod> builder) { builder.ShardingProperty(o => o.Id); } }

 //DefaultTableDbContext is acutal execute dbcontext public class DefaultShardingDbContext:AbstractShardingDbContext,IShardingTableDbContext { public DefaultShardingDbContext(DbContextOptions<DefaultShardingDbContext> options) : base(options) { } protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.ApplyConfiguration(new SysUserModMap()); } public IRouteTail RouteTail { get; set; } }

 public void ConfigureServices(IServiceCollection services) { services.AddControllers(); //if u want use no sharding operate //services.AddDbContext<DefaultTableDbContext>(o => o.UseSqlServer("Data Source=localhost;Initial Catalog=ShardingCoreDB;Integrated Security=True")); //add shardingdbcontext support life scope services.AddShardingDbContext<DefaultShardingDbContext>( (conStr, builder) => builder.UseSqlServer(conStr) ) .Begin(o => { o.CreateShardingTableOnStart = true;//create sharding table o.EnsureCreatedWithOutShardingTable = true;//create data source with out sharding table }) .AddShardingTransaction((connection, builder) => builder.UseSqlServer(connection)) .AddDefaultDataSource("ds0", "Data Source=localhost;Initial Catalog=ShardingCoreDB1;Integrated Security=True;") .AddShardingTableRoute(o => { o.AddShardingTableRoute<SysUserModVirtualTableRoute>(); }).End();

 var shardingBootstrapper = app.ApplicationServices.GetRequiredService<IShardingBootstrapper>(); shardingBootstrapper.Start();

 private readonly DefaultShardingDbContext _defaultShardingDbContext; public ctor(DefaultShardingDbContext defaultShardingDbContext) { _defaultShardingDbContext = defaultShardingDbContext; } public async Task Insert_1000() { if (!_defaultShardingDbContext.Set<SysUserMod>().Any()) { var ids = Enumerable.Range(1, 1000); var userMods = new List<SysUserMod>(); foreach (var id in ids) { userMods.Add(new SysUserMod() { Id = id.ToString(), Age = id, Name = $"name_{id}", AgeGroup = Math.Abs(id % 10) }); } _defaultShardingDbContext.AddRange(userMods); await _defaultShardingDbContext.SaveChangesAsync(); } } public async Task ToList_All() { var mods = await _defaultShardingDbContext.Set<SysUserMod>().ToListAsync(); Assert.Equal(1000, mods.Count); var modOrders1 = await _defaultShardingDbContext.Set<SysUserMod>().OrderBy(o => o.Age).ToListAsync(); int ascAge = 1; foreach (var sysUserMod in modOrders1) { Assert.Equal(ascAge, sysUserMod.Age); ascAge++; } var modOrders2 = await _defaultShardingDbContext.Set<SysUserMod>().OrderByDescending(o => o.Age).ToListAsync(); int descAge = 1000; foreach (var sysUserMod in modOrders2) { Assert.Equal(descAge, sysUserMod.Age); descAge--; } }

 public class SysUserMod { /// <summary> /// 用户Id用于分库 /// </summary> public string Id { get; set; } /// <summary> /// 用户名称 /// </summary> public string Name { get; set; } /// <summary> /// 用户姓名 /// </summary> public int Age { get; set; } } 

 public class SysUserModVirtualDataSourceRoute:AbstractShardingOperatorVirtualDataSourceRoute<SysUserMod,string> { protected readonly int Mod=3; protected readonly int TailLength=1; protected readonly char PaddingChar='0'; protected override string ConvertToShardingKey(object shardingKey) { return shardingKey.ToString(); } public override string ShardingKeyToDataSourceName(object shardingKey) { var shardingKeyStr = ConvertToShardingKey(shardingKey); return "ds"+Math.Abs(ShardingCoreHelper.GetStringHashCode(shardingKeyStr) % Mod).ToString().PadLeft(TailLength, PaddingChar); ; } public override List<string> GetAllDataSourceNames() { return new List<string>() { "ds0", "ds1", "ds2" }; } public override bool AddDataSourceName(string dataSourceName) { throw new NotImplementedException(); } public override void Configure(EntityMetadataDataSourceBuilder<SysUserMod> builder) { builder.ShardingProperty(o => o.Name); } protected override Expression<Func<string, bool>> GetRouteToFilter(string shardingKey, ShardingOperatorEnum shardingOperator) { var t = ShardingKeyToDataSourceName(shardingKey); switch (shardingOperator) { case ShardingOperatorEnum.Equal: return tail => tail == t; default: { return tail => true; } } } }

 //DefaultTableDbContext is acutal execute dbcontext public class DefaultShardingDbContext:AbstractShardingDbContext { public DefaultShardingDbContext(DbContextOptions<DefaultShardingDbContext> options) : base(options) { } protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); modelBuilder.ApplyConfiguration(new SysUserModMap()); } }

 public void ConfigureServices(IServiceCollection services) { services.AddControllers(); //add shardingdbcontext support life scope services.AddShardingDbContext<DefaultShardingDbContext>( (conStr, builder) => builder.UseSqlServer(conStr) ).Begin(o => { o.CreateShardingTableOnStart = true; o.EnsureCreatedWithOutShardingTable = true; }) .AddShardingTransaction((connection, builder) => builder.UseSqlServer(connection)) .AddDefaultDataSource("ds0","Data Source=localhost;Initial Catalog=ShardingCoreDBxx0;Integrated Security=True;") .AddShardingDataSource(sp => { return new Dictionary<string, string>() { {"ds1", "Data Source=localhost;Initial Catalog=ShardingCoreDBxx1;Integrated Security=True;"}, {"ds2", "Data Source=localhost;Initial Catalog=ShardingCoreDBxx2;Integrated Security=True;"}, }; }).AddShardingDataSourceRoute(o => { o.AddShardingDatabaseRoute<SysUserModVirtualDataSourceRoute>(); }).End();

 var shardingBootstrapper = app.ApplicationServices.GetRequiredService<IShardingBootstrapper>(); shardingBootstrapper.Start();

 private readonly DefaultShardingDbContext _defaultShardingDbContext; public ctor(DefaultShardingDbContext defaultShardingDbContext) { _defaultShardingDbContext = defaultShardingDbContext; } public async Task Insert_1000() { if (!_defaultShardingDbContext.Set<SysUserMod>().Any()) { var ids = Enumerable.Range(1, 1000); var userMods = new List<SysUserMod>(); foreach (var id in ids) { userMods.Add(new SysUserMod() { Id = id.ToString(), Age = id, Name = $"name_{id}", AgeGroup = Math.Abs(id % 10) }); } _defaultShardingDbContext.AddRange(userMods); await _defaultShardingDbContext.SaveChangesAsync(); } } public async Task ToList_All() { var mods = await _defaultShardingDbContext.Set<SysUserMod>().ToListAsync(); Assert.Equal(1000, mods.Count); var modOrders1 = await _defaultShardingDbContext.Set<SysUserMod>().OrderBy(o => o.Age).ToListAsync(); int ascAge = 1; foreach (var sysUserMod in modOrders1) { Assert.Equal(ascAge, sysUserMod.Age); ascAge++; } var modOrders2 = await _defaultShardingDbContext.Set<SysUserMod>().OrderByDescending(o => o.Age).ToListAsync(); int descAge = 1000; foreach (var sysUserMod in modOrders2) { Assert.Equal(descAge, sysUserMod.Age); descAge--; } }

services.AddShardingDbContext<DefaultShardingDbContext>(.......) .Begin(o => { o.CreateShardingTableOnStart = true; o.EnsureCreatedWithOutShardingTable = true; //autotrack support asnotracking astracking QueryTrackingBehavior.TrackAll o.AutoTrackEntity = true; })

ctor inject IShardingRouteManager shardingRouteManager public class SysUserModVirtualTableRoute : AbstractSimpleShardingModKeyStringVirtualTableRoute<SysUserMod> { /// <summary> /// 开启提示路由 /// </summary> protected override bool EnableHintRoute => true; public SysUserModVirtualTableRoute() : base(2,3) { } } using (_shardingRouteManager.CreateScope()) { _shardingRouteManager.Current.TryCreateOrAddMustTail<SysUserMod>("00"); var mod00s = await _defaultTableDbContext.Set<SysUserMod>().Skip(10).Take(11).ToListAsync(); }

 await _defaultShardingDbContext.SaveChangesAsync(); 

 using (var tran = _defaultTableDbContext.DataBase.BeginTransaction()) { ........ _defaultTableDbContext.SaveChanges(); tran.Commit(); }

var list = new List<SysUserMod>(); ///通过集合返回出对应的k-v归集通过事务开启 var dbContexts = _defaultTableDbContext.BulkShardingEnumerable(list); foreach (var dataSourceMap in dbContexts) { foreach (var tailMap in dataSourceMap.Value) { tailMap.Key.BulkInsert(tailMap.Value.ToList()); //tailMap.Key.BulkDelete(tailMap.Value.ToList()); //tailMap.Key.BulkUpdate(tailMap.Value.ToList()); } } _defaultTableDbContext.SaveChanges(); //or var dbContexts = _defaultTableDbContext.BulkShardingEnumerable(list); using (var tran = _defaultTableDbContext.Database.BeginTransaction()) { foreach (var dataSourceMap in dbContexts) { foreach (var tailMap in dataSourceMap.Value) { tailMap.Key.BulkInsert(tailMap.Value.ToList()); //tailMap.Key.BulkDelete(tailMap.Value.ToList()); //tailMap.Key.BulkUpdate(tailMap.Value.ToList()); } } _defaultTableDbContext.SaveChanges(); tran.Commit(); }

 //切换到只读数据库，只读数据库又只配置了A数据源读取B数据源 _virtualDbContext.ReadWriteSeparationReadOnly(); _virtualDbContext.ReadWriteSeparationWriteOnly();

services.AddShardingDbContext<DefaultShardingDbContext>( (conStr, builder) => builder.UseSqlServer(conStr).UseLoggerFactory(efLogger) ).Begin(o => { o.CreateShardingTableOnStart = true; o.EnsureCreatedWithOutShardingTable = true; }) .AddShardingTransaction((connection, builder) => builder.UseSqlServer(connection).UseLoggerFactory(efLogger)) .AddDefaultDataSource("ds0", "Data Source=localhost;Initial Catalog=ShardingCoreDB1;Integrated Security=True;") .AddShardingTableRoute(o => { o.AddShardingTableRoute<SysUserModVirtualTableRoute>(); }).AddReadWriteSeparation(o => { return new Dictionary<string, ISet<string>>() { { "ds0", new HashSet<string>(){ "Data Source=localhost;Initial Catalog=ShardingCoreDBReadOnly1;Integrated Security=True;", "Data Source=localhost;Initial Catalog=ShardingCoreDBReadOnly2;Integrated Security=True;"} } }; }, ReadStrategyEnum.Loop,defaultEnable:true).End(); _virtualDbContext.ReadWriteSeparationReadOnly(); //reslove read write delay data not found //dbcontext use write connection string  _virtualDbContext.ReadWriteSeparationWriteOnly();

 public class SysUserSalaryPaginationConfiguration:IPaginationConfiguration<SysUserSalary> { public void Configure(PaginationBuilder<SysUserSalary> builder) { builder.PaginationSequence(o => o.Id) .UseRouteCompare(Comparer<string>.Default) .UseQueryMatch(PaginationMatchEnum.Owner | PaginationMatchEnum.Named | PaginationMatchEnum.PrimaryMatch); builder.PaginationSequence(o => o.DateOfMonth) .UseQueryMatch(PaginationMatchEnum.Owner | PaginationMatchEnum.Named | PaginationMatchEnum.PrimaryMatch).UseAppendIfOrderNone(10); builder.PaginationSequence(o => o.Salary) .UseQueryMatch(PaginationMatchEnum.Owner | PaginationMatchEnum.Named | PaginationMatchEnum.PrimaryMatch).UseAppendIfOrderNone(); builder.ConfigReverseShardingPage(0.5d,10000L); } }

 public override IPaginationConfiguration<SysUserSalary> CreatePaginationConfiguration() { return new SysUserSalaryPaginationConfiguration(); }

var shardingPageResultAsync = await _defaultTableDbContext.Set<SysUserMod>().OrderBy(o=>o.Age).ToShardingPageAsync(pageIndex, pageSize);

 public class OrderCreateTimeVirtualTableRoute:AbstractSimpleShardingMonthKeyDateTimeVirtualTableRoute<Order> { //开启表达式缓存 public override bool EnableRouteParseCompileCache => true; }

 public virtual Func<string, bool> CachingCompile(Expression<Func<string, bool>> parseWhere) { if (EnableRouteParseCompileCache) { var doCachingCompile = DoCachingCompile(parseWhere); if (doCachingCompile != null) return doCachingCompile; doCachingCompile = CustomerCachingCompile(parseWhere); if (doCachingCompile != null) return doCachingCompile; } return parseWhere.Compile(); } /// <summary> /// 系统默认永久单表达式缓存 /// </summary> /// <param name="parseWhere"></param> /// <returns>返回null会走<see cref="CustomerCachingCompile"/>这个方法如果还是null就会调用<see cref="Compile"/>方法</returns> protected virtual Func<string, bool> DoCachingCompile(Expression<Func<string, bool>> parseWhere) { var shouldCache = ShouldCache(parseWhere); if(shouldCache) return _routeCompileCaches.GetOrAdd(parseWhere, key => parseWhere.Compile()); return null; } protected virtual Func<string, bool> CustomerCachingCompile(Expression<Func<string, bool>> parseWhere) { return null; }

 return optionsBuilder.UseShardingWrapMark() .ReplaceService<IDbSetSource, ShardingDbSetSource>() .ReplaceService<IQueryCompiler, ShardingQueryCompiler>() .ReplaceService<IDbContextTransactionManager, ShardingRelationalTransactionManager<TShardingDbContext>>() .ReplaceService<IRelationalTransactionFactory, ShardingRelationalTransactionFactory<TShardingDbContext>>();

return optionsBuilder.ReplaceService<IModelCacheKeyFactory, ShardingModelCacheKeyFactory>() .ReplaceService<IModelCustomizer, ShardingModelCustomizer<TShardingDbContext>>();

//支持最终修改 var sresult = _defaultTableDbContext.Set<SysUserMod>().ToList(); var sysUserMod98 = result.FirstOrDefault(o => o.Id == "98"); sysUserMod98.Name = "name_update"+new Random().Next(1,99)+"_98"; await _defaultTableDbContext.SaveChangesAsync(); --log info Executed DbCommand (1ms) [Parameters=[@p1='?' (Size = 128), @p0='?' (Size = 128)], CommandType='Text', CommandTimeout='30'] SET NOCOUNT ON; UPDATE [SysUserMod_02] SET [Name] = @p0 WHERE [Id] = @p1; SELECT @@ROWCOUNT;