一、自动分表（高版本可用）
特性写法
//假如是按月分表：[Table(Name = "log_{yyyyMM}", AsTable = "createtime=2022-1-1(1 month)")]注意：①需包含log_202201这张表 ②递增规律是一个月一次，确保他们存在。 ③确保有字段createtime。
[Table(Name = "Teacher_{yyyy}", AsTable = "time=2023-1-1(1 year)")]
public class Teacher
{
[Column(IsPrimary = true)]
public int id { get; set; }
public DateTime time { get; set; }
}
程序写法（附上生成的SQL）：
static void Main(string[] args)
{
//-----------------------01查全表---------------------------
var list1 = freesql.Select<Teacher>().ToList();
var sql = freesql.Select<Teacher>().ToSql();
/*
SELECT * from (SELECT a.[id], a.[time]
FROM [Teacher_2024] a) ftb
UNION ALL
SELECT * from (SELECT a.[id], a.[time]
FROM [Teacher_2023] a) ftb
*/
//-----------------------02跨时间查表---------------------------
//var start = new DateTime(2023, 12, 31);
//var end = new DateTime(2024, 1, 2);
var sql2 = freesql.Select<Teacher>()
.Where(x => x.time.Between(DateTime.Parse("2023-12-31"), DateTime.Parse("2024-01-02")))//时间写法1
//.Where(x => x.time.Between(start,end))//时间写法2
//.Where(x => x.time>=start && x.time<=end)//时间写法3
.ToSql();
var list2 = freesql.Select<Teacher>()
.Where(x => x.time.Between(DateTime.Parse("2023-12-31"), DateTime.Parse("2024-01-02")))
.ToList();
/*
SELECT * from (SELECT a.[id], a.[time]
FROM [Teacher_2024] a
WHERE (a.[time] between '2023-12-31 00:00:00.000' and '2024-01-02 00:00:00.000')) ftb
UNION ALL
SELECT * from (SELECT a.[id], a.[time]
FROM [Teacher_2023] a
WHERE (a.[time] between '2023-12-31 00:00:00.000' and '2024-01-02 00:00:00.000')) ftb
*/
//-----------------------03不跨时间查表---------------------------
var list3 = freesql.Select<Teacher>()
.Where(x => x.time.Between(DateTime.Parse("2024-1-1"), DateTime.Parse("2024-01-02")))
.ToList();
var sql3 = freesql.Select<Teacher>()
.Where(x => x.time.Between(DateTime.Parse("2024-1-1"), DateTime.Parse("2024-01-02")))
.ToSql();
/*
SELECT a.[id], a.[time]
FROM [Teacher_2024] a
WHERE (a.[time] between '2024-01-01 00:00:00.000' and '2024-01-02 00:00:00.000')
*/
}二、手动分表
1.静态映射类型（简单）
//01 配置分表表名 映射实体 查询框架
var tablenamelist = new List<string> { "Student_2023", "Student_2024" };
var unionSelect = fsql.Queryable<StudentAll>();
var querySelect1 = fsql.Queryable<StudentAll>();
var querySelect2 = fsql.Queryable<StudentAll>();
//02 Union连接分表
foreach (var tableName in tablenamelist)
{
bool isExistTable = fsql.DbFirst.GetTableByName(tableName) != null;
if (isExistTable)//存在该表则union all
{
unionSelect.AsTable((type, oldname) => tableName);
//不要在遍历循环内部这里用where筛选，因为遍历多少次，就会加上where多少次，sql效率低
}
}
//03 编写sql条件
string cte = unionSelect.Where(x => x.Age.Equals(25))//这里不写cte，直接把where写到下面去也可以
.ToSql();//所有分表都会加上这个条件
//04 查表且映射到实体
var allList = querySelect1.WithSql(cte)
.ToList();//SELECT * FROM CTE
var pageList = querySelect2.WithSql(cte)
.Page(1, 20)
.Count(out var counts)
.ToList();//SELECT TOP 20 * FROM CTEcte处生成的sql是（下同）：
SELECT * FROM Student_2023 WHERE Age = 25
UNION ALL
SELECT * FROM Student_2024 WHERE Age = 25
注意：三个select不能合并，否则会导致重复查询！（下同）
- unionSelect 用于构建跨表的联合查询，并且没有执行查询。
- querySelect1 用于获取符合条件的全部数据。
- querySelect2 用于分页查询并获取指定页面的数据。
2.动态映射类型
要注意，先筛查，后联表，因此需要加上where语句，小表union小表才更快！（而不是联合成大表后再筛选）
//01 配置分表表名 映射实体 查询框架
var tablenamelist = new List<string> { "Student_2023", "Student_2024" };
var entity = typeof(StudentAll);
var unionSelect = fsql.Queryable<object>();
var querySelect1 = fsql.Queryable<object>();
var querySelect2 = fsql.Queryable<object>();
//02 Union连接分表
foreach (var tableName in tablenamelist)
{
bool isExistTable = fsql.DbFirst.GetTableByName(tableName) != null;
if (isExistTable)//存在该表则union all
{
unionSelect.AsType(entity).AsTable((type, oldname) => tableName);
//不要在遍历循环内部这里用where筛选，因为遍历多少次，就会加上where多少次，sql效率低
}
}
//03 编写sql条件
string cte = unionSelect.Where(x => (x as StudentAll).Age.Equals(25))//注意：此处的StudentAll必须是映射实体或其父类
.ToSql();//所有分表都会加上这个条件
//04 查表且映射到实体
var allList = querySelect1.AsType(entity)
.WithSql(cte)
.ToList()
.OfType<StudentAll>()
.ToList();//SELECT * FROM CTE
var pageList = querySelect2.AsType(entity)
.WithSql(cte)
.Page(1, 20)
.Count(out var counts)
.ToList().OfType<StudentAll>().ToList();//SELECT TOP 20 * FROM CTE注意：以下两种写法输出结果是一致的
List<object> data1 = freesql.Select<object>().AsType(type).WithSql(sql).ToList();
ISelect<object> data2 = freesql.Select<object>().WithSql(sql);
List<Student> result1 = data1.OfType<Student>().ToList();//两者输出是一致的
List<Student> result2 = data2.ToList<Student>();//两者输出是一致的
