CODEWITHSUNDEEP
c#.netlinqlist
LeFoxie
LeFoxie

Reputation: 31

Linq for rank() equivalent in SQL Server -

How can order my list using Linq equals rank() over in SQL ?

For example rank is my List<Player>

class Player
{
    public int Id;
    public int RankNumber;
    public int Points;
    public int Name;

}

Original Rank list: 

RankNumber   Points  Name    Id
    1          100    James   01
    2          80     Mike    50
    3          80     Jess    22
    4          50     Jack    11
    5          50     Paul    03  
    6          10     Monik   13

I need this Rank:

RankNumber   Points  Name    Id
    1          100    James   01
    2          80     Mike    50
    2          80     Jess    22
    4          50     Jack    11
    4          50     Paul    03  
    6          10     Monik   13

Upvotes: 3

Views: 229

Answers (2)

ATP
ATP

Reputation: 561

You can try below expression:

var newData = players
             .OrderByDescending(x => x.Points)
             .GroupBy(x => x.Points)                 
             .SelectMany((x, index) => x.Select(y => new Player
            {
                Name = y.Name,
                Points = y.Points,
                RankNumber = index + 1,
                Id = y.Id
            }));

players contains IEnumerable of objects of type Player and newData contains ordered data with rank.

Upvotes: 0

DavidG
DavidG

Reputation: 119076

I don't think there is a good way to convert this directly to Linq to SQL but you could do this:

var rankedPlayers = players
    .OrderByDescending(p => p.Points)
    .Select((p, r) => new Player
    {
        Id = p.Id,
        RankNumber = players.Where(pl => pl.Points > p.Points).Count() + 1,
        Points = p.Points,
        Name = p.Name
    });

It gives you the correct output but will convert horribly and inefficiently to SQL. So I would suggest this modification which materialises the data to a list before creating the ranks:

var rankedPlayers = players
    .OrderByDescending(p => p.Points)
    .ToList() //<--- Add this
    .Select((p, r) => new Player
    {
        Id = p.Id,
        RankNumber = players.Where(pl => pl.Points > p.Points).Count() + 1,
        Points = p.Points,
        Name = p.Name
    });

Upvotes: 1

Related Questions