.Net下極限生產力之分表分庫全自動化Migrations Code-First ## 介紹本文ShardinfCore版本x.6.x.x+ 本期主角: - [`ShardingCore`](https://github.com/dotnetcore/sharding-core) 一款ef-cor ...

.Net下極限生產力之分表分庫全自動化Migrations Code-First

## 介紹本文ShardinfCore版本x.6.x.x+ 本期主角: - [`ShardingCore`](https://github.com/dotnetcore/sharding-core) 一款ef-core下高性能、輕量級針對分表分庫讀寫分離的解決方案，具有零依賴、零學習成本、零業務代碼入侵適配

開始

本次我們的主題就是極限生產力,其他語言望塵莫及的分表分庫全自動化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不可以為空,你可以自己賦值，但是這樣abp的部分特性就不能用了,所以我們做一下相容

  //在TodoApp.Domain.Shared新增兩個介面（非必須）
    public interface IShardingKeyIsCreationTime
    {
    }
    public interface IShardingKeyIsGuId
    {
    }
    public class TodoItem : BasicAggregateRoot<Guid>,IShardingKeyIsGuId//,IShardingKeyIsCreationTime
    {
        public string Text { get; set; }
    }

    //不做時間分片所以不需要提前賦值
    public class TodoItem : BasicAggregateRoot<Guid>,IShardingKeyIsGuId//,IShardingKeyIsCreationTime
    {
        public string Text { get; set; }
    }

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

//靜態創建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<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();
    }
}

//Program
using 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

您都看到這邊了確定不點個star或者贊嗎,一款.Net不得不學的分庫分表解決方案,簡單理解為sharding-jdbc在.net中的實現並且支持更多特性和更優秀的數據聚合,擁有原生性能的97%,並且無業務侵入性,支持未分片的所有efcore原生查詢

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

.Net下極限生產力之分表分庫全自動化Migrations Code-First

目錄

開始

移除靜態容器

原生efcore

添加依賴 ShardingCore 6.6.0.3 MySql

創建一個todo實體

創建dbcontext

分庫路由

新建遷移資料庫腳本生成

配置依賴註入

添加遷移文件

啟動程式

crud

添加todo欄位並遷移

集成AbpVNext

新建兩個介面用於賦值創建時間和guid

AbpDbContext抽象類

新增分庫分表路由

編寫sqlserver分片遷移腳本生成

abp的efcore模塊註入

啟動abp遷移項目

插入todoitem

集成Furion

添加依賴 ShardingCore 6.6.0.3 MySql

新增todoitem

新增帶分片的DbContext和Abp一樣

新增分表分庫路由

編寫遷移文件

啟動註入

添加遷移文件

啟動

集成WTM

添加依賴

添加依賴 ShardingCore 6.6.0.3 MySql

新增分表分庫路由

創建DbContextCreator

遷移腳本

靜態構造IShardingRuntimeContext

創建抽象分片DbContext

修改dbcontext

註入ShardingCore

遷移

啟動程式

crud

最後的最後