Using Linq to group and sum with multiple columns
Using Linq to group and sum with multiple columns
(OP)
I am trying to figure out how to take some data from a datatable or class that could have 5 columns or could have 10 or 20 columns. This was created pivoting a table which gave me an unknown number of columns. But I can't figure out how to use Linq to handle this. Here is a sample set of code I have been playing with.
The list<> objects are set up to handle the different number of columns:
The IEnumberable<> values is the grouped detail that is put into grouped item. The count and sum fields are the ones that are dynamic. But this can get unwieldy. I would need one for each possibility and have a different linq statement for each one.
I thought about have a list<> object something like:
But couldn't think about how I would do the select statement since most of the time there would be only 7 or 8.
Any way to change the Linq query to handle this?
Thanks,
Tom
CODE
var books = new List<Book>(); // Add test data books.Add(new Book { Author = "Douglas Adams", Title = "The Hitchhiker's Guide to the Galaxy", Genre = "Fiction", Price = 159.95M }); books.Add(new Book { Author = "Scott Adams", Title = "The Dilbert Principle", Genre = "Fiction", Price = 23.95M }); books.Add(new Book { Author = "Douglas Coupland", Title = "Generation X", Genre = "Fiction", Price = 300.00M }); books.Add(new Book { Author = "Walter Isaacson", Title = "Steve Jobs", Genre = "Biography", Price = 219.25M }); books.Add(new Book { Author = "Michael Freeman", Title = "The Photographer's Eye", Genre = "Photography", Price = 195.50M }); // Group the books by Genre var booksGrouped = from b in books group b by b.Genre into g select new Group<string, Book> { Key = g.Key, Values = g }; List<Group<string, Book>> bookList = booksGrouped.ToList(); // Group the books by Genre count number of books var booksGroupedCounted = from b in books group b by b.Genre into g select new GroupCount<string, Book, int> { Key = g.Key, Values = g, Count = g.Count() }; List<GroupCount<string, Book, int>> booksCounted = booksGroupedCounted.ToList(); //Group the books by Genre count number of books and sum the prices var booksGroupedCountedSummed = from b in books group b by b.Genre into g select new GroupCountSum<string, Book, int, decimal> { Key = g.Key, Values = g, Count = g.Count(), Summed = g.Sum(x => x.Price) }; List<GroupCountSum<string, Book, int, decimal>> booksCountedSummed = booksGroupedCountedSummed.ToList();
The list<> objects are set up to handle the different number of columns:
CODE
public class Group<K, T> { public K Key; public IEnumerable<T> Values; } public class GroupCount<K, T, C> { public K Key; public IEnumerable<T> Values; public int Count; } public class GroupCountSum<K, T, C, D> { public K Key; public IEnumerable<T> Values; public int Count; public decimal Summed; }
The IEnumberable<> values is the grouped detail that is put into grouped item. The count and sum fields are the ones that are dynamic. But this can get unwieldy. I would need one for each possibility and have a different linq statement for each one.
I thought about have a list<> object something like:
CODE
public class GroupCountSum<K, T, C1, C2, C3, C4, C5, C6, C7, C8, C9, C10, C11, C12, C13, C14, C15, C16, C17, C18, C19, C20, C21> { public K Key; public IEnumerable<T> Values; public int Count1; public int Count2; public int Count3; public int Count4; public int Count5; ... }
But couldn't think about how I would do the select statement since most of the time there would be only 7 or 8.
Any way to change the Linq query to handle this?
Thanks,
Tom