FreeSql自动分表

一、依赖环境

并安装包FreeSql.Provider.Sqlite--根据不同的数据库选择

二、添加实体类

using FreeSql.DataAnnotations;
//自动分表策略：按照createtime字段每月创建一个分表，起始时间为5月（即默认创建               
topic_202405,topic_202406,topic_202407三张表）
[Table(Name = "topic_{yyyyMM}", AsTable="createtime=2024-5-1(1 month)")]
 public class Topic
 {
     [Column(IsIdentity = true, IsPrimary = true)]
     public int Id { get; set; }
     public int Clicks { get; set; }
     public string? Title { get; set; }
     public DateTime CreateTime { get; set; }  //根据此字段分表
 }

三、示例代码

当前日期：2024-07-19
static void Main(string[] args)
{
var fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.Sqlite, "Data Source=./local.db")
.UseAutoSyncStructure(true)
.Build();

//生成测试数据
for (int offset = -2; offset < 1; offset++)
{
    var datetime = DateTime.Now.AddMonths(offset);
    for (int i = 0; i < 10; i++)
    {
        var item = new Topic { Title = $"测试-{datetime.Month}月", Clicks = 100 + i, CreateTime = datetime };
        var insert_row_count = fsql.Insert(item).ExecuteAffrows(); //自动根据CreateTime时间，插入相应月份分表
        Console.WriteLine(insert_row_count);
    }
}

//查询所有分表数据
var item_list = fsql.Select<Topic>().ToList();
foreach (var i in item_list)
{
    Console.WriteLine($"{i.Id}\t{i.Title}\t{i.Clicks}\t{i.CreateTime}");
}

//获取自动分表名称
var tablename = fsql.CodeFirst
    .GetTableByEntity(typeof(Topic))
    .AsTableImpl
    .GetTableNameByColumnValue(DateTime.Now.AddMonths(-1), true);  //获取上个月的表名

//查询指定分表
var july_items = fsql.Select<Topic>().AsTable((type, oldname) => tablename).ToList();
foreach (var i in july_items)
{
    Console.WriteLine($"{i.Id}\t{i.Title}\t{i.Clicks}\t{i.CreateTime}");
}

//更新指定表，主键等于3的行
var update_row_count = fsql.Update<Topic>(3)
    .Set(x => new { Title = $"更新测试111" }) //更新具体字段
    .AsTable(tablename)   //不指定表，会更新所有分表主键(id)为3的记录
    .ExecuteAffrows();
Console.WriteLine(update_row_count);

//删除指定表，主键等于3的行
var delete_row_count = fsql.Delete<Topic>(3)
    .AsTable(tablename)
    .ExecuteAffrows();
Console.WriteLine(delete_row_count);

}