SQL Rank over partition to C# Linq

Question

I am just starting to code in C# and need some direction with achieving the SQL Rank over partition within C# ( LINQ)

I have retrieved the following data from the database and have stored the SQL result in a data table within c#. the column 'Score' would have to be multiplied with a coefficient within the code and hence I cant perform a SQL Rank. I need to rank the SKU s within the partition [ID1, ID2] by descending order of score. The SQL Equivalent is :

Select ID1,ID2,SKU, Rank () over ( partition by ID1,ID2 order by score desc) as [Rank]

The result set

Once I have the rankings, I finally need to obtain the ranking distribution of a specific SKU (say s1) and the corresponding revenue distribution across the entire result.

Select SKU, 
       Rank, 
      count(*) 
      sum (revenue) 
from prev_result

SKU    RANK    OCCURENCE   REVENUE                                                             
s1       1        2          400                                                             
s1       2        xx         yy

Any help will be greatly beneficial.

It's fairly easy with C#, ut you need to have some ORM,or at least class that you can map yuor resultset. Having that, you can do `GroupBy` on result set, then each group is a collection and on each group you can call `OrderBy` combined with `First` method. You need to provide yuor C# code in order get further help. — Michał Turczyn, Dec 15 '19 at 09:16
If you have the info in a database, why not get the RANKing from the database? https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=be07ac0248b276bb50f4ab4005cb7066 — Luuk, Dec 15 '19 at 09:56

score 0 · Answer 1 · answered Dec 15 '19 at 11:36

Try following :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt1 = new DataTable();
            dt1.Columns.Add("ID1", typeof(string));
            dt1.Columns.Add("ID2", typeof(string));
            dt1.Columns.Add("SKU", typeof(string));
            dt1.Columns.Add("Revenue", typeof(int));
            dt1.Columns.Add("Score", typeof(int));

            dt1.Rows.Add(new object[] { "A", "a", "s1", 1000, 100});
            dt1.Rows.Add(new object[] { "A", "a", "s2", 2000, 200 });
            dt1.Rows.Add(new object[] { "A", "a", "s3", 1500, 150 });
            dt1.Rows.Add(new object[] { "B", "b", "s1", 3000, 55 });
            dt1.Rows.Add(new object[] { "B", "b", "s2", 200, 67 });
            dt1.Rows.Add(new object[] { "B", "b", "s4", 1000, 99 });

            var groups = dt1.AsEnumerable()
                .GroupBy(x => new { ID1 = x.Field<string>("ID1"), ID2 = x.Field<string>("ID2"),  })
                .OrderByDescending(x => x.Sum(y => y.Field<int>("Score")))
                .Select((x, i) => new { SKU = string.Join(",", x.Select(y => y.Field<string>("SKU")).OrderBy(y => y).Distinct()), Rank = i + 1, OCCURANCE = x.Count(), REVENUE = x.Sum(y => y.Field<int>("REVENUE")) })
                .ToList();

        }
    }
}

SQL Rank over partition to C# Linq

1 Answers1