Using LINQ on ObservableCollection with GroupBy and Sum aggregate

Question

I have the following block of code which works fine;

var boughtItemsToday = (from DBControl.MoneySpent
            bought in BoughtItemDB.BoughtItems
            select bought);

BoughtItems = new ObservableCollection<DBControl.MoneySpent>(boughtItemsToday);

It returns data from my MoneySpent table which includes ItemCategory, ItemAmount, ItemDateTime.

I want to change it to group by ItemCategory and ItemAmount so I can see where I am spending most of my money, so I created a GroupBy query, and ended up with this;

var finalQuery = boughtItemsToday.AsQueryable().GroupBy(category => category.ItemCategory); 

BoughtItems = new ObservableCollection<DBControl.MoneySpent>(finalQuery);

Which gives me 2 errors;

Error 1 The best overloaded method match for 'System.Collections.ObjectModel.ObservableCollection.ObservableCollection(System.Collections.Generic.List)' has some invalid arguments

Error 2 Argument 1: cannot convert from 'System.Linq.IQueryable>' to 'System.Collections.Generic.List'

And this is where I'm stuck! How can I use the GroupBy and Sum aggregate function to get a list of my categories and the associated spend in 1 LINQ query?!

Any help/suggestions gratefully received.

Mark

Answer 1

.GroupBy(category => category.ItemCategory); returns an enumerable of IGrouping objects, where the key of each IGrouping is a distinct ItemCategory value, and the value is a list of MoneySpent objects. So, you won't be able to simply drop these groupings into an ObservableCollection as you're currently doing.

Instead, you probably want to Select each grouped result into a new MoneySpent object:

var finalQuery = boughtItemsToday
    .GroupBy(category => category.ItemCategory)
    .Select(grouping => new MoneySpent { ItemCategory = grouping.Key, ItemAmount = grouping.Sum(moneySpent => moneySpent.ItemAmount);

BoughtItems = new ObservableCollection<DBControl.MoneySpent>(finalQuery);