河北省网站快速备案,网站排名总是不稳定,网站建设公司选择哪家好,网站开发合同范本一、自动分表#xff08;高版本可用#xff09;
特性写法 //假如是按月分表#xff1a;[Table(Name log_{yyyyMM}, AsTable createtime2022-1-1(1 month))]注意#xff1a;①需包含log_202201这张表 ②递增规律是一个月一次#xff0c;确保他们…一、自动分表高版本可用
特性写法 //假如是按月分表[Table(Name log_{yyyyMM}, AsTable createtime2022-1-1(1 month))]注意①需包含log_202201这张表 ②递增规律是一个月一次确保他们存在。 ③确保有字段createtime。[Table(Name Teacher_{yyyy}, AsTable time2023-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.SelectTeacher().ToList();var sql freesql.SelectTeacher().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.SelectTeacher().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.timestart x.timeend)//时间写法3.ToSql();var list2 freesql.SelectTeacher().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.SelectTeacher().Where(x x.time.Between(DateTime.Parse(2024-1-1), DateTime.Parse(2024-01-02))).ToList();var sql3 freesql.SelectTeacher().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 Liststring { Student_2023, Student_2024 };
var unionSelect fsql.QueryableStudentAll();
var querySelect1 fsql.QueryableStudentAll();
var querySelect2 fsql.QueryableStudentAll();//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 Liststring { Student_2023, Student_2024 };
var entity typeof(StudentAll);
var unionSelect fsql.Queryableobject();
var querySelect1 fsql.Queryableobject();
var querySelect2 fsql.Queryableobject();//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().OfTypeStudentAll().ToList();//SELECT * FROM CTEvar pageList querySelect2.AsType(entity).WithSql(cte).Page(1, 20).Count(out var counts).ToList().OfTypeStudentAll().ToList();//SELECT TOP 20 * FROM CTE
注意以下两种写法输出结果是一致的
Listobject data1 freesql.Selectobject().AsType(type).WithSql(sql).ToList();
ISelectobject data2 freesql.Selectobject().WithSql(sql);ListStudent result1 data1.OfTypeStudent().ToList();//两者输出是一致的
ListStudent result2 data2.ToListStudent();//两者输出是一致的
