.Net下極限生產力之efcore分表分庫全自動化遷移CodeFirst

開始

本次我們的主題就是極限生產力,其他語言望塵莫及的分表分庫全自動化Migrations Code-First 加 efcore 分表分庫無感開發

還記得上次發布博客還是在上次,上次發布了如何兼容WTM框架后也有不少小伙伴來問我如何兼容如何遷移等問題,經過這么多框架的兼容我自己也認識到了一些問題,譬如在ShardingCore初始化前使用(畢竟efcore)的初始化是在依賴注入的時候不需要手動調用初始化，比如efcore.tool的遷移的問題,本項目不能遷移,因為efcore.tool在使用命令的時候不會調用Configure導致無法初始化的bug,導致遷移必須要通過新建控制臺程序,而不能在本項目內遷移,再或者code-first和ShardingCore的啟動參數沖突導致需要平凡修改，并且不支持分庫,之前有小伙伴分了300個庫如果自動遷移不能用確實是一件很頭疼的事情,雖然這些問題對于分庫分表而言其實是小事情，但是如果一旦分表分庫到達一定的量級就會難以維護。所以ShardingCore在最近三周內開啟了新的版本,新版本主要是解決上述痛點并且將代碼更加標準的使用

開發軟件一般是先能用，然后好用，最后標準化，ShardingCore也是如此，因為需要擴展efcore所以有時候在不熟悉efcore的擴展方式的時候只能靠靜態類來進行注入訪問,而靜態類其實是一個非常不標準的用法,除非萬不得已。那么新版本x.6.x.x ShardingCore帶來了什么請往下看

移除靜態容器

靜態容器的使用導致ShardingCore在整個應用程序聲明周期只有一份數據,那么數據都是共享的這個對于后續的測試維護擴展是相當的不利的，沒有單例那種隔離性來的好,所以移除了ShardingContainer,通過提供IShardingRuntimeContext來保證和之前的參數結構的訪問,同一個DbContext類型在使用不同的IShardingRuntimeContext后可以表現出不同的分表分庫特性。

原生efcore

首先我們針對原生efcore進行擴展來達到分庫分表+code-first自動遷移開發

添加依賴 ShardingCore 6.6.0.3 MySql

//請安裝最新版本目前x.6.0.3+,第一個版本號6代表efcore的版本號
Install-Package ShardingCore -Version 6.6.0.3Install-Package Pomelo.EntityFrameworkCore.MySql  -Version 6.0.1
Install-Package Microsoft.EntityFrameworkCore.Tools  -Version 6.0.6

創建一個todo實體

public class TodoItem{public string Id { get; set; }public string Text { get; set; }
}

創建dbcontext

簡單的將對象和數據庫做了一下映射當然DbSet+Attribute也是可以的

public class MyDbContext:AbstractShardingDbContext,IShardingTableDbContext
{public MyDbContext(DbContextOptions<MyDbContext> options) : base(options){}public IRouteTail RouteTail { get; set; }protected override void OnModelCreating(ModelBuilder modelBuilder){base.OnModelCreating(modelBuilder);modelBuilder.Entity<TodoItem>(mb =>{mb.HasKey(o => o.Id);mb.Property(o => o.Id).IsRequired().HasMaxLength(50).HasComment("id");mb.Property(o => o.Text).IsRequired().HasMaxLength(256).HasComment("事情");mb.ToTable(nameof(TodoItem));});}
}

新建分庫分表路由

分庫路由

public class TodoItemDataSourceRoute:AbstractShardingOperatorVirtualDataSourceRoute<TodoItem,string>
{    /// <summary>/// id的hashcode取模余3分庫/// </summary>/// <param name="shardingKey"></param>/// <returns></returns>/// <exception cref="InvalidOperationException"></exception>public override string ShardingKeyToDataSourceName(object shardingKey){        if (shardingKey == null) throw new InvalidOperationException("sharding key cant null");var stringHashCode = ShardingCoreHelper.GetStringHashCode(shardingKey.ToString());        return $"ds{(Math.Abs(stringHashCode) % 3)}";//ds0,ds1,ds2}private readonly List<string> _dataSources = new List<string>() { "ds0", "ds1", "ds2" };public override List<string> GetAllDataSourceNames(){        return _dataSources;}public override bool AddDataSourceName(string dataSourceName){throw new NotImplementedException();}    /// <summary>/// id分庫/// </summary>/// <param name="builder"></param>public override void Configure(EntityMetadataDataSourceBuilder<TodoItem> builder){builder.ShardingProperty(o => o.Id);}public override 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;}}}
}折疊

分表路由

public class TodoItemTableRoute:AbstractSimpleShardingModKeyStringVirtualTableRoute<TodoItem>
{public TodoItemTableRoute() : base(2, 3){}    /// <summary>/// 正常情況下不會用內容來做分片鍵因為作為分片鍵有個前提就是不會被修改/// </summary>/// <param name="builder"></param>public override void Configure(EntityMetadataTableBuilder<TodoItem> builder){builder.ShardingProperty(o => o.Text);}
}

新建遷移數據庫腳本生成

public class ShardingMySqlMigrationsSqlGenerator:MySqlMigrationsSqlGenerator
{private readonly IShardingRuntimeContext _shardingRuntimeContext;public ShardingMySqlMigrationsSqlGenerator(MigrationsSqlGeneratorDependencies dependencies, IRelationalAnnotationProvider annotationProvider, IMySqlOptions options,IShardingRuntimeContext shardingRuntimeContext) : base(dependencies, annotationProvider, options){_shardingRuntimeContext = shardingRuntimeContext;}protected override void Generate(MigrationOperation operation,IModel model,MigrationCommandListBuilder builder){var oldCmds = builder.GetCommandList().ToList();base.Generate(operation, model, builder);var newCmds = builder.GetCommandList().ToList();var addCmds = newCmds.Where(x => !oldCmds.Contains(x)).ToList();MigrationHelper.Generate(_shardingRuntimeContext,operation, builder, Dependencies.SqlGenerationHelper, addCmds);}
}

配置依賴注入

ILoggerFactory efLogger = LoggerFactory.Create(builder =>
{builder.AddFilter((category, level) => category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Information).AddConsole();
});
var builder = WebApplication.CreateBuilder(args);builder.Services.AddControllers();builder.Services.AddShardingDbContext<MyDbContext>().UseRouteConfig(op =>{op.AddShardingTableRoute<TodoItemTableRoute>();op.AddShardingDataSourceRoute<TodoItemDataSourceRoute>();}).UseConfig((sp,op) =>{op.UseShardingQuery((con, b) =>{b.UseMySql(con, new MySqlServerVersion(new Version())).UseLoggerFactory(efLogger);});op.UseShardingTransaction((con, b) =>{b.UseMySql(con, new MySqlServerVersion(new Version())).UseLoggerFactory(efLogger);});op.AddDefaultDataSource("ds0", "server=127.0.0.1;port=3306;database=mydb0;userid=root;password=root;");op.AddExtraDataSource(sp=>new Dictionary<string, string>(){{"ds1", "server=127.0.0.1;port=3306;database=mydb1;userid=root;password=root;"},{"ds2", "server=127.0.0.1;port=3306;database=mydb2;userid=root;password=root;"}});op.UseShardingMigrationConfigure(b =>{b.ReplaceService<IMigrationsSqlGenerator, ShardingMySqlMigrationsSqlGenerator>();});}).AddShardingCore();var app = builder.Build();// Configure the HTTP request pipeline.//如果有按時間分片的需要加定時任務否則可以不加app.Services.UseAutoShardingCreate();using (var scope = app.Services.CreateScope()){var defaultShardingDbContext = scope.ServiceProvider.GetRequiredService<MyDbContext>();     if (defaultShardingDbContext.Database.GetPendingMigrations().Any()){defaultShardingDbContext.Database.Migrate();}} //如果需要在啟動后掃描是否有表卻掃了可以添加這個//app.Services.UseAutoTryCompensateTable();//......app.Run();折疊

添加遷移文件

Add-Migration Init

啟動程序

分表分庫自動遷移

crud

添加todo字段并遷移

接下來我們將針對TodoItem添加一個name字段并且新增一張既不分庫也不分表的表然后進行遷移

public class TodoItem{public string Id { get; set; }public string Text { get; set; }public string Name { get; set; }
}
public class TodoTest{public string Id { get; set; }public string Test { get; set; }
}//docontextprotected override void OnModelCreating(ModelBuilder modelBuilder){base.OnModelCreating(modelBuilder);modelBuilder.Entity<TodoItem>(mb =>{mb.HasKey(o => o.Id);mb.Property(o => o.Id).IsRequired().HasMaxLength(50).HasComment("id");mb.Property(o => o.Text).IsRequired().HasMaxLength(256).HasComment("事情");mb.Property(o => o.Name).HasMaxLength(256).HasComment("姓名");mb.ToTable(nameof(TodoItem));});modelBuilder.Entity<TodoTest>(mb =>{mb.HasKey(o => o.Id);mb.Property(o => o.Id).IsRequired().HasMaxLength(50).HasComment("id");mb.Property(o => o.Test).IsRequired().HasMaxLength(256).HasComment("測試");mb.ToTable(nameof(TodoTest));});}

不出意外我們成功了然后再次啟動

啟動程序后我們驚奇的發現不單原先的表新增了一個name字段，并且為分片未分開的表也被添加進來了

到此為止efcore的原生分庫分表+全自動化遷移Code-First已經全部完成,這不僅大大的提高了程序的性能并且大大的方便了開發人員的維護。

集成AbpVNext

完成了efcore原生的分表分庫遷移我們將進行abp下的操作
首先我們去github下的abp-samples里面下載對應的demo測試，這邊選擇todo-mvc
接著我們本地打開安裝依賴,只需要安裝·ShardingCore· 6.6.0.3。

新建兩個接口用于賦值創建時間和guid

因為ShardingCore需要add,update,remove的時候shardingkey不可以為空,你可以自己賦值，但是這樣efcore的不分性能就不能用了

//在TodoApp.Domain.Shared新增兩個接口（非必須）public interface IShardingKeyIsCreationTime{}public class IShardingKeyIsGuId{}

AbpDbContext抽象類

因為Abp需要繼承AbpDbContext所以這邊進行一個修改因為ShardingCore只需要接口所以可以滿足任何情況
//為了篇幅移除了大部分代碼剩下的可以在文末demo處查看

public abstract class AbstractShardingAbpDbContext<TDbContext> : AbpDbContext<TDbContext>, IShardingDbContext, ISupportShardingReadWritewhere TDbContext : DbContext{private readonly IShardingDbContextExecutor _shardingDbContextExecutor;protected AbstractShardingAbpDbContext(DbContextOptions<TDbContext> options) : base(options){var wrapOptionsExtension = options.FindExtension<ShardingWrapOptionsExtension>();            if (wrapOptionsExtension != null){_shardingDbContextExecutor = new ShardingDbContextExecutor(this);}}public DbContext GetDbContext(string dataSourceName, CreateDbContextStrategyEnum strategy, IRouteTail routeTail){var dbContext = _shardingDbContextExecutor.CreateDbContext(strategy, dataSourceName, routeTail);            if (dbContext is AbpDbContext<TDbContext> abpDbContext && abpDbContext.LazyServiceProvider == null){abpDbContext.LazyServiceProvider = this.LazyServiceProvider;}            return dbContext;}}

新增分庫分表路由

todoitem id取模分庫

public class TodoDataSourceRoute:AbstractShardingOperatorVirtualDataSourceRoute<TodoItem,string>{public override string ShardingKeyToDataSourceName(object shardingKey){            if (shardingKey == null) throw new InvalidOperationException("sharding key cant null");var stringHashCode = ShardingCoreHelper.GetStringHashCode(shardingKey.ToString());            return $"ds{(Math.Abs(stringHashCode) % 3)}";//ds0,ds1,ds2}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<TodoItem> builder){builder.ShardingProperty(o => o.Id);}public override 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;}}}}

todoitem text 取模分表

public class TodoTableRoute:AbstractSimpleShardingModKeyStringVirtualTableRoute<TodoItem>{public TodoTableRoute() : base(2, 5){}public override void Configure(EntityMetadataTableBuilder<TodoItem> builder){builder.ShardingProperty(o => o.Text);}}

編寫sqlserver分片遷移腳本生成

public class ShardingSqlServerMigrationsSqlGenerator: SqlServerMigrationsSqlGenerator{private readonly IShardingRuntimeContext _shardingRuntimeContext;public ShardingSqlServerMigrationsSqlGenerator(IShardingRuntimeContext shardingRuntimeContext,[NotNull] MigrationsSqlGeneratorDependencies dependencies, [NotNull] IRelationalAnnotationProvider migrationsAnnotations) : base(dependencies, migrationsAnnotations){_shardingRuntimeContext = shardingRuntimeContext;}protected override void Generate(MigrationOperation operation,IModel model,MigrationCommandListBuilder builder){var oldCmds = builder.GetCommandList().ToList();base.Generate(operation, model, builder);var newCmds = builder.GetCommandList().ToList();var addCmds = newCmds.Where(x => !oldCmds.Contains(x)).ToList();MigrationHelper.Generate(_shardingRuntimeContext,operation, builder, Dependencies.SqlGenerationHelper, addCmds);}}

abp的efcore模塊注入

TodoAppEntityFrameworkCoreModule編寫注入

public class TodoAppEntityFrameworkCoreModule : AbpModule{public static readonly ILoggerFactory efLogger = LoggerFactory.Create(builder =>{builder.AddFilter((category, level) => category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Information).AddConsole();});public override void PreConfigureServices(ServiceConfigurationContext context){TodoAppEfCoreEntityExtensionMappings.Configure();}public override void ConfigureServices(ServiceConfigurationContext context){context.Services.AddAbpDbContext<TodoAppDbContext>(options =>{                /* Remove "includeAllEntities: true" to create* default repositories only for aggregate roots */options.AddDefaultRepositories(includeAllEntities: true);});Configure<AbpDbContextOptions>(options =>{                /* The main point to change your DBMS.* See also TodoAppDbContextFactory for EF Core tooling. */options.UseSqlServer();options.Configure<TodoAppDbContext>(innerContext =>{ShardingCoreExtension.UseDefaultSharding<TodoAppDbContext>(innerContext.ServiceProvider, innerContext.DbContextOptions);});});context.Services.AddShardingConfigure<TodoAppDbContext>().UseRouteConfig(op =>{op.AddShardingDataSourceRoute<TodoDataSourceRoute>();op.AddShardingTableRoute<TodoTableRoute>();}).UseConfig((sp, op) =>{                  //var loggerFactory = sp.GetRequiredService<ILoggerFactory>();op.UseShardingQuery((conStr, builder) =>{builder.UseSqlServer(conStr).UseLoggerFactory(efLogger);});op.UseShardingTransaction((connection, builder) =>{builder.UseSqlServer(connection).UseLoggerFactory(efLogger);});op.UseShardingMigrationConfigure(builder =>{builder.ReplaceService<IMigrationsSqlGenerator, ShardingSqlServerMigrationsSqlGenerator>();});op.AddDefaultDataSource("ds0", "Server=.;Database=TodoApp;Trusted_Connection=True");op.AddExtraDataSource(sp =>{                        return new Dictionary<string, string>(){{ "ds1", "Server=.;Database=TodoApp1;Trusted_Connection=True" },{ "ds2", "Server=.;Database=TodoApp2;Trusted_Connection=True" }};});}).AddShardingCore();}public override void OnPostApplicationInitialization(ApplicationInitializationContext context){base.OnPostApplicationInitialization(context);            //創建表的定時任務如果有按年月日系統默認路由的需要系統創建的記得開起來context.ServiceProvider.UseAutoShardingCreate();            //補償表 //自動遷移的話不需要//context.ServiceProvider.UseAutoTryCompensateTable();}}折疊

啟動abp遷移項目

啟動

等待輸出

插入todoitem

查詢

驗證

到此為止我們這邊完成了針對abpvnext的分表分庫+自動化遷移的操作

集成Furion

接下來我們開始集成Furion的操作
首先依舊安裝依賴

添加依賴 ShardingCore 6.6.0.3 MySql

Install-Package Furion -Version 3.7.5
//請安裝最新版本目前x.6.0.5+,第一個版本號6代表efcore的版本號
Install-Package ShardingCore -Version 6.6.0.5Install-Package Pomelo.EntityFrameworkCore.MySql  -Version 6.0.1
Install-Package Microsoft.EntityFrameworkCore.Tools  -Version 6.0.6

新增todoitem

public class TodoItem:IEntity, IEntityTypeBuilder<TodoItem>
{public string Id { get; set; }public string Text { get; set; }public void Configure(EntityTypeBuilder<TodoItem> entityBuilder, DbContext dbContext, Type dbContextLocator){entityBuilder.HasKey(o => o.Id);entityBuilder.Property(o => o.Id).IsRequired().HasMaxLength(50).HasComment("id");entityBuilder.Property(o => o.Text).IsRequired().HasMaxLength(256).HasComment("事情");entityBuilder.ToTable(nameof(TodoItem));}
}

新增帶分片的DbContext和Abp一樣

抽象對象直接看遠嗎,這邊直接新增一個dbcontext

public class MyDbContext : AppShardingDbContext<MyDbContext>,IShardingTableDbContext
{public MyDbContext(DbContextOptions<MyDbContext> options) : base(options){}public IRouteTail RouteTail { get; set; }
}

新增分表分庫路由

新增分庫路由

public class TodoItemDataSourceRoute:AbstractShardingOperatorVirtualDataSourceRoute<TodoItem,string>
{    /// <summary>/// id的hashcode取模余3分庫/// </summary>/// <param name="shardingKey"></param>/// <returns></returns>/// <exception cref="InvalidOperationException"></exception>public override string ShardingKeyToDataSourceName(object shardingKey){        if (shardingKey == null) throw new InvalidOperationException("sharding key cant null");var stringHashCode = ShardingCoreHelper.GetStringHashCode(shardingKey.ToString());        return $"ds{(Math.Abs(stringHashCode) % 3)}";//ds0,ds1,ds2}private readonly List<string> _dataSources = new List<string>() { "ds0", "ds1", "ds2" };public override List<string> GetAllDataSourceNames(){        return _dataSources;}public override bool AddDataSourceName(string dataSourceName){throw new NotImplementedException();}    /// <summary>/// id分庫/// </summary>/// <param name="builder"></param>public override void Configure(EntityMetadataDataSourceBuilder<TodoItem> builder){builder.ShardingProperty(o => o.Id);}public override 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;}}}
}折疊

新增分表路由

public class TodoItemTableRoute:AbstractSimpleShardingModKeyStringVirtualTableRoute<TodoItem>
{public TodoItemTableRoute() : base(2, 3){}    /// <summary>/// 正常情況下不會用內容來做分片鍵因為作為分片鍵有個前提就是不會被修改/// </summary>/// <param name="builder"></param>public override void Configure(EntityMetadataTableBuilder<TodoItem> builder){builder.ShardingProperty(o => o.Text);}
}

編寫遷移文件

using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore.Migrations.Operations;
using Pomelo.EntityFrameworkCore.MySql.Infrastructure.Internal;
using Pomelo.EntityFrameworkCore.MySql.Migrations;
using ShardingCore.Core.RuntimeContexts;
using ShardingCore.Helpers;namespace TodoApp;public class ShardingMySqlMigrationsSqlGenerator:MySqlMigrationsSqlGenerator
{private readonly IShardingRuntimeContext _shardingRuntimeContext;public ShardingMySqlMigrationsSqlGenerator(MigrationsSqlGeneratorDependencies dependencies, IRelationalAnnotationProvider annotationProvider, IMySqlOptions options,IShardingRuntimeContext shardingRuntimeContext) : base(dependencies, annotationProvider, options){_shardingRuntimeContext = shardingRuntimeContext;}protected override void Generate(MigrationOperation operation,IModel model,MigrationCommandListBuilder builder){var oldCmds = builder.GetCommandList().ToList();base.Generate(operation, model, builder);var newCmds = builder.GetCommandList().ToList();var addCmds = newCmds.Where(x => !oldCmds.Contains(x)).ToList();MigrationHelper.Generate(_shardingRuntimeContext,operation, builder, Dependencies.SqlGenerationHelper, addCmds);}
}

啟動注入

這邊簡單看了一下furion貌似沒有提供Func<IServiceProvider,DbContextOptionBuilder>的efcore注入方式所以這邊不得已采用靜態方式,
如果采用靜態的方式需要實現一個接口IDbContextCreator

//靜態創建IShardingRuntimeContextpublic class ShardingCoreProvider{private static ILoggerFactory efLogger = LoggerFactory.Create(builder =>{builder.AddFilter((category, level) => category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Information).AddConsole();});private static readonly IShardingRuntimeContext instance;public static IShardingRuntimeContext ShardingRuntimeContext => instance;    static ShardingCoreProvider(){instance=new ShardingRuntimeBuilder<MyDbContext>().UseRouteConfig(op =>{op.AddShardingTableRoute<TodoItemTableRoute>();op.AddShardingDataSourceRoute<TodoItemDataSourceRoute>();}).UseConfig((sp,op) =>{op.UseShardingQuery((con, b) =>{b.UseMySql(con, new MySqlServerVersion(new Version())).UseLoggerFactory(efLogger);});op.UseShardingTransaction((con, b) =>{b.UseMySql(con, new MySqlServerVersion(new Version())).UseLoggerFactory(efLogger);});op.AddDefaultDataSource("ds0", "server=127.0.0.1;port=3306;database=furion0;userid=root;password=root;");op.AddExtraDataSource(sp=>new Dictionary<string, string>(){{"ds1", "server=127.0.0.1;port=3306;database=furion1;userid=root;password=root;"},{"ds2", "server=127.0.0.1;port=3306;database=furion2;userid=root;password=root;"}});op.UseShardingMigrationConfigure(b =>{b.ReplaceService<IMigrationsSqlGenerator, ShardingMySqlMigrationsSqlGenerator>();});}).ReplaceService<IDbContextCreator, CustomerDbContextCreator>(ServiceLifetime.Singleton).Build();}
}//啟動服務public class ShardingCoreComponent:IServiceComponent
{public void Load(IServiceCollection services, ComponentContext componentContext){services.AddControllers();services.AddEndpointsApiExplorer();services.AddSwaggerGen();services.AddDatabaseAccessor(options =>{            // 配置默認數據庫options.AddDb<MyDbContext>(o =>{o.UseDefaultSharding<MyDbContext>(ShardingCoreProvider.ShardingRuntimeContext);});});        //依賴注入services.AddSingleton<IShardingRuntimeContext>(sp => ShardingCoreProvider.ShardingRuntimeContext);}
}
public class CustomerDbContextCreator:ActivatorDbContextCreator<MyDbContext>
{public override DbContext GetShellDbContext(IShardingProvider shardingProvider){var dbContextOptionsBuilder = new DbContextOptionsBuilder<MyDbContext>();dbContextOptionsBuilder.UseDefaultSharding<MyDbContext>(ShardingCoreProvider.ShardingRuntimeContext);        return new MyDbContext(dbContextOptionsBuilder.Options);}
}
public class UseShardingCoreComponent:IApplicationComponent
{public void Load(IApplicationBuilder app, IWebHostEnvironment env, ComponentContext componentContext){        //......app.ApplicationServices.UseAutoShardingCreate();var serviceProvider = app.ApplicationServices;using (var scope = app.ApplicationServices.CreateScope()){var defaultShardingDbContext = scope.ServiceProvider.GetRequiredService<MyDbContext>();            if (defaultShardingDbContext.Database.GetPendingMigrations().Any()){defaultShardingDbContext.Database.Migrate();}}        // app.Services.UseAutoTryCompensateTable();}
}//Programusing TodoApp;Serve.Run(RunOptions.Default.AddComponent<ShardingCoreComponent>().UseComponent<UseShardingCoreComponent>());折疊

添加遷移文件

啟動

增刪改查

集成WTM

之前也有一次繼承過之后也有因為遷移過于麻煩所以這邊ShardingCore出了更加完善遷移方案并且使用起來code-first更加無感

添加依賴

添加依賴 ShardingCore 6.6.0.3 MySql

//請安裝最新版本目前x.6.0.5+,第一個版本號6代表efcore的版本號
Install-Package ShardingCore -Version 6.6.0.5
Install-Package Microsoft.EntityFrameworkCore.Tools -Version 6.0.6

新增分表分庫路由

//分庫路由public class TodoDataSourceRoute:AbstractShardingOperatorVirtualDataSourceRoute<Todo,string>
{    /// <summary>/// id的hashcode取模余3分庫/// </summary>/// <param name="shardingKey"></param>/// <returns></returns>/// <exception cref="InvalidOperationException"></exception>public override string ShardingKeyToDataSourceName(object shardingKey){        if (shardingKey == null) throw new InvalidOperationException("sharding key cant null");var stringHashCode = ShardingCoreHelper.GetStringHashCode(shardingKey.ToString());        return $"ds{(Math.Abs(stringHashCode) % 3)}";//ds0,ds1,ds2}private readonly List<string> _dataSources = new List<string>() { "ds0", "ds1", "ds2" };public override List<string> GetAllDataSourceNames(){        return _dataSources;}public override bool AddDataSourceName(string dataSourceName){throw new NotImplementedException();}    /// <summary>/// id分庫/// </summary>/// <param name="builder"></param>public override void Configure(EntityMetadataDataSourceBuilder<Todo> builder){builder.ShardingProperty(o => o.Id);}public override 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;}}}
}//分表路由public class TodoTableRoute:AbstractSimpleShardingModKeyStringVirtualTableRoute<Todo>
{public TodoTableRoute() : base(2, 3){}    /// <summary>/// 正常情況下不會用內容來做分片鍵因為作為分片鍵有個前提就是不會被修改/// </summary>/// <param name="builder"></param>public override void Configure(EntityMetadataTableBuilder<Todo> builder){builder.ShardingProperty(o => o.Name);}
}折疊

創建DbContextCreator

public class WTMDbContextCreator:IDbContextCreator
{public DbContext CreateDbContext(DbContext shellDbContext, ShardingDbContextOptions shardingDbContextOptions){var context = new DataContext((DbContextOptions<DataContext>)shardingDbContextOptions.DbContextOptions);context.RouteTail = shardingDbContextOptions.RouteTail;        return context;}public DbContext GetShellDbContext(IShardingProvider shardingProvider){var dbContextOptionsBuilder = new DbContextOptionsBuilder<DataContext>();dbContextOptionsBuilder.UseDefaultSharding<DataContext>(ShardingCoreProvider.ShardingRuntimeContext);        return new DataContext(dbContextOptionsBuilder.Options);}
}

遷移腳本

public class ShardingMySqlMigrationsSqlGenerator:MySqlMigrationsSqlGenerator
{private readonly IShardingRuntimeContext _shardingRuntimeContext;public ShardingMySqlMigrationsSqlGenerator(MigrationsSqlGeneratorDependencies dependencies, IRelationalAnnotationProvider annotationProvider, IMySqlOptions options,IShardingRuntimeContext shardingRuntimeContext) : base(dependencies, annotationProvider, options){_shardingRuntimeContext = shardingRuntimeContext;}protected override void Generate(MigrationOperation operation,IModel model,MigrationCommandListBuilder builder){var oldCmds = builder.GetCommandList().ToList();base.Generate(operation, model, builder);var newCmds = builder.GetCommandList().ToList();var addCmds = newCmds.Where(x => !oldCmds.Contains(x)).ToList();MigrationHelper.Generate(_shardingRuntimeContext,operation, builder, Dependencies.SqlGenerationHelper, addCmds);}
}

靜態構造IShardingRuntimeContext

因為WTM在創建dbcontext并不是通過依賴注入創建的而是由其余的內部實現所以為了兼容我們這邊只能通過靜態IShardingRuntimeContext注入

public class ShardingCoreProvider{private static ILoggerFactory efLogger = LoggerFactory.Create(builder =>{builder.AddFilter((category, level) => category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Information).AddConsole();});private static readonly IShardingRuntimeContext instance;public static IShardingRuntimeContext ShardingRuntimeContext => instance;    static ShardingCoreProvider(){instance=new ShardingRuntimeBuilder<DataContext>().UseRouteConfig(op =>{op.AddShardingTableRoute<TodoRoute>();op.AddShardingDataSourceRoute<TodoDataSourceRoute>();}).UseConfig((sp,op) =>{op.UseShardingQuery((con, b) =>{b.UseMySql(con, new MySqlServerVersion(new Version())).UseLoggerFactory(efLogger);});op.UseShardingTransaction((con, b) =>{b.UseMySql(con, new MySqlServerVersion(new Version())).UseLoggerFactory(efLogger);});op.AddDefaultDataSource("ds0", "server=127.0.0.1;port=3306;database=wtm0;userid=root;password=root;");op.AddExtraDataSource(sp=>new Dictionary<string, string>(){{"ds1", "server=127.0.0.1;port=3306;database=wtm1;userid=root;password=root;"},{"ds2", "server=127.0.0.1;port=3306;database=wtm2;userid=root;password=root;"}});op.UseShardingMigrationConfigure(b =>{b.ReplaceService<IMigrationsSqlGenerator, ShardingMySqlMigrationsSqlGenerator>();});}).ReplaceService<IDbContextCreator, WTMDbContextCreator>(ServiceLifetime.Singleton).Build();}
}折疊

創建抽象分片DbContext

因為過于長所以這邊只顯示主要部分其余通過demo查看

public abstract class AbstractShardingFrameworkContext:FrameworkContext, IShardingDbContext, ISupportShardingReadWrite{protected IShardingDbContextExecutor ShardingDbContextExecutor{get;}public AbstractShardingFrameworkContext(CS cs): base(cs){ShardingDbContextExecutor =new ShardingDbContextExecutor(this);IsExecutor = false;}public AbstractShardingFrameworkContext(string cs, DBTypeEnum dbtype): base(cs, dbtype){ShardingDbContextExecutor =new ShardingDbContextExecutor(this);IsExecutor = false;}public AbstractShardingFrameworkContext(string cs, DBTypeEnum dbtype, string version = null): base(cs, dbtype, version){ShardingDbContextExecutor =new ShardingDbContextExecutor(this);IsExecutor = false;}public AbstractShardingFrameworkContext(DbContextOptions options) : base(options){var wrapOptionsExtension = options.FindExtension<ShardingWrapOptionsExtension>();            if (wrapOptionsExtension != null){ShardingDbContextExecutor =new ShardingDbContextExecutor(this);;}IsExecutor = wrapOptionsExtension == null;}protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder){            if (this.CSName!=null){base.OnConfiguring(optionsBuilder);optionsBuilder.UseDefaultSharding<DataContext>(ShardingCoreProvider.ShardingRuntimeContext);}}public DbContext GetDbContext(string dataSourceName, CreateDbContextStrategyEnum strategy, IRouteTail routeTail){            return ShardingDbContextExecutor.CreateDbContext(strategy, dataSourceName, routeTail);}
}折疊

修改dbcontext

public class DataContextFactory : IDesignTimeDbContextFactory<DataContext>{public DataContext CreateDbContext(string[] args){var virtualDataSource = ShardingCoreProvider.ShardingRuntimeContext.GetVirtualDataSource();var defaultConnectionString = virtualDataSource.DefaultConnectionString;            return new DataContext(defaultConnectionString, DBTypeEnum.MySql);}}

注入ShardingCore

移除掉了之前的多余代碼

public void ConfigureServices(IServiceCollection services){            //....services.AddSingleton<IShardingRuntimeContext>(sp => ShardingCoreProvider.ShardingRuntimeContext);}public void Configure(IApplicationBuilder app, IOptionsMonitor<Configs> configs){IconFontsHelper.GenerateIconFont();            // using (var scope = app.ApplicationServices.CreateScope())// {//     var requiredService = scope.ServiceProvider.GetRequiredService<WTMContext>();//     var requiredServiceDc = requiredService.DC;// }//定時任務app.ApplicationServices.UseAutoShardingCreate();using (var dbconContext=new DataContextFactory().CreateDbContext(new string[0])){dbconContext.Database.Migrate();}            //補齊表防止iis之類的休眠導致按天按月的表沒有新建//app.ApplicationServices.UseAutoTryCompensateTable();//....}