Code First

2021-11-3 大约 3 分钟

# 介绍

用过efcore的小伙伴肯定都知道code first是很久之前就一直在主打的一种编程方式,他可以让我们直接上手编程,而不需要去构建数据库,以一种先写代码后自动创建数据库的模式让开发者从数据库设计中脱离出来，更多的是快速进入到开发的一种状态。

本项目demo示例 (opens new window)

# 安装

首先无论你是aspnetcore还是普通的控制台程序，我们这边需要做的是新建一个控制台程序，命名为Project.Migrations,如果您是分层架构，那安装Microsoft.EntityFrameworkCore.Tools请选择对应的efcore对应版本

dotnet add package Microsoft.EntityFrameworkCore.Tools

# 开始

首先efcore分为以下几步首先我们创建几个对象分别是分表和部分表的对象，分表对象又分取模和时间分表

# 创建对象

如果是项目引入efcore层可以忽略


    public class NoShardingTable
    {
        public string Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
    }
    
    public class ShardingWithDateTime
    {
        public string Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public DateTime CreateTime { get; set; }
    }
    
    public class ShardingWithMod
    {
        public string Id { get; set; }
        public string Name { get; set; }
        public int Age { get; set; }
        public string TextStr { get; set; }
        public string TextStr1 { get; set; }
        public string TextStr2 { get; set; }
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

# 创建数据库关系

如果是项目引入efcore层可以忽略


    public class NoShardingTableMap : IEntityTypeConfiguration<NoShardingTable>
    {
        public void Configure(EntityTypeBuilder<NoShardingTable> builder)
        {
            builder.HasKey(o => o.Id);
            builder.Property(o => o.Id).IsRequired().IsUnicode(false).HasMaxLength(128);
            builder.Property(o => o.Name).HasMaxLength(128);
            builder.ToTable(nameof(NoShardingTable));
        }
    }
    public class ShardingWithDateTimeMap : IEntityTypeConfiguration<ShardingWithDateTime>
    {
        public void Configure(EntityTypeBuilder<ShardingWithDateTime> builder)
        {
            builder.HasKey(o => o.Id);
            builder.Property(o => o.Id).IsRequired().IsUnicode(false).HasMaxLength(128);
            builder.Property(o => o.Name).HasMaxLength(128);
            builder.ToTable(nameof(ShardingWithDateTime));
        }
    }
    public class ShardingWithModMap : IEntityTypeConfiguration<ShardingWithMod>
    {
        public void Configure(EntityTypeBuilder<ShardingWithMod> builder)
        {
            builder.HasKey(o => o.Id);
            builder.Property(o => o.Id).IsRequired().IsUnicode(false).HasMaxLength(128);
            builder.Property(o => o.Name).HasMaxLength(128);
            builder.Property(o => o.Name).HasMaxLength(128);
            builder.Property(o => o.TextStr).IsRequired().HasMaxLength(128).HasDefaultValue("");
            builder.Property(o => o.TextStr1).IsRequired().HasMaxLength(128).HasDefaultValue("123");
            builder.Property(o => o.TextStr2).IsRequired().HasMaxLength(128).HasDefaultValue("123");
            builder.ToTable(nameof(ShardingWithMod));
        }
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

# 创建dbcontext

创建dbcontext并且建立关系


    public class DefaultShardingTableDbContext:AbstractShardingDbContext, IShardingTableDbContext
    {
        public DefaultShardingTableDbContext(DbContextOptions options) : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.ApplyConfiguration(new NoShardingTableMap());
            modelBuilder.ApplyConfiguration(new ShardingWithModMap());
            modelBuilder.ApplyConfiguration(new ShardingWithDateTimeMap());
        }

        public IRouteTail RouteTail { get; set; }
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# 分表路由

如果引用的层里有对应的可以忽略


    public class ShardingWithModVirtualTableRoute : AbstractSimpleShardingModKeyStringVirtualTableRoute<ShardingWithMod>
    {
        public ShardingWithModVirtualTableRoute() : base(2, 3)
        {
        }

        public override void Configure(EntityMetadataTableBuilder<ShardingWithMod> builder)
        {
            builder.ShardingProperty(o => o.Id);
        }
    }
    public class ShardingWithDateTimeVirtualTableRoute : AbstractSimpleShardingMonthKeyDateTimeVirtualTableRoute<ShardingWithDateTime>
    {
        public override DateTime GetBeginTime()
        {
            return new DateTime(2021, 9, 1);
        }

        public override void Configure(EntityMetadataTableBuilder<ShardingWithDateTime> builder)
        {
            builder.ShardingProperty(o => o.CreateTime);
        }
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

# SqlGenerator

我们需要对迁移sql生成进行重写,假如我们是SqlServer,MySql亦是如此

Oracle比较特殊可以看源码Sample.OracleIssue


    public class ShardingSqlServerMigrationsSqlGenerator: SqlServerMigrationsSqlGenerator where TShardingDbContext:DbContext,IShardingDbContext
    {
        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);
        }
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

 services.AddShardingDbContext<DefaultShardingDbContext>()
                .UseRouteConfig(o =>
                {
                    //...
                }).UseConfig(o =>
                {
                    //注意添加这个替换掉默认的
                   
                    o.UseShardingMigrationConfigure(b =>
                    {
                        b.ReplaceService<IMigrationsSqlGenerator, ShardingSqlServerMigrationsSqlGenerator>();
                    });
                })
                .AddShardingCore();



        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }

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

            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
        }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

# 迁移初始化命令

PM> Add-Migration InitSharding

如果控制台出现迁移文件就说明迁移成功

# 更新到数据库

PM> update-Database

# 生产环境

PM> Script-Migration

如果是生产环境更多的是通过生成脚本来进行手动处理执行

具体可以参考Sample.Migrations (opens new window)

事务中查询高性能分页