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?

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