Getting the DENSE_RANK() in C# over multiple splits of data

Question

I have a dataset that is built gradually in parts, and as each part is done, I'm associating the entries with their DENSE_RANK() with the following code (source: implement dense rank with linq):

 aQueryable.GroupBy(x => x)
       .Where(g => g.Any())
       .OrderBy(g => g.Key.SortOrder1)
       .ThenBy(g => g.Key.SortOrder2)
       .ThenBy(g => g.Key.SortOrder3)
            .Select((g, i) =>
             {
               ++i;
               foreach (var x in g)
               {
                        x.DenseRank = i;
               }
                    return g;
              }).Select(g => g.Key)

SQL equivalent: DENSE_RANK() OVER ( ORDER BY SortOrder1, SortOrder2, SortOrder3 )

However, the DenseRank that I'm computing here doesn't match the DENSE_RANK() I get in SQL once the entire dataset is written. I suspect this is because I'm computing my DENSE_RANK() on a subset of the full dataset.

Is there any way I can compute the same DENSE_RANK() as SQL without waiting for my entire dataset to finish populating first?

You need to group by the keys: `.GroupBy(x => new { x.Key.SortOrder1, x.Key.SortOrder2, x.Key.SortOrder3 })`. In your code you are grouping by `x`, which will just put every item in it's own group. The select still needs to be done client side though. — Yitz, Sep 20 '22 at 09:33
Are you trying to do that with EF Core or just list of objects? — Svyatoslav Danyliv, Sep 20 '22 at 10:11

score 0 · Answer 1 · answered Sep 20 '22 at 10:14

If you wanted to stream the results through an IEnumerable, you can OrderBy on the database and then write your own select, checking for rank:

var lastSort1 = default(int);
var lastSort2 = default(int);
var lastSort3 = default(int);
var firstRun = true;
var rank = 1;
iqueryable
   .OrderBy(i => i.SortOrder1)
   .ThenBy(i => i.SortOrder2)
   .ThenBy(i => i.SortOrder3)
   .AsEnumerable()
    .Select(i =>
     {
         if (!firstRun && (lastSort1 != i.SortOrder1 || lastSort2 != i.SortOrder2 || lastSort3 != i.SortOrder3))
         {
             rank++;
         }
         firstRun = false;
         lastSort1 = i.SortOrder1;
         lastSort2 = i.SortOrder2;
         lastSort3 = i.SortOrder3;
         i.DenseRank = rank;
         return i;
     });

As soon as the first data comes through, you'll start receiving items through the IEnumerable, but eventually it will all be materialized.

Getting the DENSE_RANK() in C# over multiple splits of data

1 Answers1