(精華)2020年7月15日 ASP.NET Core EFCore分庫分表框架的使用(手寫版)

public void ConfigureServices(IServiceCollection services)
{
    services.AddEFCoreSharding(config =>
    {
        單表
        //config.UseDatabase(Config.CONSTRING1, DatabaseType.SqlServer);
        使用多個資料庫
        //config.UseDatabase<IMyDbAccessor>(Config.CONSTRING1, DatabaseType.SqlServer);

        //DateTime startTime = DateTime.Now.AddMinutes(-5);
        //DateTime endTime = DateTime.MaxValue;
        分表
        //config.AddAbsDb(DatabaseType.SqlServer)//新增抽象資料庫
        //    .AddPhysicDbGroup()//新增物理資料庫組
        //    .AddPhysicDb(ReadWriteType.Read | ReadWriteType.Write, Config.CONSTRING1)//新增物理資料庫1
        //    .SetDateShardingRule<Base_UnitTest>(nameof(Base_UnitTest.CreateTime))//設定分表規則
        //    .AutoExpandByDate<Base_UnitTest>(//設定為按時間自動分表
        //        ExpandByDateMode.PerMinute,
        //        (startTime, endTime, ShardingConfig.DefaultDbGourpName)
        //        );
        //獲取配置檔案的連線字串
        string conName = Configuration["ConnectionName"];
        //判斷是否啟用邏輯刪除
        if (Configuration["LogicDelete"].ToBool())
            config.UseLogicDelete();
        //連線資料庫
        config.UseDatabase(Configuration.GetConnectionString(conName), Configuration["DatabaseType"].ToEnum<DatabaseType>());
        //設定啟用程式集的名稱
        config.SetEntityAssembly(GlobalData.FXASSEMBLY_PATTERN);
    });
}

appsettings.json

{
  "LogicDelete": false, //是否開啟邏輯刪除,預設為物理刪除
  "DatabaseType": "SqlServer", //預設資料庫型別,定義詳見EFCore.Sharding.DatabaseType
  "ConnectionName": "BaseDb", //預設連線字串名
  "ConnectionStrings": {
    //SQLServer
    "BaseDb": "Data Source=.;Initial Catalog=Colder.Admin.AntdVue;Integrated Security=True;Pooling=true;"
    //PostgreSQL
    //"BaseDb": "SERVER=localhost;PORT=5432;DATABASE=Colder.Admin.AntdVue;USER ID=postgres;PASSWORD=postgres"
    //MySQl
    //"BaseDb": "server=127.0.0.1;user id=root;password=root;persistsecurityinfo=True;database=Colder.Admin.AntdVue;SslMode=none"
    //Oracle
    //"BaseDb": "Data Source=127.0.0.1/ORCL;User ID=COLDER.ADMIN.ANTDVUE;Password=123456;Connect Timeout=3"
  }
}

普通的使用方式

ShardingConfig.Init(config =>
{
    config.AddAbsDb(DatabaseType.SQLite)
        .AddPhysicDb(ReadWriteType.Read | ReadWriteType.Write, "DataSource=db.db")
        .AddPhysicDbGroup()
        .SetHashModShardingRule<Base_UnitTest>(nameof(Base_UnitTest.Id), 3);
});

using(IShardingDbAccessor _db = DbFactory.GetShardingDbAccessor())
{
	Base_UnitTest _newData  = new Base_UnitTest
	{
	    Id = Guid.NewGuid().ToString(),
	    UserId = "Admin",
	    UserName = "超級管理員",
	    Age = 22
	};
	List<Base_UnitTest> _insertList = new List<Base_UnitTest>
	{
	    new Base_UnitTest
	    {
	        Id = Guid.NewGuid().ToString(),
	        UserId = "Admin1",
	        UserName = "超級管理員1",
	        Age = 22
	    },
	    new Base_UnitTest
	    {
	        Id = Guid.NewGuid().ToString(),
	        UserId = "Admin2",
	        UserName = "超級管理員2",
	        Age = 22
	    }
	};
	//新增單條資料
	_db.Insert(_newData);
	//新增多條資料
	_db.Insert(_insertList);
	//清空表
	_db.DeleteAll<Base_UnitTest>();
	//刪除單條資料
	_db.Delete(_newData);
	//刪除多條資料
	_db.Delete(_insertList);
	//刪除指定資料
	_db.Delete<Base_UnitTest>(x => x.UserId == "Admin2");
	//更新單條資料
	_db.Update(_newData);
	//更新多條資料
	_db.Update(_insertList);
	//更新單條資料指定屬性
	_db.UpdateAny(_newData, new List<string> { "UserName", "Age" });
	//更新多條資料指定屬性
	_db.UpdateAny(_insertList, new List<string> { "UserName", "Age" });
	//更新指定條件資料
	_db.UpdateWhere<Base_UnitTest>(x => x.UserId == "Admin", x =>
	{
	    x.UserId = "Admin2";
	});
	//GetList獲取表的所有資料
	var list=_db.GetList<Base_UnitTest>();
	//GetIQPagination獲取分頁後的資料
	var list=_db.GetIShardingQueryable<Base_UnitTest>().GetPagination(pagination);
	//Max
	var max=_db.GetIShardingQueryable<Base_UnitTest>().Max(x => x.Age);
	//Min
	var min=_db.GetIShardingQueryable<Base_UnitTest>().Min(x => x.Age);
	//Average
	var min=_db.GetIShardingQueryable<Base_UnitTest>().Average(x => x.Age);
	//Count
	var min=_db.GetIShardingQueryable<Base_UnitTest>().Count();
	//事務,使用方式與普通事務一致
	bool succcess = _db.RunTransaction(() =>
	{
	    _db.Insert(_newData);
	    var newData2 = _newData.DeepClone();
	    _db.Insert(newData2);
	}).Success;
	Assert.AreEqual(succcess, false);
	}

按時間分表

var db = DbFactory.GetShardingDbAccessor();
while (true)
{
    try
    {
        db.Insert(new Base_UnitTest
        {
            Id = Guid.NewGuid().ToString(),
            Age = 1,
            UserName = Guid.NewGuid().ToString(),
            CreateTime = DateTime.Now
        });

        DateTime time = DateTime.Now.AddMinutes(-2);
        var count = db.GetIShardingQueryable<Base_UnitTest>()
            .Where(x => x.CreateTime >= time)
            .Count();
        Console.WriteLine($"當前資料量:{count}");
        
        Stopwatch watch = new Stopwatch();
        var q = db.GetIQueryable<Base_UnitTest>()
            .Where(x => x.UserName.Contains("00001C22-8DD2-4D47-B500-407554B099AB"))
            .OrderByDescending(x => x.Id)
            .Skip(0)
            .Take(30);
        q.ToList();
        q.ToSharding().ToList();
        watch.Restart();
        var list1 = q.ToList();
        watch.Stop();
        Console.WriteLine($"未分表耗時:{watch.ElapsedMilliseconds}ms");//7800ms
        watch.Restart();
        var list2 = q.ToSharding().ToList();
        watch.Stop();
        Console.WriteLine($"分表後耗時:{watch.ElapsedMilliseconds}ms");//2400ms
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }

    Thread.Sleep(50);
}

備註

/// <summary>
/// 單元測試表
/// </summary>
[Table("Base_UnitTest")]
[Index(false, nameof(CreateTime))]//建表自動建立索引
[Index(false, nameof(Age))]//建表自動建立索引
[Keys(nameof(Id), nameof(UserName))]//自動建表時會自動建立主鍵(多主鍵支援)
public class Base_UnitTest
{
    /// <summary>
    /// 代理主鍵
    /// </summary>
    [Key, StringLength(50)]
    public String Id { get; set; }

    /// <summary>
    /// 建立時間
    /// </summary>
    public DateTime CreateTime { get; set; }

    /// <summary>
    /// 使用者名稱
    /// </summary>
    public String UserName { get; set; }

    /// <summary>
    /// Age
    /// </summary>
    public Int32? Age { get; set; }
}

多資料庫插入

class Program
    {
        static void Main(string[] args)
        {
            Host.CreateDefaultBuilder(args)
                .ConfigureLogging(config =>
                {

                })
                .ConfigureServices((host, services) =>
                {
                    services.AddHostedService<DbTest>();
                    services.UseEFCoreSharding(config =>
                    {
                        //單表
                        config.UseDatabase(Config.CONSTRING1, DatabaseType.SqlServer);
                        //使用多個資料庫
                        config.UseDatabase<IMyDbAccessor>(Config.CONSTRING1, DatabaseType.SqlServer);

                        DateTime startTime = DateTime.Now.AddMinutes(-5);
                        DateTime endTime = DateTime.MaxValue;
                        //分表
                        config.AddAbsDb(DatabaseType.SqlServer)//新增抽象資料庫
                            .AddPhysicDbGroup()//新增物理資料庫組
                            .AddPhysicDb(ReadWriteType.Read | ReadWriteType.Write, Config.CONSTRING1)//新增物理資料庫1
                            .SetDateShardingRule<Base_UnitTest>(nameof(Base_UnitTest.CreateTime))//設定分表規則
                            .AutoExpandByDate<Base_UnitTest>(//設定為按時間自動分表
                                ExpandByDateMode.PerMinute,
                                (startTime, endTime, ShardingConfig.DefaultDbGourpName)
                                );
                    });
                })
                .Build()
                .Run();
        }
    }

public interface IMyDbAccessor : IDbAccessor
    {

    }

class DbTest : BackgroundService
    {
        readonly IServiceProvider _serviceProvider;
        readonly ILogger _logger;
        public DbTest(IServiceProvider serviceProvider, ILogger<DbTest> logger)
        {
            _serviceProvider = serviceProvider;
            _logger = logger;
        }
        protected override async Task ExecuteAsync(CancellationToken stoppingToken)
        {
            Task.Factory.StartNew(async () =>
            {
                while (true)
                {
                    try
                    {
                        using (var scop = _serviceProvider.CreateScope())
                        {
                            //單表
                            var db = scop.ServiceProvider.GetService<IMyDbAccessor>();
                            List<Base_UnitTest> insertList = new List<Base_UnitTest>();
                            for (int i = 0; i < 100; i++)
                            {
                                insertList.Add(new Base_UnitTest
                                {
                                    Id = Guid.NewGuid().ToString(),
                                    Age = i,
                                    CreateTime = DateTime.Now,
                                    UserName = Guid.NewGuid().ToString()
                                });
                            }

                            var single = new Base_UnitTest
                            {
                                Id = Guid.NewGuid().ToString(),
                                Age = 100,
                                CreateTime = DateTime.Now,
                                UserName = Guid.NewGuid().ToString()
                            };

                            await db.InsertAsync(single);
                            await db.InsertAsync(insertList);

                            int count = await db.GetIQueryable<Base_UnitTest>().CountAsync();
                            _logger.LogInformation("單表插入資料成功 當前資料量:{Count}", count);

                            //分表
                            var shardingDb = scop.ServiceProvider.GetService<IShardingDbAccessor>();
                            await shardingDb.InsertAsync(single);
                            await shardingDb.InsertAsync(insertList);
                            count = await shardingDb.GetIShardingQueryable<Base_UnitTest>().CountAsync();
                            _logger.LogInformation("分表插入資料成功 當前資料量:{Count}", count);
                        }
                    }
                    catch (Exception ex)
                    {
                        _logger.LogError(ex, "");
                    }

                    await Task.Delay(2000);
                }

            }, TaskCreationOptions.LongRunning);

            await Task.CompletedTask;
        }
    }

webapi的使用

[ApiController]
[Route("[controller]")]
public class TestController : ControllerBase
{
    readonly IShardingDbAccessor _shardingDbAccessor;
    public TestController(IShardingDbAccessor shardingDbAccessor)
    {
        _shardingDbAccessor = shardingDbAccessor;
    }

    [HttpGet]
    public async Task<string> Get()
    {
        List<Base_UnitTest> insertList = new List<Base_UnitTest>();
        for (int i = 0; i < 100; i++)
        {
            insertList.Add(new Base_UnitTest
            {
                Id = Guid.NewGuid().ToString(),
                Age = i,
                CreateTime = DateTime.Now,
                UserName = Guid.NewGuid().ToString()
            });
        }

        await _shardingDbAccessor.InsertAsync(insertList);

        return "成功";
    }
}

備註：非分表用IDbAccessor，分表用IShardingDbAccessor