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


Theoretical knowledge

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

Sub database – blocks the data originally stored in one database to multiple databases, and blocks the data originally stored in a table to multiple tables. The amount of data in the database is not necessarily controllable. With the development of time and business, there will be more and more tables in the database, and the amount of data in the tables will also be larger and larger. Correspondingly, the cost of data operation, addition, deletion and query will be more and more large. In addition, the resources (CPU, disk, memory, IO, etc.) of a server are limited. Finally, the resources of a server (CPU, disk, memory, IO, etc.) are limited 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 the. Net ORM sub library?

Full of feelings

It is a big problem to divide tables and databases under. Net. To be simple, you can use middleware like MYCAT. However, due to the ecology of. Net platform, there is a lack of powerful wheels like sharding JDBC.

Based on my own limited technical level and experience, I analyze the sub table and sub database, and realize a set of usage methods. Although sharding JDBC is not very powerful, it is still 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 future, we will explain various tenant design schemes in detail. In addition to distinguishing tenants by field, we also include the scheme of sub database and sub table. Please pay attention!

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

Getting ready for the play

FreeSQL is. Net ORM, which can support. Netframework4.0 +,. NETCORE, xamarin, XAUI, blazor, and other indescribable running platforms. Because the code is green and independent, it is very simple to support new platforms. 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. It is gratifying that FreeSQL has joined the NCC open source community: After 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 make wheels over and over again?

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

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

  • Support the migration of codefirst contrast structure change;
  • Support dbfirst to import entity classes from database;
  • Support rich expression functions and user-defined parsing;
  • Support batch addition, batch update and bulkcopy;
  • Support navigation attributes, greedy loading, delayed loading, cascade saving;
  • Support read-write separation, separate tables and databases, and tenant design;
  • Support MySQL / sqlserver / PostgreSQL / Oracle / SQLite / Damon / Shentong / Renmin Jincang / msaccess;

FreeSQL is very simple to use. For 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

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

Sub table

Since it is a separate table, it is bold to think that it is to operate a stand-alone database. You only need to dynamically map the entity class to the table name. The native usage of FreeSQL is 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_ Form 201903 CRUD

repo.Insert(new Log { ... });

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

  • If useautosyncstructure (true) is enabled, astable will automatically create corresponding sub tables;
  • Can be used fsql.CodeFirst.SyncStructure (typeof (entity class), “sub table name”) to create tables manually;

In most cases, we recommend creating a sub table in advance. If the sub table is divided by month, the sub table for one year should be created manually.

At present, this kind of solution is relatively simple to start with, which is far less intelligent than MYCAT and sharding JDBC. For example:

  • The sub table field cannot be used to map the sub table automatically;
  • It is not possible to automatically map sub tables or even joint queries across multiple sub tables according to where conditions;

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

Sub database (single machine)

But under the same database server instance. In this case, the astable mode can also be used, as follows:

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

After the database is divided, the biggest problem is the transaction. If you use sqlserver, 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 { ... });

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

Sub database (cross server)

As mentioned earlier, the stand-alone database only needs to define an ifreesql object. Does the sub library 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), and obtain the corresponding ifreesql when crud. Because ifreesql is a static singleton designed for long-term memory, resources will be wasted when the number of sub databases is too large, because not all sub databases are accessed all the time. For example, there are 10000 tenant sub databases and 10000 static ifreesql?

A better way is to use idle object management container of idlebus, effectively organize the reuse of objects, automatically create and destroy them, and solve the problem of too many instances and long time occupation. Sometimes you want to reuse a singleton object to improve performance, but if there are too many definitions, some may be idle and occupy resources. Special solution: scenarios that want to reuse and occupy less resources.…

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());
//... sign up for many


Idlebus is also a singleton design! The main two methods are registration and acquisition. Idlebus registration does not create ifreesql. It is created only when it gets the first time. The created ifreesql will be used in the future. There is also a timeout mechanism. If the ifreesql is not used for 10 minutes, it will be disposed, and then a new ifreesql will be created next time, and so on. Thus, the problem of 10000 ifreesql long resident memory is solved.

Besides, it is more convenient to use the asynclocal feature extension

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>();
  • Use change database to switch dB;
  • Get the current ifreesql with get() and omit 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, and use it every time ib.Get Get ifreesql object;

Idlebus < ifreesql > IB =...; // single case 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 for DB2

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

At present, this kind of solution is relatively simple to start with, which is far less intelligent than MYCAT and sharding JDBC. For example, it does not implement cross database transactions.

Write it at the end

The. Net ecosystem 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 that you can move your little finger and forward the article to let more people know that. Net has such a good orm. Thank you!!

Free SQL open source protocol MIT , can be commercially available, complete documentation. QQ group: 4336577 (full), 8578575 (online), 52508226 (online)

If you have good ORM implementation idea, welcome to leave a message to the author for discussion, thank you for watching!