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
Talk To Other Members