I am trying to group a list by 2 fields (Category, then Vendor), but at different values. If the category is "01", sum all the cost. If the category is not "01", group by the category, and then by the vendor.
Some demo data:
List<MyItem> myItemList = new List<MyItem>();
myItemList.Add(new MyItem{Vendor="Ven1", Cost=100, Category="01"});
myItemList.Add(new MyItem{Vendor="Ven2", Cost=10, Category="02"});
myItemList.Add(new MyItem{Vendor="Ven3", Cost=50, Category="02"}));
myItemList.Add(new MyItem{Vendor="Ven2", Cost=40, Category="01"});
myItemList.Add(new MyItem{Vendor="Ven2", Cost=20, Category="01"});
myItemList.Add(new MyItem{Vendor="Ven3", Cost=30, Category="02"});
myItemList.Add(new MyItem{Vendor="Ven1", Cost=10, Category="03"});
What I currently am doing:
List<MyItem> groupedItems = myItemList.GroupBy(a=> new {a.Category, a.Vendor})
.Select(b=> new MyItem{
Vendor = b.First().Vendor,
Cost = b.Sum(c => c.Cost),
Category = b.First().Category
}).ToList();
What I am trying to do (aka my best guess):
List<MyItem> groupedItems = myItemList.GroupBy(a=> new {a.Category.Where(z=>z.Category.Equals("01:)), a.Vendor})
.Select(b=> new MyItem{
Vendor = b.First().Vendor,
Cost = b.Sum(c => c.Cost),
Category = b.First().Category
}).ToList();
Desired Result:
Category = "01", Vendor = "N/A ", Cost = 160
Category = "02", Vendor = "Ven2", Cost = 10
Category = "02", Vendor = "Ven3", Cost = 80
Category = "03", Vendor = "Ven1", Cost = 10
The GroupBy
returns an anonymous type with 2 fields. The values inside the returned object can be anything. In this case they can be altered if the category is "01": myItemList.GroupBy(a=> new {a.Category, Vendor= a.Category == "01" ? null : a.Vendor})
To keep in line with your current code, the property is named Vendor, but can be any name.
In your current code:
List<MyItem> groupedItems = myItemList
.GroupBy(a => new {a.Category, Vendor = a.Category == "01" ? null : a.Vendor})
.Select(b => new MyItem
{
Vendor = b.First().Vendor,
Cost = b.Sum(c => c.Cost),
Category = b.First().Category
})
.ToList();
Or with a slight alteration, instead of the First()
value of a group, you can reuse the key (and implement the N\A
in one go)
List<MyItem> groupedItems = myItemList
.GroupBy(a=> new {a.Category, Vendor= a.Category == "01" ? "N/A" : a.Vendor})
.Select(b=> new MyItem{
Vendor = b.Key.Vendor,
Cost = b.Sum(c => c.Cost),
Category = b.Key.Category
}).ToList();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With