C# LINQ query to sum rows by condition and add to existing list

Question

I have the following List of data.

 var data = new List<SummaryTabData>()
        {
            new SummaryTabData(){ LP = "LP_1", assetClass = "Asset_CFD_Long", avgSlippagePts = 54.23} ,
            new SummaryTabData(){ LP = "LP_1", assetClass = "Asset_FX_Long", avgSlippagePts = 61.41} ,
            new SummaryTabData(){ LP = "LP_1", assetClass = "Asset_FX_Short", avgSlippagePts = 11.48} ,
            new SummaryTabData(){ LP = "LP_1", assetClass = "Asset_CFD_Short", avgSlippagePts = 63.51} ,

            new SummaryTabData(){ LP = "LP_2", assetClass = "Asset_FX_Long",  avgSlippagePts = 6.51} ,            
            new SummaryTabData(){ LP = "LP_2", assetClass = "Asset_FX_Short", avgSlippagePts = 13.51}
        };

My goal is to add an additional row for every pair of Asset_CFD_long/Asset_CFD_short and Asset_FX_Long/Asset_FX_Short for every type of LP. Sometimes there might be either 2 or 4 asset classes like you can see in the example snip

The final result should look something like this.

var data = new List<SummaryTabData>()
            {
                new SummaryTabData(){ LP = "LP_1", assetClass = "Asset_CFD_Long", avgSlippagePts = 54.23} ,
                new SummaryTabData(){ LP = "LP_1", assetClass = "Asset_CFD_Short", avgSlippagePts = 63.51} ,

                // that is the new row added to the collection for LP_1 CFD
                new SummaryTabData(){ LP = "LP_1", assetClass = "CFD_Total", avgSlippagePts = 117.74} ,

                new SummaryTabData(){ LP = "LP_1", assetClass = "Asset_FX_Long", avgSlippagePts = 61.41} ,
                new SummaryTabData(){ LP = "LP_1", assetClass = "Asset_FX_Short", avgSlippagePts = 11.48} ,

                // that is the new row added to the collection for LP_1 FX
                new SummaryTabData(){ LP = "LP_1", assetClass = "FX_Total", avgSlippagePts = 72.89} ,

                new SummaryTabData(){ LP = "LP_2", assetClass = "Asset_FX_Long",  avgSlippagePts = 6.51} ,
                new SummaryTabData(){ LP = "LP_2", assetClass = "Asset_FX_Short", avgSlippagePts = 13.51},

                // that is the new row added to the collection for LP_2 FX
                new SummaryTabData(){ LP = "LP_2", assetClass = "FX_Total", avgSlippagePts = 20.02},
            };

The data is not sorted. Do you know for some kind of LINQ query that can do this or I should go with the traditional for loop approach ?

Answer 1

The requirements are a little strange, however the assumption is the LP groups can be ordered in any way

Updated

var groups = data
   .Select(x => new { Asset = x.assetClass.Split("_")[1], Summary = x })
   .GroupBy(x => new { x.Asset, x.Summary.LP });

var list = new List<SummaryTabData>();

foreach (var group in groups)
{
   list.AddRange(group.Select(x => x.Summary).ToList());

   list.Add(new SummaryTabData()
   {
      assetClass = $"{group.Key.Asset}_Total",
      LP = group.Key.LP,
      avgSlippagePts = group.Sum(x => x.Summary.avgSlippagePts)
   });

}

foreach (var item in list)
   Console.WriteLine($"{item.LP}, {item.assetClass}, {item.avgSlippagePts}");

Output

LP_1, Asset_FX_Short, 11.48
LP_1, Asset_FX_Long, 61.41
LP_1, FX_Total, 72.89
LP_1, Asset_CFD_Short, 63.51
LP_1, Asset_CFD_Long, 54.23
LP_1, CFD_Total, 117.74
LP_2, Asset_FX_Short, 13.51
LP_2, Asset_FX_Long, 6.51
LP_2, FX_Total, 20.02

In all likely hood there are other ways to do this

Full Demo Here