Linq for rank() equivalent in SQL Server -

Question

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

Seems to be a few different solutions [here](http://stackoverflow.com/questions/9873962/converting-sql-rank-to-linq-or-alternative). — Bradford Dillon, Nov 02 '15 at 22:40
You could try this [answer1](http://stackoverflow.com/questions/20953645/implementing-rank-over-sql-clause-in-c-sharp-linq) or this [answer2](http://stackoverflow.com/questions/9873962/converting-sql-rank-to-linq-or-alternative) — Boris Sokolov, Nov 02 '15 at 22:41

score 1 · Answer 1 · answered Nov 02 '15 at 22:39

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

score 0 · Answer 2 · answered Nov 03 '15 at 05:04

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.

Linq for rank() equivalent in SQL Server -

2 Answers2