Select 子查询
new DBHelper<DTStudentModel>()
.Select(p => new
{
p.IdentityID,
p.StuCode,
p.StuName,
p.StuAge,
p.StuSex
})
.SelectSub<DTScoreModel>(new DBHelper<DTScoreModel>().Select(p => new { TotalScore = DBMethod.Sum(p.Score) }).Where<DTScoreModel, DTStudentModel>((k, v) => k.StuCode == v.StuCode), "TotalScore")
.OrderBy(p => p.IdentityID)
.ToEntityList<DTStudentModel>();
-- 对应 SQL
select [T_Student].[IdentityID],[T_Student].[StuCode],[T_Student].[StuName],[T_Student].[StuAge],[T_Student].[StuSex],(select Sum([T_Score].[Score]) as [TotalScore] from [T_Score] where ([T_Score].[StuCode] = [T_Student].[StuCode])) as [TotalScore] from [T_Student] order by [T_Student].[IdentityID] asc
From 子查询,必须使用 As 指定别名
new DBHelper<DTStudentModel>()
.As("subTable")
.Select(p => new
{
p.IdentityID,
p.StuCode,
p.StuName,
p.StuAge,
p.StuSex
})
.FromSub<DTStudentModel>(new DBHelper<DTStudentModel>().Select(p => new { p.IdentityID, p.StuCode, p.StuName, p.StuAge, p.StuSex }).Where(p => p.IdentityID > 10), "subTable")
.OrderBy(p => p.IdentityID)
.ToEntityList<DTStudentModel>();
-- 对应 SQL
select [subTable].[IdentityID],[subTable].[StuCode],[subTable].[StuName],[subTable].[StuAge],[subTable].[StuSex] from (select [T_Student].[IdentityID],[T_Student].[StuCode],[T_Student].[StuName],[T_Student].[StuAge],[T_Student].[StuSex] from [T_Student] where ([T_Student].[IdentityID] > 10)) as subTable order by [subTable].[IdentityID] asc
Where 子查询
new DBHelper<DTStudentModel>()
.Select(p => new
{
p.IdentityID,
p.StuCode,
p.StuName,
p.StuAge,
p.StuSex
})
.WhereSub<DTStudentModel>(p => p.StuCode, new DBHelper<DTStudentModel>().Select(p => new { p.StuCode }).Where(p => p.IdentityID >= 3 && p.IdentityID <= 7), DBWhereSubType.In)
.OrderBy(p => p.IdentityID)
.ToEntity<DTStudentModel>();
-- 对应 SQL
select [T_Student].[IdentityID],[T_Student].[StuCode],[T_Student].[StuName],[T_Student].[StuAge],[T_Student].[StuSex] from [T_Student] where [T_Student].[StuCode] in (select [T_Student].[StuCode] from [T_Student] where (([T_Student].[IdentityID] >= 3) and ([T_Student].[IdentityID] <= 7))) order by [T_Student].[IdentityID] asc