简单分页查询
int totalCount = 0;
int pageCount = 0;
new DBHelper<DTStudentModel>()
.Select(p => new
{
p.IdentityID,
p.StuCode,
p.StuName,
p.StuAge,
p.StuSex
})
.OrderBy(p => p.IdentityID)
.ToEntityList<DTStudentModel>(2, 10, ref totalCount, ref pageCount);
-- 对应 SQL
select * from (select ROW_NUMBER() over(order by [T_Student].[IdentityID] asc) as '__RowNumber__', [T_Student].[IdentityID],[T_Student].[StuCode],[T_Student].[StuName],[T_Student].[StuAge],[T_Student].[StuSex] from [T_Student]) as __RowTable__ where __RowNumber__ between 11 and 20
Select 子查询,分页查询,查询学生基本信息和平均成绩
int totalCount = 0;
int pageCount = 0;
new DBHelper<DTStudentModel>()
.Select(p => new
{
p.IdentityID,
p.StuCode,
p.StuName,
p.StuAge,
p.StuSex
})
.SelectSub<DTScoreModel>(new DBHelper<DTScoreModel>().Select(p => new { AvgScore = DBMethod.Avg(p.Score) }).Where<DTScoreModel, DTStudentModel>((k, v) => k.StuCode == v.StuCode), "AvgScore")
.OrderBy(p => p.IdentityID)
.ToEntityList<DTStudentModel>(1, 10, ref totalCount, ref pageCount);
-- 对应 SQL
select * from (select ROW_NUMBER() over(order by [T_Student].[IdentityID] asc) as '__RowNumber__', [T_Student].[IdentityID],[T_Student].[StuCode],[T_Student].[StuName],[T_Student].[StuAge],[T_Student].[StuSex],(select Avg([T_Score].[Score]) as [AvgScore] from [T_Score] where ([T_Score].[StuCode] = [T_Student].[StuCode])) as AvgScore from [T_Student]) as __RowTable__ where __RowNumber__ between 1 and 10
联表分页查询,查询学生基本信息和成绩
new DBHelper<DTStudentModel>()
.Select(p => new
{
p.IdentityID,
p.StuCode,
p.StuName,
p.StuAge,
p.StuSex
})
.Select<DTScoreModel>(p => p.Score)
.Join<DTStudentModel, DTScoreModel>(DBJoinType.Left, (k, v) => k.StuCode == v.StuCode)
.OrderBy(p => p.IdentityID)
.ToEntityList<DTStudentModel>(1, 10, ref totalCount, ref pageCount);
-- 对应 SQL
select * from (select ROW_NUMBER() over(order by [T_Student].[IdentityID] asc) as '__RowNumber__', [T_Student].[IdentityID],[T_Student].[StuCode],[T_Student].[StuName],[T_Student].[StuAge],[T_Student].[StuSex],[T_Score].[Score] from [T_Student] left join [T_Score] on ([T_Student].[StuCode] = [T_Score].[StuCode])) as __RowTable__ where __RowNumber__ between 1 and 10