How to get IGrouping index with Linq to entities ? (NotSupportedException)

Question

I'm using EF code first, and I'm trying to create a query which would get a player's rank based on a particular column, here Seniority.
Here is an example of what could be the Players table (Rank is not part of it, I need to deduce it from Seniority):

Username | Seniority (=> Rank)  
player1  | 25        (=> 1st)  
player2  | 20        (=> 2nd)  
player3  | 20        (=> 2nd)  
player4  | 18        (=> 3rd)  
player5  | 16        (=> 4th)  
player6  | 16        (=> 4th)  
player7  | 15        (=> 5th)  
player8  | 11        (=> 6th)  
player9  | 10        (=> 7th)  
player10 | 8         (=> 8th)  
player11 | 7         (=> 9th)  
player12 | 5         (=> 10th)  
player13 | 2         (=> 11th)  
player14 | 1         (=> 12th)  
player15 | 0         (=> 13th) 

I would need the following request to return all players whose rank is <= 10 (i.e. from player1 to player12), and then paginate this; for instance the first page with 10 max elements per page (i.e. from player1 to player10).

entities.Players
.GroupBy(p => p.Seniority)
.Where((group, groupIndex) => groupIndex <= 10)
.SelectMany((group, groupIndex) =>
    group.Select(grp =>
        new PlayerRanking()
        {
            Player = grp,
            Rank = groupIndex + 1,
            Score = grp.Seniority
        }
    )
    .OrderByDescending(r => r.Score)
    .ThenBy(r => r.Player.UserName)
)
.ToPagedList(pageNb, 10);

LINQ to Entities does not recognize the method 'System.Linq.IQueryable1[Models.BO.Ranking.PlayerRanking] SelectMany[IGrouping2,PlayerRanking](System.Linq.IQueryable1[System.Linq.IGrouping2[System.Int32,Models.BO.Players.Player]], System.Linq.Expressions.Expression1[System.Func3[System.Linq.IGrouping2[System.Int32,Models.BO.Players.Player],System.Int32,System.Collections.Generic.IEnumerable1[Models.BO.Ranking.PlayerRanking]]])', and this method cannot be translated into a store expression.

I read on some other posts that EF is complaining about Where and SelectMany having two arguments, but could I do without ?
I don't want to use ToList() nor AsEnumerable() because if I do, I guess the request will enumerate all Players entries (which can be really huge...) although I only need 10 of them. I really care about performances on this particular query.

Is there a way ?

Thanks a lot.

@octavioccl

I just added the Skip part for pagination:

var result = entities.Players.GroupBy(p => p.Seniority)
                       .OrderBy(e=>e.Key)
                       .Skip((pageNb - 1) * 10)
                       .Take(10)
                       .AsEnumerable()
                       .SelectMany((g,i)=>g.OrderBy(e=>e.UserName)
                                            .Select(e=>new PlayerRanking()
                                                       {
                                                          Player = e,
                                                          Rank = i + 1,
                                                          Score = e.Seniority
                                                       });

For pageNb = 1, the result seems correct, I have player1 to player10 with correct ranks :)
However for pageNb = 2 the Rank restarts from 1 again (i.e. player11 is marked as 1st instead of 9th).

Answer 1

Linq to Entities does not support method overloads with index. Also, since any usage of GroupBy returning sequence (and not simply keys + aggregates as in SQL GROUP BY) leads to 2 table access paths in the generated SQL, you could simply do that manually and use Count method for determining the rank (which should be the count of distinct Seniority values greater than the current).

So the working equivalent of your query could be like this:

var result = entities.Players
    .Select(p => new PlayerRanking
    {
        Player = p,
        Rank = entities.Players.Where(p1 => p1.Seniority > p.Seniority)
               .Select(p1 => p1.Seniority).Distinct().Count() + 1,
        Score = p.Seniority
    })
    .Where(r => r.Rank <= 10)
    .OrderBy(r => r.Rank)
    .ThenBy(r => r.Player.UserName)
    .ToPagedList(pageNb, 10);