How to aggregate and SUM EntityFramework fields with multiple joins

Question

I am able to produce a set of results that are desirable, but I have the need to group and sum of these fields and am struggling to understand how to approach this.

In my scenario, what would be the best way to get results that will:

1. Have a distinct [KeyCode] (right now I get many records, same KeyCode but different occupation details)
2. SUM wage and projection fields (in same query)

Here is my LINQ code:

private IQueryable<MyAbstractCustomOccupationInfoClass> GetMyAbstractCustomOccupationInfoClass(string[] regionNumbers)
{
    //Get a list of wage data
    var wages = _db.ProjectionAndWages
        .Join(
            _db.HWOLInformation,
            wages => wages.KeyCode,
            hwol => hwol.KeyCode,
            (wages, hwol) => new { wages, hwol }
        )
        .Where(o => regionNumbers.Contains(o.hwol.LocationID))
        .Where(o => o.wages.areaID.Equals("48"))
        .Where(o => regionNumbers.Contains(o.wages.RegionNumber.Substring(4))); //regions filter, remove first 4 characters (0000)

    //Join OccupationInfo table to wage data, for "full" output results
    var occupations = wages.Join(
                _db.OccupationInfo,
                o => o.wages.KeyCode,
                p => p.KeyCode,
                (p, o) => new MyAbstractCustomOccupationInfoClass
                {
                    KeyCode = o.KeyCode,
                    KeyTitle = o.KeyTitle,
                    CareerField = o.CareerField,
                    AverageAnnualOpeningsGrowth = p.wages.AverageAnnualOpeningsGrowth,
                    AverageAnnualOpeningsReplacement = p.wages.AverageAnnualOpeningsReplacement,
                    AverageAnnualOpeningsTotal = p.wages.AverageAnnualOpeningsTotal,
                });

    //TO-DO: How to Aggregate and Sum "occupations" list here & make the [KeyCode] Distinct ?

    return occupations;
}

I am unsure if I should perform the Grouping mechanism on the 2nd join? Or perform a .GroupJoin()? Or have a third query?

Answer 1

var occupations = _db.OccupationInfo.GroupJoin(
            wages,
            o => o.KeyCode,
            p => p.wages.KeyCode,
            (o, pg) => new MyAbstractCustomOccupationInfoClass {
                KeyCode = o.KeyCode,
                KeyTitle = o.KeyTitle,
                CareerField = o.CareerField,
                AverageAnnualOpeningsGrowth = pg.Sum(p => p.wages.AverageAnnualOpeningsGrowth),
                AverageAnnualOpeningsReplacement = pg.Sum(p => p.wages.AverageAnnualOpeningsReplacement),
                AverageAnnualOpeningsTotal = pg.Sum(p => p.wages.AverageAnnualOpeningsTotal),
            });