.net core 使用 ShardingCore 按时间分表（一）

ShardingCore 官方文档地址

快速上手AspNetCore | ShardingCore文档 (xuejmnet.github.io)

Nuget 下载

ShardingCore

注意 ShardingCore 包中含有 Microsoft.EntityFrameworkCore 包 Microsoft.EntityFrameworkCore.Relational 包

 所以 项目 就没有必要在 单独引用 Microsoft.EntityFrameworkCore 包

ShardingCore 抽象 封装了 DBContext  抽象类为 AbstractShardingDbContext 所以 只需要继承该抽象类即可

 

 分表操作 主要实现 IShardingTableDbContext 接口

 

虚拟路由 按时间分

AbstractSimpleShardingMonthKeyDateTimeVirtualTableRoute

 

 

 

 

上代码

搞个简单类

public class Order
    {
        [Key]
        public string Id { get; set; }
        public string Payer { get; set; }
        public long Money { get; set; }
        public string Area { get; set; }
        public OrderStatusEnum OrderStatus { get; set; }

        public string UserId { get; set; }

        public DateTime CreationTime { get; set; }
    }
    public enum OrderStatusEnum
    {
        NoPay = 1,
        Paying = 2,
        Payed = 3,
        PayFail = 4
    }

 

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

 

按时间分表

public class OrderVirtualTableRoute : AbstractSimpleShardingMonthKeyDateTimeVirtualTableRoute<Order>
    {
        public override DateTime GetBeginTime()
        {
            return new DateTime(2023, 5, 1);
        }
        //注意一定要配置或者采用接口+标签也是可以的
        public override void Configure(EntityMetadataTableBuilder<Order> builder)
        {
            builder.ShardingProperty(o => o.CreationTime);
        }

        public override bool AutoCreateTableByTime()
        {
            return true;
        }
    }

数据迁移需要实现 MySqlMigrationsSqlGenerator

 public class ShardingMySqlMigrationsSqlGenerator : MySqlMigrationsSqlGenerator
    {
        private readonly IShardingRuntimeContext _shardingRuntimeContext;
        public ShardingMySqlMigrationsSqlGenerator(IShardingRuntimeContext shardingRuntimeContext, MigrationsSqlGeneratorDependencies dependencies, ICommandBatchPreparer commandBatchPreparer, IMySqlOptions options) : base(dependencies, commandBatchPreparer, 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);
        }
    }

种子数据

 //种子数据
    public static class StartupExtension
    {
        //public static void UseShardingCore(this IApplicationBuilder app)
        //{
        //    app.ApplicationServices.GetRequiredService<IShardingBootstrapper>().Start();
        //}
        public static void InitSeed(this IApplicationBuilder app)
        {
            using (var serviceScope = app.ApplicationServices.CreateScope())
            {
                var myDbContext = serviceScope.ServiceProvider.GetRequiredService<MyDbContext>();
                if (!myDbContext.Set<Order>().Any())
                {
                    List<Order> orders = new List<Order>(10);
                    var begin = new DateTime(2023, 5, 1, 3, 3, 3);
                    for (int i = 0; i < 10; i++)
                    {
                        var order = new Order()
                        {
                            Id = i.ToString(),
                            Payer = $"{i % 10}",
                            Area ="A",
                            UserId ="11",
                            Money = 100 + new Random().Next(100, 3000),
                            OrderStatus = (OrderStatusEnum)(i % 4 + 1),
                            CreationTime = begin.AddDays(i)
                        };
                        orders.Add(order);
                    }
                    myDbContext.AddRange(orders);
                    myDbContext.SaveChanges();
                }
            }
        }
    }

 

依赖注入配置

builder.Services.AddShardingDbContext<MyDbContext>().UseRouteConfig(op =>
{
    //添加order表虚拟路由
    op.AddShardingTableRoute<OrderVirtualTableRoute>();
}).UseConfig(op =>
{
    ILoggerFactory factory = LoggerFactory.Create(config =>
        {
            config.AddConsole();
        });
    op.UseShardingMigrationConfigure(b =>
    {
        //数据迁移
        b.ReplaceService<IMigrationsSqlGenerator, ShardingMySqlMigrationsSqlGenerator>();
    });
    op.ThrowIfQueryRouteNotMatch = false;
    op.UseShardingQuery((conStr, builder) =>
    {
        //查询
        builder.UseMySql(conStr, MySqlServerVersion.AutoDetect(conStr)).UseLoggerFactory(factory);
    });
    op.UseShardingTransaction((connection, builder) =>
    {
        //事务
        builder.UseMySql(connection, MySqlServerVersion.AutoDetect(connection.ConnectionString)).UseLoggerFactory(factory);
    });
    //默认数据源
    op.AddDefaultDataSource("ds0", "Data Source=192.168.0.192;Initial Catalog=test;uid=root;pwd=123456;");
    op.AddReadWriteSeparation(sp =>
    {
        return new Dictionary<string, IEnumerable<string>>()
                        {
                        {
                            "ds0", new List<string>()
                            {
                                "Data Source=192.168.0.192;Initial Catalog=test;uid=root;pwd=123456;"
                            }
                        }
                        };
    }, ReadStrategyEnum.Loop, defaultEnable: true);
}).AddShardingCore();

 

中间件配置

//建议补偿表在迁移后面
using (var scope = app.Services.CreateScope())
{
    var myDbContext = scope.ServiceProvider.GetService<MyDbContext>();
    //如果没有迁移那么就直接创建表和库
    //myDbContext.Database.EnsureCreated();
    //如果有迁移使用下面的
     myDbContext.Database.Migrate();
}

//not required, enable check table missing and auto create,非必须  启动检查缺少的表并且创建
app.Services.UseAutoTryCompensateTable();

//种子数据
app.InitSeed();