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);

}