CODEWITHSUNDEEP
c#sql-serverlinq
shadowwave
shadowwave

Reputation: 39

convert sql (row_number and DENSE_RANK) to linq

I have the below SQL(MS-Sql 2014) statement that works .

How do you convert this SQL to LINQ?

I'm reading it now, but just putting this out there in case I can't do it.

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

Thank you very much.

Upvotes: 4

Views: 1584

Answers (1)

Gilad Green
Gilad Green

Reputation: 37299

Probably not relevant anymore but for whoever else crosses upon this:

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")....

Upvotes: 2

Related Questions