DENSE_RANK() using LINQ without intermediary steps

Question

I have the SQL SERVER RANK() function working by following the URL:

https://stackoverflow.com/questions/27469838/is-there-a-function-in-entity-framework-that-translates-to-the-rank-function-i?noredirect=1&lq=1

And the following answer seems to do the job:

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

I figured if I can't directly get the DENSE_RANK(), I can watch when the RANK() changes and try to select a DENSE_RANK() based on that

var denseRankCounter = 0;

Dictionary<int, int> denseRankWithRank = new Dictionary<int, int>();

denseRankWithRank.Add(customersByCountry[0].RANK, ++denseRankCounter);

for (int x = 1; x < customersByCountry.Count; x++)
{
    if (customersByCountry[x] != customersByCountry[x - 1])
    {
        if (!denseRankWithRank.ContainsKey(customersByCountry[x].RANK))
        {
            denseRankWithRank.Add(customersByCountry[x].RANK, ++denseRankCounter);
        }
    }
}

Then apply these results back to the result set,

var denseCustomersByCountry = customersByCountry.Select(c => new
                            {
                                DENSE_RANK = denseRankWithRank[c.RANK],
                                CountryID = c.CountryID
                                // ...  ,any other required 
                            }).ToList();

Although this is working somewhat, it seems to be super cumbersome.
I was wondering if there is an easier way without the Dictionary or any intermediary steps.

score 1 · Accepted Answer · answered Mar 09 '20 at 15:56

1

Try following :

            var customersByCountry = db.Customers
                .GroupBy(c => c.CountryID)
                .OrderByDescending(x => x.Count())
                .Select((g,rank) => new { CountryID = g.Key, Count = g.Count(), Rank = rank });

answered Mar 09 '20 at 15:56

jdweng

33,250
2
15
20

Thanks! i got it to work using this approach, though i can't group by on my actual db results as it was very slow. I have put the details in an answer – heyNow Mar 09 '20 at 16:36

heyNow · Answer 2 · 2020-03-10T12:21:22.520

This wasnt too hard.
I got it to work using jdweng's approach above so I marked that as the answer.
In my actual db results groupBy was really slow so i created a subset of the fields, then did the actual grouping.

var customers = db.Customers.OrderBy(c=>c.CountryID);

    var ranks = customers.Select(c => new 
                                  { 
                                        c.CountryID, 
                                        c.Count, 
                                        RANK = customers.Count(c2 => c2.Count > c.Count) + 1
                                    });


    var denseCustomersByCountry = ranks.GroupBy(r => r.RANK)
                                            .Select((r, idx) => new
                                            {
                                                RANK = r.Key,
                                                COUNT = r.Count(),
                                                DENSE_RANK = idx + 1,
                                                CountryID = r.FirstOrDefault().CountryID
                                            })
                                            .ToList();

Don't you need an orderby to get rank? Otherwise, you just have an index number. — jdweng, Mar 09 '20 at 20:54

DENSE_RANK() using LINQ without intermediary steps

2 Answers2