c# - Is there a function in Entity Framework that translates to the RANK() function in SQL?

Question

Let's say I want to rank my customer database by country. In SQL I would write:

select CountryID, CustomerCount = count(*), 
       [Rank] = RANK() over (order by count(*) desc)
from Customer

Now I want to write this in Entity Framework:

var ranks = db.Customers
  .GroupBy(c => c.CountryID)
  .OrderByDescending(g => g.Count())
  .Select((g, index) => new {CountryID = g.Key, CustomerCount = g.Count, Rank = index+1});

There are two problems with this:

1. It doesn't work. EF throws a System.NotSupportedException; evidently there's no SQL translation for the overload of .Select() that uses the row number; you would have to pull everything into memory with a .ToList() in order to be able to call this method; and
2. Even if you run the method in local memory, it doesn't handle equal rankings the way the RANK() function does in SQL, i.e. they should have an equal rank, and then the following item skips to the original order.

So how should I do this?

Answer 1

AFAIK Rank() has no builtin function in LINQ. This answer uses your approach, but it seems to work for them. Here's how you could use it:

var customersByCountry = db.Customers
    .GroupBy(c => c.CountryID);
    .Select(g => new { CountryID = g.Key, Count = g.Count() });
var ranks = customersByCountry
    .Select(c => new 
        { 
            c.CountryID, 
            c.Count, 
            Rank = customersByCountry.Count(c2 => c2.Count > c.Count) + 1
        });