I am trying to sort data in a table based on Ranks. Not sure how to achieve this in linq's

Input Table:

Rank Table:

Based on the Input & Rank table, I am expecting the output as below:

CodePudding user response：

Assuming your classes look like this:

public class CodeRank{
  public CodeRank(string code, int rank){
    this.Code = code;
    this.Rank = rank;
  }

  public string Code {get;set;}
  public int Rank {get;set;}
}

public class StateCode{
  public StateCode(string state, string code){
    this.State = state;
    this.Code = code;
  }
  public string State {get;set;}
  public string Code {get;set;}

}

You could use the below query. If all codes have a rank, you could put the rank table into a dictionary and use it to find the rank instead of searching for it each iteration.

var result = stateCodes.Select(stateCode => new {State = stateCode.State, Code = stateCode.Code, Rank = ranks.SingleOrDefault(y => y.Code == stateCode.Code)?.Rank ?? 100})
        .OrderBy(x => x.Rank)
        .Select(x => new StateCode(x.State, x.Code))
        .ToList();
    

CodePudding user response：

If your classes are defined as follows:

public class StateAndCodes
{
    public string State { get; set; }
    public int? Codes { get; set; }
}

public class RankAndCodes
{
    public int Rank { get; set; }
    public int Codes { get; set; }
}

, your input and rank tables may be represented as follows:

var input = new List<StateAndCodes>
{
    new StateAndCodes { State = "GA" },
    new StateAndCodes { State = "GA", Codes = 30223 },
    new StateAndCodes { State = "GA", Codes = 30223 },
    new StateAndCodes { State = "GA", Codes = 30223 },
    new StateAndCodes { State = "GA", Codes = 30115 },
    new StateAndCodes { State = "GA", Codes = 30115 },
    new StateAndCodes { State = "GA", Codes = 30115 },
    new StateAndCodes { State = "GA", Codes = 30112 },
    new StateAndCodes { State = "GA", Codes = 30093 },
    new StateAndCodes { State = "GA", Codes = 31088 },
    new StateAndCodes { State = "GA", Codes = 31088 }, // Assuming 30188 was a typo in original post?
    new StateAndCodes { State = "GA", Codes = 30363 },
    new StateAndCodes { State = "GA", Codes = 30363 },
    new StateAndCodes { State = "GA", Codes = 30363 },
};

var rank = new List<RankAndCodes>
{
    new RankAndCodes { Rank = 1, Codes = 30115 },
    new RankAndCodes { Rank = 2, Codes = 30363 },
    new RankAndCodes { Rank = 3, Codes = 31088 },
    new RankAndCodes { Rank = 4, Codes = 30223 },
};

You could then

• group the Codess in your input table by Codes
• sort the Codes groups so that groups that have no Codes (Codes == null) come after groups that do have a Codes value
• then, sort the groups that do have a Codes value according to their matching Rank value in the rank table (if a matching Rank value exists; if not, sort last)
• flattening the results (the groupings are dissolved)
• converting the results to a list

as follows:

var output = input
    .GroupBy(entry => entry.Codes)
    .OrderByDescending(gr => gr.Key.HasValue)
    .ThenBy(gr => 
        rank.SingleOrDefault(r => r.Codes == gr.Key)?.Rank ?? 
        rank.Max(r => r.Rank)   1)
    .SelectMany(gr => gr.Select(entry => entry))
    .ToList();