假如利用select KeyValue,ID,Status from ComputingResult 得到一个实体Temp,实体中三个字段KeyValue1,ID1,Status1 分别对应select语句中的三个字段,接下来如何使用Linq操作Temp,得到以下sql语句的效果:
select KeyValue,ID,Status from ComputingResult where ID in ( select max(ID) as ID from ComputingResult where RegularRecord = 1 and LLKI='a' group by KeyValue)写到一半不会写了:
var temp1 = from p in Temp
where p.ID1 == LCRNormal.Max(p2 => p2.ID1)
group p by p.KeyValue;
select KeyValue,ID,Status from ComputingResult where ID in ( select max(ID) as ID from ComputingResult where RegularRecord = 1 and LLKI='a' group by KeyValue)写到一半不会写了:
var temp1 = from p in Temp
where p.ID1 == LCRNormal.Max(p2 => p2.ID1)
group p by p.KeyValue;
select KeyValue,ID,Status from ComputingResult where ID in ( select max(ID) as ID from ComputingResult where RegularRecord = 1 and LLKI='a' group by KeyValue)
=========
var query=from cr in ComputingResult
let listId=from crt in ComputingResult
where crt. RegularRecord == 1 && crt.LLKI=="a"
group crt by crt.Keyvalue into m
select m.Max(n=>n.ID)
where listId.Contains(cr.ID)
select new
{
crt.Keyvalue,
crt.ID,
crt.Status
};
=========
var query=from cr in ComputingResult
let listId=from crt in ComputingResult
where crt. RegularRecord == 1 && crt.LLKI=="a"
group crt by crt.Keyvalue into m
select m.Max(n=>n.ID)
where listId.Contains(cr.ID)
select new //代码手写 此处更正一下
{
cr.Keyvalue,
cr.ID,
cr.Status
};
再执行select KeyValue,ID,Status from ComputingResult where ID in ( )语句
let maxIDs = from p1 in ComputingResult
where p1.RegularRecord == 1 && p1.LIKI == "a"
group p1 by p1.KeyValue into g
select g.Max(x => x.ID)
where maxIDs.Contains(p.ID)
select p;
.GroupBy(g => g.KeyValue)
.Select(s => s.OrderByDescending(o => o.ID).FirstOrDefault())
.Select(s => new { KeyValue = s.KeyValue, ID = s.ID, Status = s.Status });
//var query = Temp.Where(w => w.RegularRecord == 1 && w.LLKI == "a")
// .GroupBy(g => g.KeyValue)
// .Select(s => s.OrderByDescending(o => o.ID).FirstOrDefault());
var query=from cr in ComputingResult
let listId=from crt in ComputingResult
where crt. RegularRecord == 1 && crt.LLKI=="a" //在第一批筛选的基础数据上第二批筛选
group crt by crt.Keyvalue into m //第一批筛选
select m.Max(n=>n.ID) //第一批筛选
where listId.Contains(cr.ID)
select new //代码手写 此处更正一下
{
cr.Keyvalue,
cr.ID,
cr.Status
};
这样的SQL语句 ,数据查询筛选都是在SQL端执行的
怎么筛选的 这就要看SQL语句在SQL客户端上的执行顺序了
group crt by crt.Keyvalue into m //第一批筛选
select m.Max(n=>n.ID) //第一批筛选
然后,在筛选出的数据上再做
crt. RegularRecord == 1 && crt.LLKI=="a" //在第一批筛选的基础数据上第二批筛选现在是先crt. RegularRecord == 1 && crt.LLKI=="a",然后再group crt by 了,得到的数据是错的。
应该是:select KeyValue,ID,Status from ComputingResult where ID in ( select max(ID) as ID from ComputingResult group by KeyValue) and RegularRecord = 1 and LLKI='a'
let listId=from crt in ComputingResult
where crt. RegularRecord == 1 && crt.LLKI=="a"group crt by crt.Keyvalue into mselect m.Max(n=>n.ID)
where listId.Contains(cr.ID) &&
cr.RegularRecord == 1 &&
cr.LLKI == "a" select cr;
.Select(s => s.OrderByDescending(o => o.ID).FirstOrDefault())
.Where(w => w.RegularRecord == 1 && w.LLKI == "a")
.Select(s => new { KeyValue = s.KeyValue, ID = s.ID, Status = s.Status });这样也行吧,麻烦看下对不?
.Where(w => w.RegularRecord == 1 && w.LLKI == "a")
.Select(s => s.OrderByDescending(o => o.ID).FirstOrDefault())
.Select(s => new { KeyValue = s.KeyValue, ID = s.ID, Status = s.Status });
.Select(s => s.Where(w=>w.RegularRecord == 1 && w.LLKI == "a")
.OrderByDescending(o => o.ID)
.FirstOrDefault())
.Select(s => new { KeyValue = s.KeyValue, ID = s.ID, Status = s.Status });
.Select(s => s.OrderByDescending(o => o.ID).FirstOrDefault())
.Where(w => w.RegularRecord == 1 && w.LLKI == "a")
.Select(s => new { KeyValue = s.KeyValue, ID = s.ID, Status = s.Status });这种测试结果是对的
var query = Temp.GroupBy(g => g.KeyValue)
.Select(s => s.Where(w=>w.RegularRecord == 1 && w.LLKI == "a")
.OrderByDescending(o => o.ID)
.FirstOrDefault())
.Select(s => new { KeyValue = s.KeyValue, ID = s.ID, Status = s.Status });这种还是先w=>w.RegularRecord == 1 && w.LLKI == "a",然后再OrderByDescending、FirstOrDefault了感谢!