Clever Geek Handbook

Using LINQ to Observe with GroupBy and Sum aggregate

I have the following code that works great:

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 a group by ItemCategory and ItemAmount so I can see where I am spending most of my money, so I created a GroupBy request and ended up getting this:

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

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

Which gives me 2 errors;

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

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

And that's where I got stuck! How can I use the GroupBy and Sum aggregation function to get a list of my categories and associated costs in 1 LINQ query ?!

Any help / suggestions are greatly appreciated.

Mark

+3


source to share


2 answers


.GroupBy(category => category.ItemCategory);

returns an enumerated IGrouping object, where the key of each IGrouping is a separate ItemCategory value, and the value is a list of MoneySpent objects. Thus, you cannot simply remove these groupings in the ObservableCollection as you are 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);
+4


source


You can project each group into any of them (or better yet create a new type for this) with the properties you want:

var finalQuery = boughtItemsToday.GroupBy(category => category.ItemCategory);
                                 .Select(g => new 
                                  { 
                                     ItemCategory = g.Key, 
                                     Cost = g.Sum(x => x.ItemAmount)
                                  });


AsQueryable()

not required at all, since boughtItemsToday

equal IQuerable

. You can also just combine queries:

var finalQuery = BoughtItemDB.BoughtItems
                             .GroupBy(item => item.ItemCategory);
                             .Select(g => new 
                              { 
                                  ItemCategory = g.Key, 
                                  Cost = g.Sum(x => x.ItemAmount)
                              });
0


source






More articles:


All Articles