一、依赖环境

创建.net 6控制台项目，并安装包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);
}