Abp集成ShardingCore分表

Posted on Edited on In Views: Symbols count in article: 21k Reading time ≈ 19 mins.
AspNet Boilerplate 集成ShardingCore分表

ShardingCore官方文档：ShardingCore文档 (xuejmnet.github.io)

调整EFCore-ShardingCore

以下操作步骤均以FoundationTemplate模板项目做演示，实际操作请根据项目调整

Step1：调整DbContext

将DbContext集成的基类由GCTSharedDbContext调整为GCTSharedShardingCoreDbContext

GCTSharedShardingCoreDbContext：实现ShardingCore分库分表操作

1
public class FoundationTemplateDbContext : GCTSharedShardingCoreDbContext<FoundationTemplateDbContext>{}

Step2：调整模块类

调整EntityFrameworkCoreModule模块类注册，取消默认的DbContext注册，如下

取消默认的DbContext注册，将以下代码注释

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
public override void PreInitialize()
{
    // ......
    // 注册dbcontext
    /*if (!FoundationTemplateConfigs.Database.SkipDbContextRegistration)
    {
        Configuration.Modules.AbpEfCore()
            // Configuration.Modules.L52AbpEfCore()
            .AddDbContext<FoundationTemplateDbContext>(options =>
            {
                if (options.ExistingConnection != null)
                {
                    FoundationTemplateDbContextConfigurer.Configure(
                        options.DbContextOptions,
                        options.ExistingConnection
                    );
                }
                else
                {
                    FoundationTemplateDbContextConfigurer.Configure(
                        options.DbContextOptions,
                        options.ConnectionString
                    );
                }
            });
    }*/
}

Step3：文件调整

在EntityFrameworkCore项目 –> 创建ShardingCore文件夹

1
2
--- ShardingCore
  --- Routes  # 用于存放路由配置类 （或者存放到Core层，也就是各自模块目录）

ShardingCoreExtensions

创建注册ShardingCore服务扩展方法

更多配置请参考：

https://xuejm.gitee.io/sharding-core-doc/guide-upgrade-5-6/#%E7%8E%B0%E5%9C%A8%E7%9A%84startup

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
using GCT.Foundation.ShardingCore;
using GCT.Foundation.ShardingCore.Routes;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.Extensions.DependencyInjection;
using ShardingCore;

namespace GCT.Foundation.ShardingCore
{

    public static class FoundationTemplateShardingCoreExtensions
    {

        /// <summary>
        /// 注册ShardingCore服务.
        /// 配置详情见： https://xuejm.gitee.io/sharding-core-doc/guide-upgrade-5-6/#%E7%8E%B0%E5%9C%A8%E7%9A%84startup
        /// </summary>
        /// <param name="services"></param>
        /// <param name="dataSourceName">数据源名称</param>
        /// <param name="connectionString">连接字符串</param>
        /// <returns></returns>
        public static IServiceCollection ConfigureShardingCore(this IServiceCollection services, string dataSourceName,
            string connectionString)
        {
            services.AddShardingDbContext<FoundationTemplateDbContext>()
                .UseRouteConfig((sp, op) =>
                {
                    // ========= 注册分表路由 =========
                    op.AddShardingTableRoute<OrderVirtualTableRoute>();
                    op.AddShardingTableRoute<SysUserVirtualTableRoute>();
                  
                }).UseConfig(op =>
                {
                    // 如何通过字符串查询创建DbContext
                    op.UseShardingQuery((connStr, build) =>
                    {
                        FoundationTemplateDbContextConfigurer.Configure(build, connStr);
                    });
                    // 如何通过事务创建DbContext
                    op.UseShardingTransaction((conn, build) =>
                    {
                        FoundationTemplateDbContextConfigurer.Configure(build, conn);
                    });

                    // 添加默认数据源
                    op.AddDefaultDataSource(dataSourceName, connectionString);

                    // ===如果需要迁移code-first必须要自行处理==
                    op.UseShardingMigrationConfigure(conf =>
                    {
                        conf.ReplaceService<IMigrationsSqlGenerator,
                            ShardingSqlServerMigrationsSqlGenerator<FoundationTemplateDbContext>>();
                    });
                })
                .AddShardingCore();
            return services;
        }
    }
}

MigrationsSqlGenerator

在ShardingCore目录下创建ShardingSqlServerMigrationsSqlGenerator.cs

ShardingCore - SqlServer的迁移Sql生成工具类，默认使用SqlServer

支持MySQL、Oracle等其他数据库

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
using System.Linq;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Migrations.Operations;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.EntityFrameworkCore;
using ShardingCore.Core.RuntimeContexts;
using ShardingCore.Helpers;
using ShardingCore.Sharding.Abstractions;

namespace GCT.Foundation.EntityFrameworkCore
{

    public class ShardingSqlServerMigrationsSqlGenerator<TShardingDbContext> : SqlServerMigrationsSqlGenerator where TShardingDbContext : GCTSharedShardingCoreDbContext<TShardingDbContext>
    {
        private readonly IShardingRuntimeContext _shardingRuntimeContext;

        public ShardingSqlServerMigrationsSqlGenerator(MigrationsSqlGeneratorDependencies dependencies, IRelationalAnnotationProvider migrationsAnnotations, IShardingRuntimeContext shardingRuntimeContext) : base(dependencies, migrationsAnnotations)
        {
            _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);
        }
    }
}

DbContextConfigurer

调整默认的DbContext注册类，移除DbContext限制

1
2
-- public static void Configure(DbContextOptionsBuilder builder, string connectionString)
++ public static void Configure(DbContextOptionsBuilder<FoundationTemplateDbContext> builder, string connectionString)

完整如下：

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
// FoundationTemplateDbContextConfigurer.cs
using System.Data.Common;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace GCT.Foundation.EntityFrameworkCore
{

    public static class FoundationTemplateDbContextConfigurer
    {
        public static readonly ILoggerFactory MyLoggerFactory
            = LoggerFactory.Create(builder => { builder.AddConsole(); });

        public static void Configure(DbContextOptionsBuilder builder, string connectionString)
        {
            builder.UseSqlServer(connectionString);

            ConfigureLog(builder);
        }

        public static void Configure(DbContextOptionsBuilder builder, DbConnection connection)
        {
            builder.UseSqlServer(connection);

            ConfigureLog(builder);
        }

        /// <summary>
        /// 配置日志,只在Debug模式下生效
        /// </summary>
        /// <param name="builder"></param>
        private static void ConfigureLog(DbContextOptionsBuilder builder)
        {
#if DEBUG
            builder.UseLoggerFactory(MyLoggerFactory);
#endif
        }
    }
}

DbContextFactory

调整DbContextFactory

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
using System;
using GCT.Foundation.Configuration;
using GCT.Foundation.Helpers;
using GCT.Foundation.ShardingCore;
using Microsoft.EntityFrameworkCore.Design;
using Microsoft.Extensions.DependencyInjection;

namespace GCT.Foundation.EntityFrameworkCore
{
    /* This class is needed to run "dotnet ef ..." commands from command line on development. Not used anywhere else */
    public class FoundationTemplateDbContextFactory : IDesignTimeDbContextFactory<FoundationTemplateDbContext>
    {
        private static IServiceProvider _serviceProvider;
        [Obsolete("Obsolete")]
        static FoundationTemplateDbContextFactory()
        {
            var basePath = FoundationTemplateWebContentDirectoryFinder.CalculateContentRootFolder();
            Console.WriteLine("工作目录：");
            Console.WriteLine(basePath);

            var configuration = AppConfigurations.Get(basePath, "Development");
            var connectionString = configuration.ConnectionStringsDefault();

            Console.WriteLine("迁移使用数据库连接字符串：");
            Console.WriteLine(connectionString);

            var services = new ServiceCollection();
            services.ConfigureShardingCore(AppConsts.DefaultDataSourceName, connectionString);
            _serviceProvider = services.BuildServiceProvider();
        }
      
        public FoundationTemplateDbContext CreateDbContext(string[] args)
        {
            return _serviceProvider.GetService<FoundationTemplateDbContext>();
        }
    }
}

目录结构如下：

image-20221025143100094

注册ShardingCore

Step1：Template.Web.Host

注册ShardingCore服务

Startup.cs

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
//Startup.cs

using GCT.Foundation.ShardingCore;

// ...
public IServiceProvider ConfigureServices(IServiceCollection services)
{
    // ......
  
    // health check ui
    ConfigureHealthCheckService(services);

    // ShardingCore
    services.ConfigureShardingCore(AppConsts.DefaultDataSourceName, Configuration.GetConnectionString("Default"));
}
 public void Configure(IApplicationBuilder app)
 {
     // 基本的中间件
     UseBasic(app);

     // Hangfire
     UseHangfire(app);

     // ShardingCore
     UseShardingCore(app);
   
     // ... other code
 }


/// <summary>
/// 启用ShardingCore
/// </summary>
/// <param name="app"></param>
protected virtual void UseShardingCore(IApplicationBuilder app)
{
    //启动ShardingCore创建表任务(不调用也可以使用ShardingCore)
    //不调用会导致定时任务不会开启
    app.ApplicationServices.UseAutoShardingCreate();
    //启动进行表补偿(不调用也可以使用ShardingCore) 启动创建分表
    // app.ApplicationServices.UseAutoTryCompensateTable();
}

Step2：Template.Migrator

注册ShardingCore服务

Startup.cs

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

using GCT.Foundation.ShardingCore;

public void ConfigurationServices(IServiceCollection services)
{
    var basePath = AppContext.BaseDirectory;
    var configuration = AppConfigurations.Get(basePath, "Development");
    var connectionString = configuration.ConnectionStringsDefault();

    // identity
    services.AddAppIdentity();

    // ShardingCore
    services.ConfigureShardingCore(AppConsts.DefaultDataSourceName, connectionString);

    // type finder
    services.Add52AbpTypeFinder((options) =>
    {
        // skip find
        options.AddSkip<FoundationDbContext>();
    });
}

分表使用案例

官方文档案例：初始化 | ShardingCore文档 (gitee.io)

创建一个ShardingCore的Demo演示功能，首先在Template.Core层创建ShardingCoreManagement目录

先拟定一个场景目前有用户表SysUser和订单表Order，再添加一个Setting配置表

  • SysUser用户表按用户Age进行取模分表

  • Order订单表按时间月进行分表

  • Setting配置表不进行分表

创建实体

Order.cs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
using System;
using Abp.Domain.Entities;

namespace GCT.Foundation.ShardingCoreManagement
{
    /// <summary>
    /// 订单表
    /// </summary>
    public class Order : Entity<int>
    {
        public int Payer { get; set; }

        public long Money { get; set; }

        public string Area { get; set; }

        public OrderStatusEnum OrderStatus { get; set; }

        public DateTime CreationTime { get; set; }
    }
}

OrderStatusEnum.cs

1
2
3
4
5
6
7
8
9
10
11
12
13
namespace GCT.Foundation.ShardingCoreManagement
{
    /// <summary>
    /// 订单状态
    /// </summary>
    public enum OrderStatusEnum
    {
        NoPay = 1,
        Paying = 2,
        Payed = 3,
        PayFail = 4
    }
}

Setting.cs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
using Abp.Domain.Entities;

namespace GCT.Foundation.ShardingCoreManagement
{
    /// <summary>
    /// 设置表
    /// </summary>
    public class Setting : Entity<int>
    {
        public string Code { get; set; }

        public string Name { get; set; }
    }
}

SysUser.cs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
using Abp.Domain.Entities;

namespace GCT.Foundation.ShardingCoreManagement
{
    /// <summary>
    /// 用户表
    /// </summary>
    public class SysUser : Entity<int>
    {
        public int Age { get; set; }

        public string Name { get; set; }

        public string SettingCode { get; set; }

        public string Area { get; set; }
    }
}

创建领域服务

在ShardingCoreManagement目录下创建DomainService目录用于存放领域服务类

OrderManager

1
2
3
4
5
6
7
8
namespace GCT.Foundation.ShardingCoreManagement.DomainService
{

    public interface IOrderManager : I52AbpDomainService<Order, int>
    {

    }
}
1
2
3
4
5
6
7
8
9
10
11
12
using System;

namespace GCT.Foundation.ShardingCoreManagement.DomainService
{

    public class OrderManager : AbpDomainService<Order, int>, IOrderManager
    {
        public OrderManager(IServiceProvider serviceProvider) : base(serviceProvider)
        {
        }
    }
}

SysUserManager

1
2
3
4
5
6
7
namespace GCT.Foundation.ShardingCoreManagement.DomainService
{

    public interface ISysUserManager : I52AbpDomainService<SysUser, int>
    {
    }
}
1
2
3
4
5
6
7
8
9
10
11
using System;

namespace GCT.Foundation.ShardingCoreManagement.DomainService
{
    public class SysUserManager : AbpDomainService<SysUser, int>, ISysUserManager
    {
        public SysUserManager(IServiceProvider serviceProvider) : base(serviceProvider)
        {
        }
    }
}

SettingManager

1
2
3
4
5
6
7
8
namespace GCT.Foundation.ShardingCoreManagement.DomainService
{

    public interface ISettingManager : I52AbpDomainService<Setting, int>
    {

    }
}
1
2
3
4
5
6
7
8
9
10
11
12
using System;

namespace GCT.Foundation.ShardingCoreManagement.DomainService
{

    public class SettingManager : AbpDomainService<Setting, int>, ISettingManager
    {
        public SettingManager(IServiceProvider serviceProvider) : base(serviceProvider)
        {
        }
    }
}

注册实体

和以往一样，在DbContext类中加入实体注册

在Template.EntityFrameworkCore的FoundationTemplateDbContext中注册实体

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
using GCT.Foundation.Examples.Entity;
using GCT.Foundation.ShardingCoreManagement;
using Microsoft.EntityFrameworkCore;

namespace GCT.Foundation.EntityFrameworkCore
{
    public class FoundationTemplateDbContext : GCTSharedDbContextShardingCore<FoundationTemplateDbContext>
    {
        public FoundationTemplateDbContext(DbContextOptions<FoundationTemplateDbContext> options) : base(options)
        {
        }

        public override void ModelCreating(ModelBuilder modelBuilder)
        {
            base.ModelCreating(modelBuilder);
            
            modelBuilder.ConfigureGCTShared();

            modelBuilder.ConfigureFoundationTemplate();
            // 可选
            modelBuilder.UseCollation("Chinese_PRC_CI_AS");
        }
        public DbSet<BigData> BigData { get; set; }
        
        public DbSet<Commodity> Commodity { get; set; }

        // 注册新建的实体
        public DbSet<Order> Order { get; set; }
        public DbSet<Setting> Setting { get; set; }
        public DbSet<SysUser> SysUser { get; set; }
    }
}

创建虚拟路由

路由官方文档：默认路由 | ShardingCore文档 (gitee.io)

！！！注意：路由配置类按照约定统一放在Template.EntityFrameworkCore层ShardingCore -> Routes目录下，

image-20221025150400595

Order订单表路由：OrderVirtualTableRoute.cs

这里实现的基类AbstractSimpleShardingMonthKeyDateTimeVirtualTableRoute按时间分表

该路由为简单的按月分表路由,支持分表字段是DateTime,分表后的后缀为yyyyMM

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
using System;
using GCT.Foundation.ShardingCoreManagement;
using ShardingCore.Core.EntityMetadatas;
using ShardingCore.VirtualRoutes.Months;

namespace GCT.Foundation.ShardingCore.Routes;

public class OrderVirtualTableRoute : AbstractSimpleShardingMonthKeyDateTimeVirtualTableRoute<Order>
{
    public override void Configure(EntityMetadataTableBuilder<Order> builder)
    {
        builder.ShardingProperty(o => o.CreationTime);
    }

    public override bool AutoCreateTableByTime()
    {
        return true;
    }

    public override DateTime GetBeginTime()
    {
        return new DateTime(2021, 1, 1);
    }
}

SysUser表路由：SysUserVirtualTableRoute.cs

继承的是Int类型取模分表路由：AbstractSimpleShardingModKeyIntVirtualTableRoute

该路由为简单的取模hash路由，分表字段是int类型，接受3个参数，第一个参数表示后缀的位数,第二位表示取模的基数，第三位是取模后缀不足的左补字符。

对应的，如果取模分片字段为String类型，AbstractSimpleShardingModKeyStringVirtualTableRoute

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
using GCT.Foundation.ShardingCoreManagement;
using ShardingCore.Core.EntityMetadatas;
using ShardingCore.VirtualRoutes.Mods;

namespace GCT.Foundation.ShardingCore.Routes
{

    public class SysUserVirtualTableRoute : AbstractSimpleShardingModKeyIntVirtualTableRoute<SysUser>
    {
        public SysUserVirtualTableRoute() : base(2, 3)
        {
        }

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

注册分片路由

在FoundationTemplateShardingCoreExtensions.ConfigureShardingCore中加入表分片路由配置，如下：

FoundationTemplateShardingCoreExtensions.cs

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
using GCT.Foundation.EntityFrameworkCore;
using GCT.Foundation.ShardingCore.Routes;
using Microsoft.EntityFrameworkCore.Migrations;
using Microsoft.Extensions.DependencyInjection;
using ShardingCore;

namespace GCT.Foundation.ShardingCore
{

    public static class FoundationTemplateShardingCoreExtensions
    {
        /// <summary>
        /// 注册ShardingCore服务
        /// </summary>
        public static IServiceCollection ConfigureShardingCore(this IServiceCollection services, string dataSourceName,
            string connectionString)
        {

            services.AddShardingDbContext<FoundationTemplateDbContext>()
                .UseRouteConfig((sp, op) =>
                {
                   // 在这添加注册分表路由
                    op.AddShardingTableRoute<OrderVirtualTableRoute>();
                    op.AddShardingTableRoute<SysUserVirtualTableRoute>();
                })
              // ..... other code
            return services;
        }
    }
}

添加种子数据

在Seed/Host目录下创建DefaultOrderBuilder.cs，如下：

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
using System.Collections.Generic;
using System;
using GCT.Foundation.EntityFrameworkCore;
using GCT.Foundation.ShardingCoreManagement;

namespace GCT.Foundation.Seed.Host
{

    public class DefaultOrderBuilder
    {
        private readonly FoundationTemplateDbContext _context;

        public DefaultOrderBuilder(FoundationTemplateDbContext context)
        {
            _context = context;
        }

        public void Create()
        {
            var settings = new List<Setting>(3)
            {
                new()
                {
                    Code = "Admin",
                    Name = "AdminName"
                },
                new()
                {
                    Code = "User",
                    Name = "UserName"
                },
                new()
                {
                    Code = "SuperAdmin",
                    Name = "SuperAdminName"
                }
            };
            var users = new List<SysUser>(10);
            for (var i = 0; i < 10; i++)
            {
                var uer = new SysUser()
                {
                    Age = i,
                    Name = $"MyName{i}",
                    SettingCode = settings[i % 3].Code
                };
                users.Add(uer);
            }
            var orders = new List<Order>(300);
            var begin = new DateTime(2021, 1, 1, 3, 3, 3);
            for (var i = 0; i < 300; i++)
            {

                var order = new Order()
                {
                    Payer = i % 10,
                    Money = 100 + new Random().Next(100, 3000),
                    OrderStatus = (OrderStatusEnum)(i % 4 + 1),
                    CreationTime = begin.AddDays(i)
                };
                orders.Add(order);
            }
            _context.AddRange(settings);
            _context.AddRange(users);
            _context.AddRange(orders);
            _context.SaveChanges();
        }
    }
}

在InitialHostDbBuilder.cs添加调用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
using GCT.Foundation.EntityFrameworkCore;

namespace GCT.Foundation.Seed.Host
{

    public class InitialHostDbBuilder
    {
        private readonly FoundationTemplateDbContext _context;

        public InitialHostDbBuilder(FoundationTemplateDbContext context)
        {
            _context = context;
        }

        public void Create()
        {
            // 创建测试种子数据
            new DefaultOrderBuilder(_context).Create();
            _context.SaveChanges();
        }
    }
}

创建迁移

按正常步骤执行迁移

1
2
Add-Migration Use-Sharding
Update-Database

分表后如下：

image-20221025155658434

img

CRUD

增删改查除了查询稍微在分表+排序的情况下需要注意其实其他操作和efcore基本上一致

查询示例

在Template.Application层创建ShardingCoreTestAppService.cs

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
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Abp.Domain.Repositories;
using GCT.Foundation.ShardingCoreManagement;
using GCT.Foundation.ShardingCoreManagement.DomainService;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Newtonsoft.Json;

namespace GCT.Foundation.ShardingCoreTest
{
    public class ShardingCoreTestAppService : FoundationTemplateAppServiceBase
    {
        private readonly OrderManager _orderManager;
        private readonly SysUserManager _sysUserManager;
        private readonly SettingManager _settingManager;

        public ShardingCoreTestAppService(OrderManager orderManager,
            SysUserManager sysUserManager,
            SettingManager settingManager)
        {
            _orderManager = orderManager;
            _sysUserManager = sysUserManager;
            _settingManager = settingManager;
        }
      
        /*private readonly IRepository<Order, int> _orderRepository;
        public ShardingCoreTestAppService(IRepository<Order, int> orderRepository)
        {
            _orderRepository = orderRepository;
        }*/
    }
}
单对象查询

官方文档单对象查询 | ShardingCore文档 (gitee.io)

和efcore原生查询没有任何区别,支持未分表下的所有操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/// <summary>
/// 单对象简单查询
/// </summary>
/// <returns></returns>
public async Task<Order> GetOrder()
{
    var order = await _orderManager.QueryAsNoTracking.FirstOrDefaultAsync(o => o.Id == 3);

    return order;
}

/// <summary>
/// 单对象简单查询
/// </summary>
/// <returns></returns>
public async Task<List<Order>> GetOrderList()
{

    var dateTime = new DateTime(2021, 3, 5);
    var orders = await _orderManager.QueryAsNoTracking.Where(x => x.CreationTime >= dateTime).ToListAsync();

    return orders;
}

image-20221025161642236

连表查询

默认不推荐include，分表对象+分表、分表对象+分表对象都是在ShardingCore内被支持的，支持的形式为join, 默认推荐join组合而不是include

分表join不分表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
/// <summary>
/// 分表Join不分表
/// </summary>
/// <returns></returns>
[HttpGet]
public async Task<string> QueryJoin()
{
    var query = from user in _sysUserManager.QueryAsNoTracking.Where(o => o.Id == 1 || o.Id == 6)
                join setting in _settingManager.QueryAsNoTracking on user.SettingCode equals setting.Code
        select new
        {
            user.Id,
            user.Name,
            user.Area,
            user.SettingCode,
            SettingName = setting.Name,
        };
    await query.ToListAsync();
    return JsonConvert.SerializeObject(query);
}

image-20221025162314303

分表join分表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
/// <summary>
/// 分表join分表
/// </summary>
/// <returns></returns>
[HttpGet]
public async Task<string> QueryJoin2()
{
    var begin = new DateTime(2021, 3, 2);
    var end = new DateTime(2021, 4, 3);
    var sql1 = from user in  _sysUserManager.QueryAsNoTracking.Where(o => o.Id == 1 || o.Id == 6)
        join order in _orderManager.QueryAsNoTracking.Where(o => o.CreationTime >= begin && o.CreationTime <= end)
            on user.Id equals order.Payer
        select new
        {
            user.Id,
            user.Name,
            user.Area,
            user.SettingCode,
            OrderId = order.Id,
            order.Payer,
            order.CreationTime
        };
    return JsonConvert.SerializeObject(await sql1.ToListAsync());
}

image-20221025162513321

image-20221025162715517

修改示例

自动追踪修改

追踪情况下sharding-core依然可以对结果进行修改，修改的字段是被修改过后的字段

1
2
3
4
5
6
public async Task UpdateUser()
{
    var sysUser = await _sysUserManager.Query.FirstOrDefaultAsync(x => x.Id == 1);
    sysUser.Name = "new Name";
    await _sysUserManager.Update(sysUser);
}
非自动追踪修改

非追踪情况下sharding-croe也支持查询修改，但是修改的字段是全字段

1
2
3
4
5
6
public async Task UpdateUserAsNoTracking()
{
    var sysUser = await _sysUserManager.QueryAsNoTracking.FirstOrDefaultAsync(x => x.Id == 1);
    sysUser.Name = "new Name";
    await _sysUserManager.Update(sysUser);
}

删除示例

下面操作会将所有分表中id==7的数据删除

1
2
3
4
public async Task DeleteUser()
{
    await _sysUserManager.Delete(x => x.Id == 7);
}

注意事项

sharding table key:Id is not ValueGenerated.Never

分片是获取插入数据的分片键，然后通过路由插入到具体的数据库，分片键不可以为自增id，必须是没有生成策略的

使用外键报错

建议在使用外键的时候如果是主表也是分表的情况下最好的情况就是取消外键

ShardingCore 多租户

当前分库分表情况：

  • 非多租户模式，全部通顺，功能正常
  • 多租户模式，不支持，需要修改实现完成功能

ShardingCore的设计时如何支持多租户模式

  • IVirtualDataSourceManager ,虚拟数据源管理，存储数据库连接字符串配置
    • 添加数据源配置
      • DynamicShardingHelper.DynamicAppendVirtualDataSourceConfig(new SqlShardingConfiguration(shardingTenantOptions));
    • 切换数据源配置
      • virtualDataSourceManager.CreateScope(数据源标识)

如何结合Abp工作单元

  • 加载查询当前数据库中的租户数据配置
  • 创建工作单元时，指定租户id（数据源标识）
  • 当前工作单元切换租户时，指定租户id（数据源标识）
Related Posts