参考文章
官方文档分库
安装ShardingCore 7.X.0.9以上版本
我用7.X.0.8的ShardingCore仅分库时,会导致无法查询数据
创建实体
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: