Grouping, adding up then sorting with linq

Question

I have 3 tables Drivers, Races and DriverRaces.

public partial class Driver
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Driver()
    {
        this.DriverRaces = new HashSet<DriverRace>();
    }

    public int DriverID { get; set; }

    [DisplayName("Name")]
    public string DriverName { get; set; }

    [DisplayName("Number")]
    public string DriverNumber { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<DriverRace> DriverRaces { get; set; }
}

public partial class DriverRace
{
    public int DriverRaceID { get; set; }

    public int Points { get; set; }

    [DisplayName("Driver")]
    public Nullable<int> Driver_DriverID { get; set; }

    [DisplayName("Race")]
    public Nullable<int> Race_RaceID { get; set; }

    public virtual Driver Driver { get; set; }

    public virtual Race Race { get; set; }
}

public partial class Race
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Race()
    {
        this.DriverRaces = new HashSet<DriverRace>();
    }
    public int RaceID { get; set; }

    public System.DateTime RaceDate { get; set; }

    [DisplayName("Name")]
    public string RaceName { get; set; }

    [DisplayName("Class")]
    public string RaceClass { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<DriverRace> DriverRaces { get; set; }
}

There are 5 classes of races, a driver can be in more than one class, and can compete in multiple races. I am trying to write a linq statement to get the top 5 drivers of each class, and the sum of their total points from each race in that class.

I know how to select all rookie drivers and get their names and points

var drivers = DriverRaces.Where(d=>d.Points > 0 && d.Race.RaceClass == "Rookie");
foreach (var driver in drivers){
    Console.WriteLine("Driver Name is {0} points is {1}", driver.Driver.DriverName, driver.Points);
}

Which could return something like

Driver Name is Alexander points is 100
Driver Name is Mady points is 99
Driver Name is Blake points is 98
Driver Name is Alexander points is 100
Driver Name is Mady points is 99
Driver Name is Tom points is 98

But I want it to show

Driver Name is Alexander points is 200
Driver Name is Mady points is 198
Driver Name is Blake points is 98
Driver Name is Tom points is 98

I need help adding up each drivers totals and then sorting them.

Answer 1

To achieve the goal you will need to aggregate these results. There are two approaches one uses Aggregate other Select. Aggregate is more performant while Select is cleaner.

I've added a small example to indicate behavior:

class Program
{
    static void Main(string[] args)
    {
        List<Driver> driversFirstTable = new List<Driver>()
        {
            new Driver
            {
                DriverName = "John", DriverRaces = new Collection<DriverRace>
                {
                    new DriverRace { Points = 99, Category = "Rookie" },
                    new DriverRace { Points = 100, Category = "Rookie" },
                    new DriverRace { Points = 10, Category = "Mid" },
                    new DriverRace { Points = 99, Category = "Pro" },
                }
            },

            new Driver
            {
                DriverName = "Jack", DriverRaces = new Collection<DriverRace>
                {
                    new DriverRace { Points = 100, Category = "Rookie" },
                    new DriverRace { Points = 98, Category = "Rookie" },
                    new DriverRace { Points = 66, Category = "Mid" },
                    new DriverRace { Points = 100, Category = "Pro" },
                }
            },
            new Driver
            {
                DriverName = "Richard", DriverRaces = new Collection<DriverRace>
                {
                    new DriverRace { Points = 98, Category = "Rookie" },
                    new DriverRace { Points = 99, Category = "Rookie" },
                    new DriverRace { Points = 62, Category = "Mid" },
                    new DriverRace { Points = 98, Category = "Pro" },
                }
            },
            new Driver
            {
                DriverName = "Will", DriverRaces = new Collection<DriverRace>
                {
                    new DriverRace { Points = 97, Category = "Rookie" },
                    new DriverRace { Points = 97, Category = "Rookie" },
                    new DriverRace { Points = 61, Category = "Mid" },
                    new DriverRace { Points = 97, Category = "Pro" },
                }
            }
        };


        var rookieTop = driversFirstTable
            .Select(x => new DriverAggregated { Category = "Rookie", DriverName = x.DriverName, TotalPoints = x.DriverRaces.Where(y => y.Category == "Rookie").Sum(y => y.Points) })
            .OrderByDescending(x => x.TotalPoints)
            .Take(3);
        // Will is not in the list
        foreach (var driver in rookieTop)
        {
            Console.WriteLine($"Driver - {driver.DriverName} gathered {driver.TotalPoints} points.");
        }
        Console.Read();
    }
}

class DriverAggregated
{
    public string DriverName { get; set; }
    public int TotalPoints { get; set; }
    public string Category { get; set; }
}

public class Driver
{
    public string DriverName { get; set; }

    public virtual ICollection<DriverRace> DriverRaces { get; set; }
}

public class DriverRace
{
    public int Points { get; set; }
    public string Category { get; set; }
}

EDIT:

Another approach could be to use Aggregate function not as pretty but more performant:

        var topRookie = driversFirstTable.Select(x => x.DriverRaces.Aggregate(new DriverAggregated() { Category = "Rookie", DriverName = x.DriverName }, (seed, race) =>
          {
              if (race.Category == seed.Category)
                  seed.TotalPoints += race.Points;
              return seed;
          }))
         .OrderByDescending(x => x.TotalPoints)
         .Take(3);