Web开发：ORM框架之使用Freesql的分表分页写法

一、自动分表（高版本可用）

特性写法

    //假如是按月分表：[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) ftbUNION ALLSELECT  * 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')) ftbUNION ALLSELECT  * 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 CTEvar pageList = querySelect2.WithSql(cte).Page(1, 20).Count(out var counts).ToList();//SELECT TOP 20 * FROM CTE

 cte处生成的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 CTEvar 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>();//两者输出是一致的