How to use LINQ to group by one field, sort within that grouping, and sum values for that combination?

Question

I could come up with a Rube Goldberg-esque way to do this, but I reckon it can probably be accomplished much more elegantly and concisely using LINQ:

With a generic list of instances of this class:

public class PlatypusData
{
    public String MemberName { get; set; }
    public String CompanyName { get; set; }
    public String ReasonDescription { get; set; }
    public String TransactionType { get; set; }
    public int QtyOrdered { get; set; }
    public int QtyShipped { get; set; }
}

I want to condense it down to rows or records of unique values for the first four class members (MemberName, CompanyName, ReasonDescription, TransactionType).

e.g., with a generic list defined like this:

List<PlatypusData> _platypusDataList = new List<PlatypusData>();

...contents of this generic list could be data such as:

MemberName = "Ollie of Burkina Faso"
CompanyName = "Riley's Rugbeaters"
ReasonDescription = "Credit - Failed to deliver item (for credit adjustments only)"
TransactionType = "CREDIT ITEM"
QtyOrdered = 1
QtyShipped = 1

MemberName = "Oscar of Redlands"
CompanyName = "Herrera's Empty Frames"
ReasonDescription = [blank]
TransactionType = "REGULAR ITEM - ON SPEC"
QtyOrdered = 7
QtyShipped = 6

MemberName = "Ollie of Burkina Faso"
CompanyName = "Gatherer Mechanics"
ReasonDescription = "Credit - Product Quality (for credit adjutments only)"
TransactionType = "CREDIT ITEM"
QtyOrdered = 3
QtyShipped = 3

MemberName = "Ollie of Burkina Faso"
CompanyName = "Riley's Rugbeaters"
ReasonDescription = [blank]
TransactionType = "REGULAR ITEM - ON SPEC"
QtyOrdered = 42
QtyShipped = 42

MemberName = "Oscar of Redlands"
CompanyName = "Fidelity Fire"
ReasonDescription = "Credit - Failed to deliver item (for credit adjustments only)"
TransactionType = "REGULAR ITEM - ON SPEC"
QtyOrdered = 5
QtyShipped = 4

MemberName = "Oscar of Redlands"
CompanyName = "Herrera's Empty Frames"
ReasonDescription = [blank]
TransactionType = "REGULAR ITEM - ON SPEC"
QtyOrdered = 7
QtyShipped = 7

MemberName = "Ollie of Burkina Faso"
CompanyName = "Gatherer Mechanics"
ReasonDescription = [blank]
TransactionType = "REGULAR ITEM - ON SPEC"
QtyOrdered = 11
QtyShipped = 10

MemberName = "Oscar of Redlands"
CompanyName = "Fidelity Fire"
ReasonDescription = [blank]
TransactionType = "REGULAR ITEM - ON SPEC"
QtyOrdered = 6
QtyShipped = 7

MemberName = "Oscar of Redlands"
CompanyName = "Fidelity Fire"
ReasonDescription = [blank]
TransactionType = "REGULAR ITEM - ON SPEC"
QtyOrdered = 8
QtyShipped = 8

MemberName = "Ollie of Burkina Faso"
CompanyName = "Gatherer Mechanics"
ReasonDescription = [blank]
TransactionType = "REGULAR ITEM - ON SPEC"
QtyOrdered = 12
QtyShipped = 12

...from which I want to condense it down to one row for each unique combination of MemberName+CompanyName+ReasonDescription+TransactionType, with QtyOrdered and QtyShipped for the combined row being the sum of those values, and the data returned from the LINQ operation being ordered by MemberName, then CompanyName, then ReasonDescription, such as:

MemberName = "Ollie of Burkina Faso"
CompanyName = "Gatherer Mechanics"
ReasonDescription = [blank]
TransactionType = "REGULAR ITEM - ON SPEC"
QtyOrdered = 23
QtyShipped = 22

MemberName = "Ollie of Burkina Faso"
CompanyName = "Gatherer Mechanics"
ReasonDescription = "Credit - Product Quality (for credit adjutments only)"
TransactionType = "CREDIT ITEM"
QtyOrdered = 3
QtyShipped = 3

MemberName = "Ollie of Burkina Faso"
CompanyName = "Riley's Rugbeaters"
ReasonDescription = [blank]
TransactionType = "REGULAR ITEM - ON SPEC"
QtyOrdered = 42
QtyShipped = 42

MemberName = "Ollie of Burkina Faso"
CompanyName = "Riley's Rugbeaters"
ReasonDescription = "Credit - Failed to deliver item (for credit adjustments only)"
TransactionType = "CREDIT ITEM"
QtyOrdered = 1
QtyShipped = 1


MemberName = "Oscar of Redlands"
CompanyName = "Fidelity Fire"
ReasonDescription = [blank]
TransactionType = "REGULAR ITEM - ON SPEC"
QtyOrdered = 14
QtyShipped = 15

MemberName = "Oscar of Redlands"
CompanyName = "Fidelity Fire"
ReasonDescription = "Credit - Failed to deliver item (for credit adjustments only)"
TransactionType = "REGULAR ITEM - ON SPEC"
QtyOrdered = 5
QtyShipped = 4

MemberName = "Oscar of Redlands"
CompanyName = "Herrera's Empty Frames"
ReasonDescription = [blank]
TransactionType = "REGULAR ITEM - ON SPEC"
QtyOrdered = 14
QtyShipped = 13

I reckon some combination of GroupBy, OrderBy, and Sum is needed, but don't know just how to do it (in a LINQ/non-Goldbergesque way).

Answer 1

I reckon some combination of GroupBy, OrderBy, and Sum is needed

You're right. This should be what you need:

var result = _platypusDataList
    .GroupBy(i => new { i.MemberName, i.CompanyName, i.ReasonDescription, i.TransactionType })
    .Select(g => new
        {
            g.Key.MemberName,
            g.Key.CompanyName,
            g.Key.ReasonDescription,
            g.Key.TransactionType,
            QtyOrdered = g.Sum(i => i.QtyOrdered),
            QtyShipped = g.Sum(i => i.QtyShipped)
        })
    .OrderBy(i => i.MemberName)
    .ThenBy(i => i.CompanyName)
    .ThenBy(i => i.ReasonDescription)
    .ToList();

The code above returns a list of anonymous type. If you want a List<PlatypusData>, you need to modify the Select:

.Select(g => new PlatypusData
    {
        MemberName = g.Key.MemberName,
        CompanyName = g.Key.CompanyName,
        ReasonDescription = g.Key.ReasonDescription,
        TransactionType = g.Key.TransactionType,
        QtyOrdered = g.Sum(i => i.QtyOrdered),
        QtyShipped = g.Sum(i => i.QtyShipped)
    })