Include all fields after grouping and summing using c# linq

Question

I am basically trying to group the allocationsGrouped list by productname and summing the Emv field. If you below i have assigned the grouping to var a. Unfortunately the after grouping it only contains ProductName and EmvSum as thats the only two fields added in the Select statement I need all the fields below listed in the foreach (var ac in allocationsGrouped). I would need to loop through a and not allocationsGrouped. So it will be foreach (var ac in a). How do I get the other fields after grouping and summing.

 private static void CreateHierarchy(string manStratName, IEnumerable<FIRMWIDE_MANAGER_ALLOCATION> allocationsGrouped, List<FirmWideAllocationsViewModel> result)
        {

            var a = allocationsGrouped
                .Where(product => !string.IsNullOrEmpty(product.PRODUCT_NAME))
                .GroupBy(product => product.PRODUCT_NAME)
                .Select(group => new
                {
                    ProductName = group.Key, // this is the value you grouped on - the ProductName
                    EmvSum = group.Sum(x => x.EMV),
                });



            var item = new FirmWideAllocationsViewModel();
            item.Hierarchy = new List<string>();

            item.Hierarchy.Add(manStratName);
            result.Add(item);




            foreach (var ac in allocationsGrouped)
            {
                var item2 = new FirmWideAllocationsViewModel();
                item2.Hierarchy = new List<string>();
                item2.Hierarchy.Add(manStratName);
                item2.Hierarchy.Add(ac.PRODUCT_NAME + "#" + ac.MANAGER_FUND_ID + ac.PRODUCT_ID + ac.EMV);
                item2.FirmID = ac.FIRM_ID;
                item2.FirmName = ac.FIRM_NAME;
                item2.ManagerStrategyID = ac.MANAGER_STRATEGY_ID;
                item2.ManagerStrategyName = ac.MANAGER_STRATEGY_NAME;
                item2.ManagerAccountClassID = ac.MANAGER_ACCOUNTING_CLASS_ID;
                item2.ManagerAccountingClassName = ac.MANAGER_ACCOUNTING_CLASS_NAME;
                item2.ManagerFundID = ac.MANAGER_FUND_ID;
                item2.ManagerFundName = ac.MANAGER_FUND_NAME;
                item2.Nav = ac.NAV;
                item2.EvalDate = ac.EVAL_DATE.HasValue ? ac.EVAL_DATE.Value.ToString("MMM dd, yyyy") : string.Empty;
                item2.ProductID = ac.PRODUCT_ID;
                item2.ProductName = ac.PRODUCT_NAME;
                item2.UsdEmv = Math.Round((decimal)ac.UsdEmv);
                item2.GroupPercent = ac.GroupPercent;
                item2.WeightWithEq = ac.WEIGHT_WITH_EQ;
                result.Add(item2);
            }
        }

Added the count and filter on a specific product that has duplicate on the proposed solution . The outer count shows one while the inner count shows 2

 var a = allocationsGrouped
            .Where(product => !string.IsNullOrEmpty(product.PRODUCT_NAME) && product.PRODUCT_NAME == "EnTrust Global Activist Fund LP")
            .GroupBy(product => product.PRODUCT_NAME)
            .Select(group => new
            {
                ProductName = group.Key, // this is the value you grouped on - the ProductName
                EmvSum = group.Sum(x => x.EMV),
                Items = group
            });

        var b = a;

        var item = new FirmWideAllocationsViewModel();
        item.Hierarchy = new List<string>();

        item.Hierarchy.Add(manStratName);
        result.Add(item);

        foreach (var ac in b)
        {
            var count = b.Count();
            var productName = ac.ProductName;
            var emvSum = ac.EmvSum;
            foreach (var elem in ac.Items)
            {
                var count1 = ac.Items.Count();
                var item2 = new FirmWideAllocationsViewModel();
                item2.Hierarchy = new List<string>();
                item2.Hierarchy.Add(manStratName);
                item2.Hierarchy.Add(elem.PRODUCT_NAME);
                item2.FirmID = elem.FIRM_ID;
                item2.FirmName = elem.FIRM_NAME;
                item2.ManagerStrategyID = elem.MANAGER_STRATEGY_ID;
                item2.ManagerStrategyName = elem.MANAGER_STRATEGY_NAME;
                item2.ManagerAccountClassID = elem.MANAGER_ACCOUNTING_CLASS_ID;
                item2.ManagerAccountingClassName = elem.MANAGER_ACCOUNTING_CLASS_NAME;
                item2.ManagerFundID = elem.MANAGER_FUND_ID;
                item2.ManagerFundName = elem.MANAGER_FUND_NAME;
                item2.Nav = elem.NAV;
                item2.EvalDate = elem.EVAL_DATE.HasValue ? elem.EVAL_DATE.Value.ToString("MMM dd, yyyy") : string.Empty;
                item2.ProductID = elem.PRODUCT_ID;
                item2.ProductName = elem.PRODUCT_NAME;
                item2.UsdEmv = Math.Round((decimal)elem.UsdEmv);
                item2.GroupPercent = elem.GroupPercent;
                item2.WeightWithEq = elem.WEIGHT_WITH_EQ;
                result.Add(item2);
            }
        }


        return result;

A group is a two dimensional array [group][items]. So following will get everything : .Select(group => new { ProductName = group.Key, EmvSum = group.Sum(x => x.EMV), group = group }); You would have to enumerate through the group using group.Select(z => ....) — jdweng, Mar 27 '19 at 16:53
Somthing like [this](https://stackoverflow.com/a/7325306/2590375)? In that example you will get a list of `key` + `Cars`, cars will have all properties. — nilsK, Mar 27 '19 at 16:54
Did you mean var a = allocationsGrouped .Where(product => !string.IsNullOrEmpty(product.PRODUCT_NAME)) .GroupBy(product => product.PRODUCT_NAME) .Select(group => new { ProductName = group.Key, // this is the value you grouped on - the ProductName EmvSum = group.Sum(x => x.EMV), group }); var b = a.Select(x => x.group); — Tom, Mar 27 '19 at 17:11
In your code `var count = b.Count()` is the number of distinct product names, whereas `var count1 = ac.Items.Count()`, is the number of products with the same product names. Why would you expect them to match? — Vikhram, Mar 27 '19 at 19:42
I am doing a loop through ac.Items which means my result object will contain non distinct object. I am looking at having distinct result object. What the use of populating the result object with same product names. For e.g if the original list has product1 with value 200 and product1 with value 100, the result object should contain product1 with value 300. Thats my ultimate objective — Tom, Mar 27 '19 at 21:14
I did try your sample .Your structure looks right from respect to ProductName and EmvSum but not items. I hope you understood what I am looking for. The list should contain all fields and should be unique — Tom, Mar 27 '19 at 23:09
First things first, Tom, if you are referring to me, you should put my name as @Vikhram, otherwise I won't be notified.Now to your question, `if the original list has product1 with value 200 and product1 with value 100, the result object should contain product1 with value 300`, that's exactly what my code does. `Your structure looks right from respect to ProductName and EmvSum but not items.`, what does that mean? Are you expecting all `Items` to be distinct? — Vikhram, Mar 29 '19 at 16:48
Can you provide minimal test data, with your expected result, and what my current code fails to achieve? — Vikhram, Mar 29 '19 at 16:49

Vikhram · Answer 1 · 2019-03-27T18:29:00.597

The IGrouping<TKey, TElement> type is an IEnumerable<TElement>. What it means is that you just need to select the group for further use. So the relevant part of teh code would be

var a = allocationsGrouped
    .Where(product => !string.IsNullOrEmpty(product.PRODUCT_NAME))
    .GroupBy(product => product.PRODUCT_NAME)
    .Select(group => new {
        ProductName = group.Key, // this is the value you grouped on - the ProductName
        EmvSum = group.Sum(x => x.EMV),
        Items = group
    });

Notice the Items = group in the Select

And you can access the grouped items now as below

foreach (var ac in a) {
    var productName = ac.ProductName;
    var emvSum = ac.EmvSum;
    foreach (var elem in ac.Items) {
        // elem is your FIRMWIDE_MANAGER_ALLOCATION in this group
    }
}

TEST You can test this using the following code

var allocationsGrouped = new List<Product>() {
    new Product { PRODUCT_NAME = "T1", EMV = 1 },
    new Product { PRODUCT_NAME = "T2", EMV = 2 },
    new Product { PRODUCT_NAME = "T1", EMV = 3 },
    new Product { PRODUCT_NAME = "T2", EMV = 4 },
    new Product { PRODUCT_NAME = "T1", EMV = 5 },
};

You will notice that, this will structure the data as below

"a": 
[
    [
        "ProductName": "T1"
        "EmvSum": 9,
        "Items": 
        [
            [{"EMV": 1, "PRODUCT_NAME": "T1"}],
            [{"EMV": 3, "PRODUCT_NAME": "T1"}],
            [{"EMV": 5, "PRODUCT_NAME": "T1"}]
        ], 
    ], 
    [
        "ProductName": "T2"
        "EmvSum": 6,
        "Items": 
        [
            [{"EMV": 2, "PRODUCT_NAME": "T2"}],
            [{"EMV": 4, "PRODUCT_NAME": "T2"}],
        ], 
    ] 
]

Hi Vikram, thanks but there is one issue here. Items does not contain the filtered list. I was expecting the Items contains the same count as the summed one — Tom, Mar 27 '19 at 18:18
I had tried to check the duplicate product like this and got two instances in the items var a = allocationsGrouped .Where(product => !string.IsNullOrEmpty(product.PRODUCT_NAME) && product.PRODUCT_NAME == "EnTrust Global Activist Fund LP") .GroupBy(product => product.PRODUCT_NAME) .Select(group => new { ProductName = group.Key, // this is the value you grouped on - the ProductName EmvSum = group.Sum(x => x.EMV), Items = group }); — Tom, Mar 27 '19 at 18:38

Include all fields after grouping and summing using c# linq

1 Answers1