c# - 将 sql (row_number 和 DENSE_RANK) 转换为 linq

Question

我有以下有效的 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')             ) 

update

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();

非常感谢。

Answer 1

可能不再相关，但对于其他人来说:

Answer with more explanation about how to do the dense_rank:

implement dense rank with linq

Answer for this specific question:

var result = data.GroupBy(item => new { item.OriginalDocumentNumber, item.FilterRound })
    .OrderBy(@group => @group.First().p2_FinalGrade)
    .AsEnumerable()
    .Select((@group, groupIndex) => new
    {
        Items = @group.Select((item, index) => new { Item = item, Index = ++index }),
        Rank = ++groupIndex
    })
    .SelectMany(v => v.Items, (s, i) => new
    {
        Data = i.Item,
        RankInGroup = i.Index,
        DenseRank = s.Rank
    }).ToList();

result.Where(item => item.Data.p2_FinalGrade == "d" ||
                        item.Data.p2_FinalGrade == "f")
      .Where(item => item.Data.OriginalDocumentNumber == "590200054")....