我有下面的SQL(MS-Sql 2014)语句 .
你如何将这个SQL转换为LINQ?
我现在正在读它,但只是把它放在那里以防万一我无法做到 .
select OriginalDocumentNumber ,FilterRound , p2_Reason , p2_FinalGrade , rowno , s_index , t_index
from (
select * ,
rowno=row_number() over (partition by OriginalDocumentNumber,FilterRound order by p2_FinalGrade desc) ,
s_index = ROW_NUMBER() OVER(PARTITION BY FilterRound,OriginalDocumentNumber ORDER BY p2_FinalGrade),
t_index = DENSE_RANK() OVER (ORDER BY p2_FinalGrade)
from #TempDa
) l
where
l.p2_FinalGrade in ('d','f')
and l.rowno = 1
and l.OriginalDocumentNumber = '590200054'
and l.p2_Reason = 'test'
and l.OriginalDocumentNumber+l.FilterRound not in (select OriginalDocumentNumber+FilterRound from #TempDa where p2_FinalGrade in ('a','b','c')
)
更新
List<string> validValues = new List<string>() { "A", "B", "C" };
List<string> GradeReject = new List<string>() { "d", "f" };
var tempDb =
res.GroupBy(p => new {p.OriginalDocumentNumber, p.FilterRound})
.Select(grp => grp.OrderByDescending(g => g.p2_FinalGrade).FirstOrDefault());
var temp = res.Where(r => validValues.Contains(r.p2_FinalGrade)).Select(r => r.OriginalDocumentNumber+r.FilterRound);
fds = tempDb.Where(r => GradeReject.Contains(r.p2_FinalGrade) && !temp.Contains(r.OriginalDocumentNumber + r.FilterRound))
.Select(r => new DataAllProcessDetailModels() { FilterRound = r.FilterRound, OriginalDocumentNumber = r.OriginalDocumentNumber, DocumentNumber = r.DocumentNumber, LandDescription = r.LandDescription, DStatus = " " }).ToList();
非常感谢你 .
1 回答
可能不再相关,但对于其他任何人来说:
implement dense rank with linq