.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.3

Install-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; 
//docontext
 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.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, ISupportShardingReadWrite
                                where 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.5

Install-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();
          //....
          

迁移

启动程序

crud

最后的最后

(ShardingWithFrameWork)[https://github.com/xuejmnet/ShardingWithFramework] https://github.com/xuejmnet/ShardingWithFramework

您都看到这边了确定不点个star或者赞吗,一款.Net不得不学的分库分表解决方案,简单理解为sharding-jdbc在.net中的实现并且支持更多特性和更优秀的数据聚合,拥有原生性能的97%,并且无业务侵入性,支持未分片的所有efcore原生查询

• github地址 https://github.com/xuejmnet/sharding-core

• gitee地址 https://gitee.com/dotnetchina/sharding-core

.net分表分库动态化处理(代码片段)

...绍本期主角:ShardingCore 一款ef-core下高性能、轻量级针对分表分库读写分离的解决方案，具有零依赖、零学习成本、零业务代码入侵我不是efcore怎么办这边肯定有小伙伴要问有没有不是efcore的,我这边很确信的和你讲有并且适... 查看详情

abpvnext集成sharding-core分表分库(代码片段)

...单、高性能、普适性，是一款扩展针对efcore生态下的分表分库的扩展解决方案,支持efcore2+的所有版本,支持efcore2+的所有数据库、支持自定义路由、动态路由、高性能分页、读写分离的一款组件，如果你喜欢这组件或... 查看详情

一款针对efcore轻量级分表分库读写分离的开源项目

...数据量比较大，比如日志记录，我们往往会采用分表分库的方案；为了提升性能，把数据库查询与更新操作分开，这时候就要采用读写分离的方案。分表分库通常包含垂直分库、垂直分表、水平分库和水平分... 查看详情

架构之数据库分表分库

1基本思想之什么是分库分表？从字面上简单理解，就是把原本存储于一个库的数据分块存储到多个库上，把原本存储于一个表的数据分块存储到多个表上。2基本思想之为什么要分库分表？数据库中的数据量不一定是可控的，在... 查看详情

.net/c#分库分表高性能o瀑布流分页(代码片段)

...有零依赖、零学习成本、零业务代码入侵dotnet下唯一一款全自动分表,多字段分表框架,拥有高性能,零依赖、零学习成本、零业务代码入侵,并且支持读写分离动态分表分库,同一种路由可以完全自定义的新星组件框架你的star和点赞... 查看详情

flea-db使用之jpa分库分表实现(代码片段)

JPA分库分表实现在开始本篇的讲解之前，我先来说下之前写过的两篇博文【现在已弃用】：flea-frame-db使用之基于EntityManager实现JPA分表的数据库操作【旧】flea-frame-db使用之基于FleaJPAQuery实现JPA分表查询【旧】这两篇都与分表相关... 查看详情

efcore动态切换schema

原文:EFCore动态切换Schema最近做个分库分表项目，用到schema的切换感觉还是有些坑的，在此分享下。 先简要说下我们的分库分表分库分表规则我定的规则是，订单号（数字）除以16，得出的结果为这个订单所在的数据库，然后... 查看详情

mysql性能优化分享(分库分表)

MYSQL性能优化之分库分表与不停机修改mysql表结构，需要的朋友可以参考下1、分库分表 很明显，一个主表（也就是很重要的表，例如用户表）无限制的增长势必严重影响性能，分库与分表是一个很不错的解决途径，也就是性... 查看详情

订单分库分表实践总结

...最大表的数据量已经是几十亿，数据库处理能力已经到了极限；单库包含多个超大表，占用的硬盘空间已经接近了服务器的硬盘极限，很快将无空间可用；2、性能问题单一服务器处理能力是有限的，单一订单库的TPS也有上限，... 查看详情

flea-db使用之jpa分库分表实现(代码片段)

JPA分库分表实现在开始本篇的讲解之前，我先来说下之前写过的两篇博文【现在已弃用】：flea-frame-db使用之基于EntityManager实现JPA分表的数据库操作【旧】flea-frame-db使用之基于FleaJPAQuery实现JPA分表查询【旧】这两篇都与... 查看详情

.net下你不得不看的分表分库解决方案-多字段分片(代码片段)

...有零依赖、零学习成本、零业务代码入侵dotnet下唯一一款全自动分表,多字段分表框架,拥有高性能,零依赖、零学习成本、零业务代码入侵,并且支持读写分离动态分表分库,同一种路由可以完全自定义的新星组件,通过本框架你不但... 查看详情

海量数据存储解决方案之分库分表原理解析及mycat安装及使用(代码片段)

...文件开始介绍比较常用，也是我在项目中使用的分库分表对海量数据存储的另一种解决方 查看详情

android数据库设计——3，自动化分库分表(代码片段)

分库、分表用户量大的时候必须去分库分表，分库分表也需要自动化分库前置工作/***Describe:修改注解，增加主键标识*/@Target(ElementType.FIELD)@Retention(RetentionPolicy.RUNTIME)public@interfaceDbFieldStringvalue();//表列名booleanis 查看详情

一小时读懂shardingjdbc之分库分表

...额外的服务，可以理解为增强版的jdbc驱动。其中，分库分表的操作是其中的重要一环，接下来就跟随我来看一看，这一操作如何进行。 环境准备 pom.xml <parent>     <groupId>org.springfr 查看详情

mysql分库分表总结

一.何为分库分表分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小ÿ... 查看详情

mysql分库分表总结

一.何为分库分表分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题，将原来独立的数据库拆分成若干数据库组成，将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小ÿ... 查看详情

大型互联网技术之数据库分库分表

https://ke.qq.com/webcourse/index.html#course_id=230866&term_id=100272363&taid=1585448522843602&vid=v14207jn6vq 查看详情

分库分表

...时间飙升时数据库性能出现扛不住的迹象。考虑进行分库分表优化：1、单数据库扩展为多数据库。新增两个数据库，用来分担原来的主库压力。主库原来其他表不动，大表数据割接到分库中。在主库新增路由表，保存分库映射... 查看详情