Ben Ng Ben Ng -4 years ago 66
C# Question

C# LINQ - Ranking Multiple Criteria

For example, I have the following list of sales personnel, and their scores for two Key Performance Indicators (KPI):

SalesmanID KPI1 KPI2
Alice 20 4
Betty 50 6
Cindy 40 8
Doris 70 2
Emily 30 3


First, we rank the sales personnel based on KPI1 in descending order as follows.

SalesmanID KPI1 KPI1_Rank
Doris 70 1
Betty 50 2
Cindy 40 3
Emily 30 4
Alice 20 5


Next, we rank the sales personnel based on KPI2 in descending order as follows.

SalesmanID KPI2 KPI2_Rank
Cindy 8 1
Betty 6 2
Alice 4 3
Emily 3 4
Doris 2 5


Finally, we put them together to compute the Overall_Rank as the average of KPI1_Rank and KPI2_Rank (i.e. Overall_Score = (KPI1_Rank + KPI2_Rank) / 2)

SalesmanID KPI1_Rank KPI2_Rank Overall_Score
Alice 5 3 4
Betty 2 2 2
Cindy 3 1 2
Doris 1 5 6
Emily 4 4 4


We then proceed to rank the sales personnel according to the Overall_Score in descending order.

SalesmanID Overall_Score Overall_Rank
Doris 6 1
Alice 4 2 (Tie)
Emily 4 2 (Tie)
Cindy 2 4 (Tie)
Betty 2 4 (Tie)


Would this be possible with C# LINQ?

Answer Source

Long code, but it is for educational purposes.

using System.Linq;

namespace ConsoleApplication
{
    class Program
    {
        static void Main (string[] args)
        {
            var salesmanList = new Salesman[]
            {
                new Salesman ("Betty", 50, 6),
                new Salesman ("Cindy", 40, 8),
                new Salesman ("Doris", 70, 2),
                new Salesman ("Emily", 30, 3),
            };


            var rankByKPI1 = salesmanList.OrderByDescending (x => x.KPI1)
                                         .Select ((x, index) => new SalesmanKpiRank (x, index + 1))
                                         .ToArray (); // for debugging only

            var rankByKPI2 = salesmanList.OrderByDescending (x => x.KPI2)
                                         .Select ((x, index) => new SalesmanKpiRank (x, index + 1))
                                         .ToArray (); // for debugging only


            var overallScoreQuery = from salesman in salesmanList

                                    let  kpi1rank = rankByKPI1.Single (x => x.Salesman.Equals (salesman)).Rank
                                    let  kpi2rank = rankByKPI2.Single (x => x.Salesman.Equals (salesman)).Rank

                                    select new SalesmanOverallScore (salesman, kpi1rank, kpi2rank);


            var rankByOverallScore = overallScoreQuery.OrderByDescending (x => x.Score)
                                                      .Select ((x , index) => new { SalesmanOverallScore = x, OverallRank = index + 1});

            var result = rankByOverallScore.ToArray ();               
        }
    }


    class Salesman
    {
        public Salesman (string id, int kpi1, int kpi2)
        {
            ID   = id;
            KPI1 = kpi1;
            KPI2 = kpi2;
        }

        public string ID   { get; }
        public int    KPI1 { get; }
        public int    KPI2 { get; }

        public override bool Equals (object obj) =>ID == ((Salesman) obj).ID; // put some logic here

        public override string ToString () => $"{ID} KPI1 = {KPI1}, KPI2 = {KPI2}";
    }


    class SalesmanKpiRank
    {
        public SalesmanKpiRank (Salesman salesman, int rank)
        {
            Salesman = salesman;
            Rank     = rank;
        }

        public Salesman Salesman { get; }
        public int      Rank     { get; }

        public override string ToString () => $"{Salesman} KPI Rank = {Rank}"; // kpi1 or kpi2
    }


    class SalesmanOverallScore
    {
        public SalesmanOverallScore (Salesman salesman, int kpi1rank, int kpi2rank)
        {
            Salesman = salesman;
            KPI1Rank = kpi1rank;
            KPI2Rank = kpi2rank;
        }

        public Salesman Salesman { get; }
        public int      KPI1Rank { get; }
        public int      KPI2Rank { get; }

        public double Score => (KPI1Rank + KPI2Rank) / 2d;

        public override string ToString () => $"{Salesman.ID} {Score}";
    }
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download