Efcore shardingcore in c# presents a “perfect” sub table

Time:2022-7-27

If you have the following pain points in the bisection table, you might as well try my open source frameworksharding-core , whether you need to use the sub table component without perception, whether you need to support ABP, whether you need to support custom sub table rules, whether you need to support custom sub table keys, whether you need to support a specific efcore version, whether you want the framework to be clean without any three-party framework, whether you need to support read-write separation, whether you need to dynamically add tables, whether you need to support join, group and other operations, and whether you need to support tracking features, Whether you want to expand the sub table function without modifying the original code. If several conditions are combined arbitrarily and you can’t find an alternative framework in the market, you can try this framework. For details on how to use the code, please refer togithubDownload the code. If sqlserver is installed locally, run the unit test or sample directly Sqlserver program will automatically create a new database and a new database table structure locally. At present, the initialization data is the user information and the monthly salary information table corresponding to the user. The user table is modeled by the user ID, and the user monthly salary table is divided into monthly tables.

First of all, you need to know a version number of this framework, otherwise your use will be phased. At present, the framework is divided into three versions, namely2.x,3.x,5.x3Versions, corresponding toefcore 2.x efcore 3.x efcore 5.x, someone wants to ask why it doesn’t support 6.x. (I just completed the development and reconstruction of this framework last week, and I haven’t started efcore 6.x yet, but I will support it in the near future (visual inspection within 1-2 weeks)).

At present, there are many solutions for dividing tables and databases in the efcore ecosystem, but at present, they all have their shortcomings, such as the need to manually set the suffix of the table, the need to replace a large number of existing codes, and the lack of support for transactions. Therefore, under this premise, I opened source beforesharding-core The sub table component, which I personally think is a “perfect” sub table component at present, is implemented with reference to sharding JDBC, but it is more powerful than sharding JDBC (because of the expression of c#). First of all, let’s look at the shortcomings of some sub meter components on the market, and let’s solve the pain points for their shortcomings.

Efcore support

Efcore version Support or not
2.x support
3.x support
5.x support
6.x Upcoming support

Database support

database Whether the theory supports it
SqlServer support
MySql support
PostgreSql support
SQLite support
Oracle support
other Support (as long as efcore supports)

Theoretically, sharding core will support any database supported by the corresponding version of efcore.

How to get started

1. Create a database object to inherit ishardingtable and label the [shardingtablekey] feature on the corresponding sub table field

/// <summary>
    ///User table
    /// </summary>
    public class SysUserMod : IShardingTable
    {
        /// <summary>
        ///User ID is used for sub table
        /// </summary>
        [ShardingTableKey(TailPrefix = "_")]
        public string Id { get; set; }
        /// <summary>
        ///User name
        /// </summary>
        public string Name { get; set; }
        /// <summary>
        ///User name
        /// </summary>
        public int Age { get; set; }
    }

2. Create the corresponding entity table and configure the recommended fluent API


   public class SysTestMap:IEntityTypeConfiguration<SysTest>
    {
        public void Configure(EntityTypeBuilder<SysTest> builder)
        {
            builder.HasKey(o => o.Id);
            builder.Property(o => o.Id).IsRequired().HasMaxLength(128);
            builder.Property(o => o.UserId).IsRequired().HasMaxLength(128);
            builder.ToTable(nameof(SysTest));
        }
    }

3. Create the corresponding sub table rule and take the module sub table. Parameter 2 represents the suffix, 2 digits are 00-99, up to 100 tables, and 3 represents module 3== key hashcode() %3


    public class SysUserModVirtualTableRoute : AbstractSimpleShardingModKeyStringVirtualTableRoute<SysUserMod>
    {
        public SysUserModVirtualTableRoute() : base(2,3)
        {
        }
    }

4. The step of creating the corresponding dbcontext is the same as that of ordinary dbcontext except that it inherits ishardingtabledbcontext


    public class DefaultTableDbContext: DbContext,IShardingTableDbContext
    {
        public DefaultTableDbContext(DbContextOptions<DefaultTableDbContext> options) :base(options)
        {
            
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.ApplyConfiguration(new SysUserModMap());
        }

        public IRouteTail RouteTail { get; set; }
    }

5. Add sub table dbcontext


    public class DefaultShardingDbContext:AbstractShardingDbContext<DefaultTableDbContext>
    {
        public DefaultShardingDbContext(DbContextOptions<DefaultShardingDbContext> options) : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.ApplyConfiguration(new SysUserModMap());
        }

        public override Type ShardingDbContextType => this.GetType();
    }

6. Add configuration

public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllers();
   //The original dbcontext can be used or not. If the original dbcontext is still in use, continue
            //services.AddDbContext<DefaultTableDbContext>(o => o.UseSqlServer("Data Source=localhost;Initial Catalog=ShardingCoreDBxx3;Integrated Security=True"));
            services.AddShardingDbContext<DefaultShardingDbContext, DefaultTableDbContext>(
                o => o.UseSqlServer("Data Source=localhost;Initial Catalog=ShardingCoreDBxx2;Integrated Security=True;")
                , op =>
                 {
                     op.EnsureCreatedWithOutShardingTable = true;
                     op.CreateShardingTableOnStart = true;
                     op.UseShardingOptionsBuilder(
                         (connection, builder) = > builder.usesqlserver (connection).Useloggerfactory (eflogger), // use dbconnection to create dbcontext support transactions
                         (conStr,builder) => builder.UseSqlServer(conStr).UseLoggerFactory(efLogger));// Create dbcontext with link string
                     op.AddShardingTableRoute<SysUserModVirtualTableRoute>();
                 });
        }
  
  
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
   ...
   //Add startup item
            app.UseShardingCore();
   ...
        }
  
  public static class ShardingCoreExtension{

   public static IApplicationBuilder UseShardingCore(this IApplicationBuilder app)
   {
    var shardingBootstrapper = app.ApplicationServices.GetRequiredService<IShardingBootstrapper>();
    shardingBootstrapper.Start();
    return app;
   }
  }

7. Controller use


        private readonly DefaultShardingDbContext _defaultTableDbContext;

        public ValuesController(DefaultShardingDbContext defaultTableDbContext)
        {
            _defaultTableDbContext = defaultTableDbContext;
        }

        [HttpGet]
        public async Task<IActionResult> Get()
        {
            var resultx11231 = await _defaultTableDbContext.Set<SysUserMod>().Where(o => o.Age == 198198).Select(o=>o.Id).ContainsAsync("1981");
            var resultx1121 = await _defaultTableDbContext.Set<SysUserMod>().Where(o => o.Id == "198").SumAsync(o=>o.Age);
            var resultx111 = await _defaultTableDbContext.Set<SysUserMod>().FirstOrDefaultAsync(o => o.Id == "198");
            var resultx2 = await _defaultTableDbContext.Set<SysUserMod>().CountAsync(o => o.Age<=10);
            var resultx = await _defaultTableDbContext.Set<SysUserMod>().Where(o => o.Id == "198").FirstOrDefaultAsync();
            var resultx33 = await _defaultTableDbContext.Set<SysUserMod>().Where(o => o.Id == "198").Select(o=>o.Id).FirstOrDefaultAsync();
            var resulxxt = await _defaultTableDbContext.Set<SysUserMod>().Where(o => o.Id == "198").ToListAsync();
            var result = await _defaultTableDbContext.Set<SysUserMod>().ToListAsync();

            var sysUserMod98 = result.FirstOrDefault(o => o.Id == "98");
            _defaultTableDbContext.Attach(sysUserMod98);
            sysUserMod98.Name = "name_update"+new Random().Next(1,99)+"_98";
            await _defaultTableDbContext.SaveChangesAsync();
            return Ok(result);
        }

User defined table splitting key, user-defined table splitting rule

At present, some frameworks on the market either have restrictions on the sub table fields, such as only supporting datetime type or int, or have restrictions on the sub table rules: only supporting by day, by month, module… Etc., but based on the fact that the sub table rules and sub table fields are business rules, this framework follows to define them by the business system itself, maximize the applicability of the sub table database, and basically meet all the sub table rules, Sharding core currently provides some common table splitting rules by default, which can be quickly integrated.

Default route

Abstract Routing rules tail Indexes
AbstractSimpleShardingModKeyIntVirtualTableRoute Take mold 0,1,2… =
AbstractSimpleShardingModKeyStringVirtualTableRoute Take mold 0,1,2… =
AbstractSimpleShardingDayKeyDateTimeVirtualTableRoute By time yyyyMMdd >,>=,<,<=,=,contains
AbstractSimpleShardingDayKeyLongVirtualTableRoute By timestamp yyyyMMdd >,>=,<,<=,=,contains
AbstractSimpleShardingWeekKeyDateTimeVirtualTableRoute By time yyyyMMdd_dd >,>=,<,<=,=,contains
AbstractSimpleShardingWeekKeyLongVirtualTableRoute By timestamp yyyyMMdd_dd >,>=,<,<=,=,contains
AbstractSimpleShardingMonthKeyDateTimeVirtualTableRoute By time yyyyMM >,>=,<,<=,=,contains
AbstractSimpleShardingMonthKeyLongVirtualTableRoute By timestamp yyyyMM >,>=,<,<=,=,contains
AbstractSimpleShardingYearKeyDateTimeVirtualTableRoute By time yyyy >,>=,<,<=,=,contains
AbstractSimpleShardingYearKeyLongVirtualTableRoute By timestamp yyyy >,>=,<,<=,=,contains

The so-called index is to reduce the scope of the specified table and speed up the execution of the program by changing the corresponding conditional operator
If the above default table cannot meet your needs, you can also customize the table. How to divide the table can be achieved by inheriting abstractshardingoperatorvirtualtableroute<tenity, tkey> (nearly 90% of the rules can be achieved)

Dynamically add sub table information

Many sub table components do not carry dynamic sub table information by default, which makes it impossible to dynamically create many sub tables according to the business system. Sharding core provides a dynamic table creation interface by default, which can support dynamic sub table information that does not need data migration by time, by tenant, etc,
If necessary, please refer toSamples.AutoByDate.SqlServer

Support select, join, group by and other linked table aggregation functions

At present, sharding core supports select on-demand query, join sub table join table query, and group by aggregate query. Although this framework supports it, for performance reasons, this framework does not recommend using the join operator to operate, because too many table routes will lead to Cartesian product, which will lead to the growth of table sets to be queried, which will test the database connection.
The following code comes from the unit test of GitHub. Sysusermod represents the user table, sysusersalary represents the user monthly salary table, the user table is modeled by ID, and the user monthly salary table is divided by month

//Join query
var list = await (from u in _virtualDbContext.Set<SysUserMod>()
                              join salary in _virtualDbContext.Set<SysUserSalary>()
                                  on u.Id equals salary.UserId
                              select new
                              {
                                  u.Id,
                                  u.Age,
                                  Salary = salary.Salary,
                                  DateOfMonth = salary.DateOfMonth,
                                  Name = u.Name
                              }).ToListAsync();

//Group aggregation query
var ids = new[] {"200", "300"};
            var dateOfMonths = new[] {202111, 202110};
            var group = await (from u in _virtualDbContext.Set<SysUserSalary>()
                    .Where(o => ids.Contains(o.UserId) && dateOfMonths.Contains(o.DateOfMonth))
                group u by new
                {
                    UId = u.UserId
                }
                into g
                select new
                {
                    GroupUserId = g.Key.UId,
                    Count = g.Count(),
                    TotalSalary = g.Sum(o => o.Salary),
                    AvgSalary = g.Average(o => o.Salary),
                    AvgSalaryDecimal = g.Average(o => o.SalaryDecimal),
                    MinSalary = g.Min(o => o.Salary),
                    MaxSalary = g.Max(o => o.Salary)
                }).ToListAsync();

paging

Pagination, as we often say, is the difficulty and the most challenging component of the sub table
1 Let’s first look at how common table components page
First, we define a set of data, such as continuous numbers from 1 to 100, and then divide it into two tables according to parity

Table name data
table1 1,3,5,7,9…
table2 2,4,6,8,10…

Select * from table limit 2,2 theoretically result 3,4
If this query falls to table1 and table2, it will be rewritten into two SQL sentences
The first sentence select * from table1 limit 4 — > 1,3,5,7
The second sentence select * from table2 limit 4 — > 2,4,6,8
Put 8 pieces of data into memory and sort them
1,2,3,4,5,6,7,8
Get the third to fourth data results [3,4]

This is our common and simplest paging, but it only applies to small amounts of data. If the user accidentally clicks on the last page of the paging, the result will be disastrous. There is no doubt about it
So how does sharding core handle it

select * from table limit 2,2
First of all, it is the same to modify the database statements and generate the corresponding SQL
First sentence select * from table1 limit 4
The second sentence select * from table2 limit 4
Because the default datareader of ado.net is a streaming access, as long as the connection is not closed, you can always achieve the next access to memory
Create a priority queue, a queue that can have sorting function
Because of the characteristics of datareader, we perform next on sql1 and SQL2 respectively and get two arrays, one is [1,…..] A and the other is array [2…] B
After obtaining the two arrays, we only know that the first object in the header cannot know the remaining data because there is no subsequent next, but one thing we can know is that the following data is in the order specified by SQL, so it will not be larger or smaller than the current header
First put 1 and 2 into the priority queue. You can know that if ASC, array A is placed at the head of the queue, array B is placed at the end of the queue, then poll the priority queue, and next A. at this time, a becomes [3,…] and then put a into the priority queue
At this time, the priority queue is that B operates in turn before a, and then filters the paged ones. Because two objects need to be skipped, it only needs to be empty for two times. Then the pointer will point to 3 of array A and 4 of array B, and the rest only needs to get two data,
This can ensure that memory is minimized, and then paging will not become a disaster for the program.

Imperceptible use

At present, few sub table frameworks can be used without perception. A better framework does not rely on three-party frameworks when you use it. Generally, it depends on many three-party frameworks, and there are a lot of restrictions when you use it. You must use its things, and you can’t achieve the native use method of dbcontext.
Sharding core currently uses a wrap mode similar to dbcontext. A new dbcontext is used to wrap the real dbcontext. This wrapped dbcontext is called shardingdbcontext. Because shardingdbcontext itself is also integrated into dbcontext, its use method is no different from the original dbcontext. And ABP and abp.next can be supported with only a few changes

Read write separation support

At present, sharding core has supported the read-write separation of single node nodes, and will support the read-write separation of multiple nodes in the near future (1-2 days)


            services.AddShardingDbContext<ShardingDefaultDbContext, DefaultDbContext>(o => o.UseSqlServer(hostBuilderContext.Configuration.GetSection("SqlServer")["ConnectionString"])
                ,op =>
                {
                    op.EnsureCreatedWithOutShardingTable = true;
                    op.CreateShardingTableOnStart = true;
                    op.UseShardingOptionsBuilder((connection, builder) => builder.UseSqlServer(connection).UseLoggerFactory(efLogger),
                        (conStr,builder)=> builder.UseSqlServer("read db connection string").UseLoggerFactory(efLogger));
                    op.AddShardingTableRoute<SysUserModVirtualTableRoute>();
                    op.AddShardingTableRoute<SysUserSalaryVirtualTableRoute>();
                });

In the future, it is planned to support sub database, forced routing, display routing, etc

Finally, please refer to GitHub for specific usage and usage(https://github.com/xuejmnet/sharding-core)

This is the end of this article about presenting the “perfect” sub table of the efcore shardingcore. For more information about the “perfect” sub table of the shardingcore, please search the previous articles of developeppaer or continue to browse the relevant articles below. I hope you will support developeppaer in the future!

Recommended Today

JS generate guid method

JS generate guid method https://blog.csdn.net/Alive_tree/article/details/87942348 Globally unique identification(GUID) is an algorithm generatedBinaryCount Reg128 bitsNumber ofidentifier , GUID is mainly used in networks or systems with multiple nodes and computers. Ideally, any computational geometry computer cluster will not generate two identical guids, and the total number of guids is2^128In theory, it is difficult to make two […]