How to do. Net ORM sub table? How to do. Net ORM branch library?

Time:2021-4-17

Theoretical knowledge

Sub table – on the surface, it means to divide a table into n small tables, each of which is a complete table. After the sub table, the data is stored in the sub table, the master table is just a shell, and the access data occurs in the sub table one by one. After splitting tables, the concurrency of single table is improved, and the disk I / O performance is also improved. Why is the concurrency capability improved? It takes less time to search once. If there is high concurrency, the general table can divide the concurrency pressure into different small tables according to different queries.

Sub database – the data originally stored in one database is stored in blocks in multiple databases, and the data originally stored in one table is stored in blocks in multiple tables. The amount of data in the database is not necessarily controllable. In the case of not dividing tables and databases, with the development of time and business, there will be more and more tables in the database, and the amount of data in the table will also be larger and larger. Correspondingly, the cost of data operation, addition, deletion and modification will also be larger and larger. In addition, the resources of a server (CPU, disk, memory, IO, etc.) are limited, and the final result is that The amount of data that the database can carry and the ability of data processing will encounter bottlenecks.

How to do. Net ORM sub table? How to do. Net ORM branch library?

Full of feelings

Sub table and sub database can be said to be the biggest problem under. Net. To be simple, you can use middleware similar to MYCAT. However, as far as the ecology of. Net platform is concerned, it lacks powerful wheels like sharding JDBC.

Based on my limited technical level and experience, I analyze the sub table and sub database, and realize a set of self-contained use method. Although it is not very powerful, sharding JDBC is relatively general and simple. I hope that one day there will be a group of real. Net gods to create great open source projects and realize the aspirations of you and me.

This set of sub table and sub database method is based on. Net ORM FreeSQL. The content may be more abstract. Please understand! In the follow-up, we will explain various tenant design schemes in detail. In addition to distinguishing tenants by field, we also include the schemes of sub database and sub table. Please pay attention to them!

How to do. Net ORM sub table? How to do. Net ORM branch library?

Preparation for entering the play

FreeSQL is. Net ORM, which can support. Netframework 4.0 +,. NETCORE, xamarin, XAUI, blazor, and other running platforms. Because the code is green and independent, it is very simple to support the new platform. At present, the number of unit tests is 5000 +, the number of nuget downloads is 180K +, and the source code is submitted almost every day. We are glad that FreeSQL has joined the NCC open source communityhttps://github.com/dotnetcore/FreeSqlAfter joining the organization, the community has a greater sense of responsibility and needs to work harder to improve the quality and contribute to the open source community.

QQ group: 4336577 (full), 8578575 (online), 52508226 (online)

Why repeat the wheel?

How to do. Net ORM sub table? How to do. Net ORM branch library?

The main advantage of FreeSQL lies in its ease of use. It is basically out of the box and has good compatibility in switching between different databases. The author has spent a lot of time and energy on this project, please spend half an hour to understand the project, thank you. The functional features are as follows:

  • Support the migration of codefirst comparing with structural changes;
  • Support dbfirst to import entity class from database;
  • Support rich expression function, custom parsing;
  • Support batch addition, batch update and bulkcopy;
  • Support navigation attribute, greedy loading, delay loading and cascade saving;
  • It supports the separation of reading and writing, table and database, and tenant design;
  • Support MySQL / sqlserver / PostgreSQL / Oracle / SQLite / Dameng / Shentong / dajincang / msaccess;

FreeSQL is very easy to use. In stand-alone database, you only need to define an ifreesql object

static IFreeSql fsql = new FreeSql.FreeSqlBuilder()
    .UseConnectionString(FreeSql.DataType.MySql, connectionString)
    . useautosyncstructure (true) // automatically synchronize entity structure to database
    . build(); // be sure to define singleton singleton singleton mode

How to do. Net ORM sub table? How to do. Net ORM branch library?

Sub table

Since it’s a separate table, it’s bold to think that it’s a single machine database operation. You only need to dynamically map the table name to the entity class FreeSql.Repository The storage usage provides the astable method to crud the sub table, for example:

var repo = fsql.GetRepository<Log>();
repo.AsTable(oldname => $"{oldname}_201903");
//Yes, log_ 201903 form CRUD

repo.Insert(new Log { ... });
repo.Update(...);
repo.Delete(...);
repo.Select...;

Astable dynamically sets the table name of entity mapping to achieve the purpose of sub table operation. In addition to crud operation, it also provides the function of creating sub tables

  • If the automatic synchronization structure function useautosyncstructure (true) is enabled, astable will automatically create the corresponding sub table;
  • Can use fsql.CodeFirst.SyncStructure (typeof (entity class), “sub table name”) to create tables manually;

In most cases, we suggest that we create a good sub table in advance. If we divide the table by month, we should manually create the sub table for one year.

At present, this kind of solution is relatively simple, far less intelligent than MYCAT and sharding JDBC

  • It is impossible to use the sub table field to map the sub table automatically;
  • It can’t automatically map sub tables according to where condition, or even joint query across multiple sub tables;

How to do. Net ORM sub table? How to do. Net ORM branch library?

Sub Library (stand alone)

Sub database, but under the same database server instance. In this case, you can also use the astable method for operation, as follows:

var repo = fsql.GetRepository<Log>();
repo.AsTable(oldname => $"{201903}.dbo.{oldname}");
//Right [201903] dbo.Log  CRUD

After dividing databases, the biggest problem is transactions. If you use SQL server, you can use transactionscope to do simple cross database transactions, as follows:

var repoLog = fsql.GetRepository<Log>();
var repoComment = fsql.GetRepository<Comment>();
repoLog.AsTable(oldname => $"{201903}.dbo.{oldname}");
repoComment.AsTable(oldname => $"{201903}.dbo.{oldname}");

using (TransactionScope ts = new TransactionScope())
{
    repoComment.Insert(new Comment { ... });
    repoLog.Insert(new Log { ... });
    ts.Complete();
}

How to do. Net ORM sub table? How to do. Net ORM branch library?

Sub database (cross server)

As mentioned earlier, the [stand-alone database] only needs to define an ifreesql object. Does the sub database need to define many ifreesql objects? The answer is yes.

The general idea is to define static concurrent dictionary < string, ifreesql > to store all ifreesql objects (key = connectionstring). When crud is performed, the corresponding ifreesql can be obtained. Because ifreesql is a static singleton design, it will waste resources when there are too many sub databases, because not all sub databases are accessed all the time. For example, there are 10000 tenant sub repositories and 10000 static ifreesql?

A better way is to use idlebus idle object management container, effectively organize the reuse of objects, automatically create and destroy them, and solve the problem of excessive and long-time occupation of [instances]. Sometimes I want to make a singleton object to be reused to improve performance, but if there are too many definitions, some may be idle all the time. Special solution: the scene that wants to reuse and occupy less resources.https://github.com/2881099/Id…

dotnet add package IdleBus

static IdleBus<IFreeSql> ib = new IdleBus<IFreeSql>(TimeSpan.FromMinutes(10));

ib.Register("db1", () => new FreeSqlBuilder().UseConnectionString(DataType.MySql, "str1").Build());
ib.Register("db2", () => new FreeSqlBuilder().UseConnectionString(DataType.MySql, "str2").Build());
ib.Register("db3", () => new FreeSqlBuilder().UseConnectionString(DataType.SqlServer, "str3").Build());
//... register many

ib.Get("db1").Select<T>().Limit(10).ToList();

Idlebus is also a single example design! The two main methods are registration and acquisition. Idlebus registration is not to create ifreesql. It is only created when it is first got, and it will always be used later. There is also a time-out mechanism. If the ifreesql is not used for 10 minutes, it will be disposed, and then a new ifreesql will be created the next time. Thus, the problem of 10000 ifreesqls staying in memory for a long time is solved.

It also makes use of the asynclocal feature to expand and use it more conveniently

public static class IdleBusExtesions
{
    static AsyncLocal<string> asyncDb = new AsyncLocal<string>();
    public static IdleBus<IFreeSql> ChangeDatabase(this IdleBus<IFreeSql> ib, string db)
    {
        asyncDb.Value = db;
        return ib;
    }
    public static IFreeSql Get(this IdleBus<IFreeSql> ib) => ib.Get(asyncDb.Value ?? "db1");
    public static IBaseRepository<T> GetRepository<T>(this IdleBus<IFreeSql> ib) where T : class 
        => ib.Get().GetRepository<T>();
}
  • Switch dB with changedatabase;
  • Use get() to get the current ifreesql, omitting to pass the DB parameter every time;
  • Use getrepository to get the warehouse class corresponding to the current ifreesql;

Note: using idlebus needs to weaken the existence of ifreesql. Use it every time ib.Get Get ifreesql object;

Idlebus < ifreesql > IB =...; // single injection

var fsql =  ib.Get (); // get the ifreesql corresponding to the current tenant

var fsql00102 =  ib.ChangeDatabase ("DB2"). Get(); // switch tenants. The following operations are all for DB2

var songRepository = ib.GetRepository<Song>();
var detailRepository = ib.GetRepository<Detail>();

At present, this kind of solution is relatively simple, far less intelligent than MYCAT and sharding JDBC. For example, cross database transaction is not implemented.

Write at the end

. net ecology is still in a weak state. We call on everyone to support and actively participate in open source projects, and contribute to the next Five-Year Plan of. Net open source community.

I hope you can move your little finger and forward the article to let more people know that. Net has such a good orm. Thank you!!

FreeSQL open source protocol MIThttps://github.com/dotnetcore/FreeSql, which can be used for commercial use with complete documents. QQ group: 4336577 (full), 8578575 (online), 52508226 (online)

If you have a good idea of ORM implementation, please leave a message for the author to discuss. Thank you for watching!