c# Linq Rank like TSQL Partition BY Multiple Columns

user_10823641 0
Viewed 45

I have a List<T> and trying to rank using Linq as I can with TSQL.

TSQL: RANK() OVER ( PARTITION BY [Time], [Filter] ORDER BY [Speed] Desc) AS speedrank

After the ranks are calculated trying to update the ranked column in the List<T> with the calculated rank.

I could write the data to a sql table and query using tsql but would prefer to use the strong typed list I have in the application if it's possible.

The Rank has to take in to account duplicates Speed values based on the partitioning, so sorting and incrementing row number does not work as expected.

Updated: List in MainForm.cs

private List<Ranking> _rankingList = new List<Ranking>();

Add to list.

            var advancedsearchranking = new Ranking
            {
                Course = course,
                RaceDate = racedate,
                RaceTime = racetime,
                RaceDayRaceNo = racenumber,
                RaceDayHorseNo = horse.HNo,
                Filter = "Going",
                Horse = horse.HorseName,
                WinPercentage = $"{winpercentage} ({wins}/{runs})",
                Positions = sbpos.ToString(),
                SpeedFigures = sbspeedratings.ToString(),
                LastSpeedFigure = lastspeedfigure,
                Average = Math.Round((double)average, 0),
                BSPs = sbbsp.ToString()
            };

            _rankingList.Add(advancedsearchranking);

Class:

public class Ranking
{
    public string Course { get; set; }
    public DateTime RaceDate { get; set; }
    public TimeSpan RaceTime { get; set; }
    public int? RaceDayRaceNo { get; set; }
    public int? RaceDayHorseNo { get; set; }
    public string Filter { get; set; }
    public string Horse { get; set; }
    public string WinPercentage { get; set; }
    public string Positions { get; set; }
    public string SpeedFigures { get; set; }
    public int? LastSpeedFigure { get; set; }
    public int LastSpeedRank { get; set; }
    public double? Average { get; set; }
    public virtual string BSPs { get; set; }
    public virtual double[] BSPSparkLine { get; set; }
    public double? MasterAverage { get; set; }

}

I'm try to partition by Filter property and rank by LastSpeedFigure Desc, so highest figure is ranked 1 or joint 1st if two have the same value.

Regards,

Neil

dense-rank rank window-functions linq c#
1 Answers

You should be able to use the following code (sorry I haven't tested any of this).

var list = _rankingList.OrderBy(r => r.Filter).ThenByDescending(r => r.LastSpeedFigure);

int rank = 1;
int rowNumber = 1;
list[0].LastSpeedRank = 1;  // set the first item

for (var i = 1; i < list.Count; i++)
{
    if(list[i].Filter != list[i - 1].Filter)  // reset numbering
    {
        rank = 1;
        rowNumber = 1;
    }
    else
    {
        rowNumber++;    // row-number always counts up
        if(list[i].LastSpeedFigure != list[i - 1].LastSpeedFigure)
            rank = rowNumber;    // only change rank if not tied
    }
    list[i].LastSpeedRank = rank;
}

You can also implement an IEnumerable extension to do this

public T WithRank<T>(
    this IEnumerable<T> source,
    Func<T, T, bool> partitioning,
    Func<T, T, bool> ordering,
    Action<T, int> setRank)
{
    using var enumer = source.GetEnumerator();
    if(!enumer.MoveNext())
        yield break;

    var previous = enumer.Current;
    setRank(previous, 1);
    yield return previous;

    int rank = 1;
    int rowNumber = 1;


    while(enumer.MoveNext())
    {
        if(!partitioning(enumer.Current,previous))  // reset numbering
        {
            rank = 1;
            rowNumber = 1;
        }
        else
        {
            rowNumber++;    // row-number always counts up
            if(ordering(enumer.Current, previous))
                rank = rowNumber;    // only change rank if not tied
        }
        setRank(previous, rank);
        yield return enumer.Current;
    }
}

Use it like this

// the list must be pre-sorted by partitioning values then ordering values.

var list = _rankedList
    .OrderBy(r => r.Filter)
    .ThenByDescending(r => r.LastSpeedFigure)
    .WithRanking(
        (a, b) => a.Filter == b.Filter,
        (a, b) => a.LastSpeedFigure == b.LastSpeedFigure,
        (o, rank) => { o.LastSpeedRank = rank; })
    .ToList();

You can implement ROW_NUMBER by just using the rowNumber variable and removing the conditional rank = rowNumber; statement. You can implement DENSE_RANK by changing that line to rank++; and ignoring rowNumber.

user_148689970
Related