.NET EF ShardingCore实现仅分库

参考文章
官方文档分库

安装ShardingCore 7.X.0.9以上版本

我用7.X.0.8的ShardingCore仅分库时，会导致无法查询数据。本文的版本是7.7.0.12

创建实体

    public enum OrderStatus
    {
        NoPay = 1,
        Paying = 2,
        Payed = 4,
        PayFail = 8
    }
    public class Order : Entity<long>
    {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public override long Id { get => base.Id; set => base.Id = value; }

        public string Payer { get; set; }
        public long Money { get; set; }
        public string Area { get; set; }
        public OrderStatus Status { get; set; }
    }

添加DbSet

public virtual DbSet<Order> Orders { get; set; }

分库路由

    public class OrderDataSourceRoute : AbstractShardingOperatorVirtualDataSourceRoute<Order, string>
    {
        private readonly List<string> _dataSources = Enumerable.Range(0, 4).Select(o => (o % 100).ToString().PadLeft(2, '0')).ToList();

        public override string ShardingKeyToDataSourceName(object shardingKey)
        {
            var shardingKeyToDataSourceName = Math.Abs(ShardingCoreHelper.GetStringHashCode(shardingKey?.ToString() ?? string.Empty) % 4).ToString().PadLeft(2, '0');
            return shardingKeyToDataSourceName;
        }

        public override List<string> GetAllDataSourceNames()
        {
            return _dataSources;
        }

        public override bool AddDataSourceName(string dataSourceName)
        {
            if (_dataSources.Any(o => o == dataSourceName))
                return false;
            _dataSources.Add(dataSourceName);
            return true;
        }

        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 override void Configure(EntityMetadataDataSourceBuilder<Order> builder)
        {
            builder.ShardingProperty(o => o.Area);
        }
    }

Startup分片配置

如果需要Code-First，需要同步修改MyDbContextFactory的分片配置

            services.AddShardingDbContext<MyDbContext>()
                .UseRouteConfig(op =>
                {
                    op.AddShardingDataSourceRoute<OrderDataSourceRoute>();
                }).UseConfig((sp, op) =>
                {
                    //op.ThrowIfQueryRouteNotMatch = false;
                    op.UseShardingQuery((conn, builder) =>
                    {
                        builder.UseSqlServer(conn);
                        builder.ReplaceService<IMigrationsModelDiffer, RemoveForeignKeyMigrationsModelDiffer>();
                    });
                    op.UseShardingTransaction((conn, builder) =>
                    {
                        builder.UseSqlServer(conn);
                        builder.ReplaceService<IMigrationsModelDiffer, RemoveForeignKeyMigrationsModelDiffer>();
                    });
                    op.AddDefaultDataSource(
                        "00",
                        _appConfiguration["ConnectionStrings:Default"]//"Data Source=localhost;Initial Catalog=DB;Integrated Security=True;"
                    );
                    op.AddExtraDataSource(sp =>
                    {
                        return Enumerable.Range(1, 3).Select(o => o.ToString().PadLeft(2, '0')).ToList()
                            .ToDictionary(x => x,
                            x => $"Server=localhost; Database=Db{x}; Trusted_Connection=True;TrustServerCertificate=True;Integrated Security=True;");
                    });
                    //如果需要迁移code-first必须要自行处理
                    op.UseShardingMigrationConfigure(b =>
                    {
                        b.ReplaceService<IMigrationsSqlGenerator, ShardingSqlServerMigrationsSqlGenerator>();
                    });
                }).AddShardingCore();

测试接口

        [HttpGet]
        public async Task<IActionResult> Get()
        {
            var list = await _dbContext.Set<Order>()
                //.Where(x => x.Area == "00")
                .ToListAsync();

            return Ok(list);
        }

        [HttpPost]
        public async Task Insert()
        {
            await _dbContext.Set<Order>().AddRangeAsync(new List<Order>() {
                new Order()
                {
                    Area = "00"
                },
                new Order()
                {
                    Area = "01"
                },
                new Order()
                {
                    Area = "02"
                },
                new Order()
                {
                    Area = "03"
                },
                new Order()
                {
                    Area = "04"
                },
                new Order()
                {
                    Area = "aa"
                }
            });

            await _dbContext.SaveChangesAsync();
        }

监听inert sql：

监听get sql：