ShardingCore兼容Code-First数据库迁移 让update-database能更新分表
背景
在我上一篇博客ABP EF CORE 7 集成ShardingCore实现分表写完之后，我发现我还有一个没解决的问题，就是在我执行update-database更新数据库的时候，分表并不会更新，并且程序启动之后也不会更新已经生成好的分表。后来又仔细看了官方的例子才发现，我还要修改 IDesignTimeDbContextFactory<> 的实现类才算真正的Code-First
修改 IDesignTimeDbContextFactory<> 的实现类
在ABP的EntityFrameworkCore项目下的EntityFrameworkCore文件夹里面，有一个DbContextFactory类，对其进行修改后的代码如下： /* This class is needed to run "dotnet ef ..." commands from command line on development. Not used anywhere else */
public class MyABP7NET6DbContextFactory : IDesignTimeDbContextFactory<MyABP7NET6DbContext>
{
private static readonly IServiceProvider _serviceProvider;
static MyABP7NET6DbContextFactory()
{
var services = new ServiceCollection();
services.AddShardingDbContext<MyABP7NET6DbContext>()
.UseRouteConfig(op =>
{
op.AddShardingTableRoute<TodoTableRoute>();
})
.UseConfig((sp, op) =>
{
op.UseShardingQuery((conStr, builder) =>
{
builder.UseSqlServer(conStr);
});
op.UseShardingTransaction((connection, builder) =>
{
builder.UseSqlServer(connection);
});
op.UseShardingMigrationConfigure(builder =>
{
builder.ReplaceService<IMigrationsSqlGenerator, ShardingSqlServerMigrationsSqlGenerator>();
});
op.AddDefaultDataSource(
Guid.NewGuid().ToString("n"),
"Server=localhost; Database=MyABP7NET6Db; Trusted_Connection=True;TrustServerCertificate=True;"
);
})
.AddShardingCore();
_serviceProvider = services.BuildServiceProvider();
}
public MyABP7NET6DbContext CreateDbContext(string[] args)
{
return _serviceProvider.GetService<MyABP7NET6DbContext>();
}
}
这段代码就是在执行update-database时，为efcore提供DbContext的，并且正如ABP的注释所说，它不会用在此外任何地方。
对迁移sql生成进行重写
public class ShardingSqlServerMigrationsSqlGenerator : SqlServerMigrationsSqlGenerator
{
private readonly IShardingRuntimeContext _shardingRuntimeContext;
public ShardingSqlServerMigrationsSqlGenerator(IShardingRuntimeContext shardingRuntimeContext, MigrationsSqlGeneratorDependencies dependencies, ICommandBatchPreparer commandBatchPreparer) : base(dependencies, commandBatchPreparer)
{
_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);
}
}
// 添加分片配置
services.AddShardingDbContext<MyABP7NET6DbContext>()
.UseRouteConfig(op =>
{
op.AddShardingTableRoute<TodoTableRoute>();
}).UseConfig((sp, op) =>
{
//...原来的配置不变，添加下面的
//如果需要迁移code-first必须要自行处理
op.UseShardingMigrationConfigure(b =>
{
b.ReplaceService<IMigrationsSqlGenerator, ShardingSqlServerMigrationsSqlGenerator>();
});
}).AddShardingCore();
创建主从表实体
接下来就可以修改实体进行测试了 public class ToDoItem : AggregateRoot<long>, ISoftDelete, IMayHaveTenant, ICreationAudited, IShardingKeyIsCreationTime
{
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public override long Id { get => base.Id; set => base.Id = value; }
public bool IsDeleted { get; set; }
public int? TenantId { get; set; }
public long? CreatorUserId { get; set; }
public DateTime CreationTime { get; set; }
public long? ToDoMasterId { get; set; }
[ForeignKey(nameof(ToDoMasterId))]
public ToDoMaster ToDoMaster { get; set; }
public string Text { get; set; }
}
//标识对应的分表对像的分表字段是id时，自动创建guid
public interface IShardingKeyIsGuId
{
}
//标识对应的分表对象的分表字段是创建时间时，自动创建时间
public interface IShardingKeyIsCreationTime
{
}
public class ToDoMaster : FullAuditedEntity<long>, IMayHaveTenant
{
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public override long Id { get => base.Id; set => base.Id = value; }
public int? TenantId { get; set; }
public ICollection<ToDoItem> ToDoItems { get; set; }
public string Name { get; set; }
}
然后再修改DbContext、分表路由、分表配置，这三部分代码就不贴出来了，因为和第一次添加实体的代码一样。
修改完以上代码以后，就可以执行迁移命令：
PM> Add-Migration Add_ToDoMaster
PM> update-Database
这一次执行完update-Database以后，就能看到数据库的变化了，而不用等到启动程序时才新建表：
新增数据
var msg = "测试数据";
var currentTime = DateTime.Now;
var master = await _dbContext.Set<ToDoMaster>().AddAsync(new ToDoMaster()
{
Name = msg + "Master",
});
var masterId = master.Entity.Id;
var insertList = new List<ToDoItem>()
{
new ToDoItem(){ ToDoMasterId = masterId, Text = msg+"1" },
new ToDoItem(){ ToDoMasterId = masterId, Text = msg+"2" },
new ToDoItem(){ ToDoMasterId = masterId, Text = msg+"3" },
new ToDoItem(){ ToDoMasterId = masterId, Text = msg+"4" },
new ToDoItem(){ ToDoMasterId = masterId, Text = msg+"5" }
};
await _dbContext.Set<ToDoItem>().AddRangeAsync(insertList);
await _dbContext.SaveChangesAsync();
从表数据截图
主表数据截图
左连接
按时间过滤后，只会查询对应时间的分表；如果查询条件不加时间，会查询所有分表。
var sql = from item in _dbContext.Set<ToDoItem>().Where(x => x.CreationTime >= startTime && x.CreationTime < endTime)
select new
{
item.ToDoMaster.Name,
item.Text,
item.CreationTime
};
对应的sql
SELECT [t0].[Name], [t].[Text], [t].[CreationTime]
FROM [ToDoItems_202212] AS [t]
LEFT JOIN (
SELECT [t1].[Id], [t1].[Name]
FROM [ToDoMasters_202212] AS [t1]
WHERE (0 = CAST(1 AS bit) OR [t1].[IsDeleted] = CAST(0 AS bit)) AND (0 = CAST(1 AS bit) OR ([t1].[TenantId] IS NULL))
) AS [t0] ON [t].[ToDoMasterId] = [t0].[Id]
WHERE (0 = CAST(1 AS bit) OR [t].[IsDeleted] = CAST(0 AS bit)) AND (0 = CAST(1 AS bit) OR ([t].[TenantId] IS NULL)) AND [t].[CreationTime] >= '2022-12-01T00:00:00' AND [t].[CreationTime] < '2023-01-01T00:00:00'
分片对象暂不支持导航属性
var list = await _dbContext.Set<ToDoItem>()
.Include(x => x.ToDoMaster)
.Where(x => x.CreationTime >= startTime && x.CreationTime < endTime)
.Where(x => x.ToDoMaster.CreationTime >= startTime && x.ToDoMaster.CreationTime < endTime)
.Select(x => new
{
x.ToDoMaster.Name,
x.Text,
x.CreationTime,
})
.ToListAsync();
对应的sql
可以看到只有ToDoItem的分表成功路由到了，而它的主表虽然写了时间查询条件，但依然用所有的分表做连接操作了。
所以，目前做查询尽量不要用到导航属性。
更详细的查询说明在官方文档也有。
