使用 Linq to Object 进行 GroupBy

Question

我正在为 Linq 的 GroupBy 苦苦挣扎。我想我无法用一种表达方式做到这一点，但不知道如何解决这个问题。

我有以下 2 个类：

public class Shipment {
  public string PortOfOrigin{get;set;}
  public string PortOfDestination{get;set;}
  public ICollection<Invoice> Invoices{get;set;}
}

public class Invoice{
  public string InvoicePeriod {get;set;}
  public decimal Amount {get;set;}
}

我有一组发货，所有发货都有一组发票。这是一个例子。

List<Shipment> shipments = new List<Shipment>
{
    new Shipment { PortOfOrigin = "USLOS", PortOfDestionation = "UKLON", 
        Invoices = new List<Invoice>
        {
            new Invoice{InvoicePeriod = "201106", Amount = 1000},
            new Invoice{InvoicePeriod = "201106", Amount = 2000},
            new Invoice{InvoicePeriod = "201107", Amount = 1000}
        }
    },
    new Shipment { PortOfOrigin = "USLOS", PortOfDestionation = "UKLON", 
        Invoices = new List<Invoice>
        {
            new Invoice{InvoicePeriod = "201106", Amount = 3000},
            new Invoice{InvoicePeriod = "201107", Amount = 2000}
        }
    },
    new Shipment { PortOfOrigin = "USDAL", PortOfDestionation = "UKLON", 
        Invoices = new List<Invoice>
        {
            new Invoice{InvoicePeriod = "201106", Amount = 3000}
        }
    }
};

现在我想按以下内容进行分组：

Shipment.PortOfOrigin、Shipment.PortOfDestionation、Shipment.Invoices.InvoicePeriod。

所以我想要这样的结果

PortOfOrigin    PortOfDestination   InvoicePeriod   Amount
------------------------------------------------------------------------
USLOS           UKLON               201106          6000
USLOS           UKLON               201107          3000
USDAL           UKLON               201106          3000

是否可以在 Invoices.InvoicePeriod 上进行分组？

Answer 1

更新：我已更新我的答案以包含发货数量。这要求货件具有 ID 字段。

---

下面的代码可以解决这个问题：

//first flatten the data so it's easier to manipulate
var query = from s in shipments
            from i in s.Invoices
            select new
            {
                s.ShipmentUniqueIdentifier,
                s.PortOfOrigin,
                s.PortOfDestination,
                i.InvoicePeriod,
                i.Amount
            };

//group the data as desired
var grouping = query.GroupBy(q => new { q.PortOfOrigin, q.PortOfDestination, q.InvoicePeriod });

//finally sum the amounts
var results = from g in grouping
              select new
              {
                  g.Key.PortOfOrigin,
                  g.Key.PortOfDestination,
                  g.Key.InvoicePeriod,
                  Amount = g.Select(s => s.Amount).Sum(),
                  NumberOfShipments = g.Select(s => s.ShipmentUniqueIdentifier).Distinct().Count()
              };

这是输出：

更新： 正如所承诺的，这是一个完整的工作示例：

public class Shipment {
  public Guid ShipmentUniqueIdentifier{get;set;}
  public string PortOfOrigin{get;set;}
  public string PortOfDestination{get;set;}
  public ICollection<Invoice> Invoices{get;set;}
}

public class Invoice {
  public string InvoicePeriod {get;set;}
  public decimal Amount {get;set;}
}

List<Shipment> shipments = new List<Shipment>
{
    new Shipment { PortOfOrigin = "USLOS", PortOfDestination = "UKLON", ShipmentUniqueIdentifier = Guid.NewGuid(),
        Invoices = new List<Invoice>
        {
            new Invoice{InvoicePeriod = "201106", Amount = 1000},
            new Invoice{InvoicePeriod = "201106", Amount = 2000},
            new Invoice{InvoicePeriod = "201107", Amount = 1000}
        }
    },
    new Shipment { PortOfOrigin = "USLOS", PortOfDestination = "UKLON", ShipmentUniqueIdentifier = Guid.NewGuid(),
        Invoices = new List<Invoice>
        {
            new Invoice{InvoicePeriod = "201106", Amount = 3000},
            new Invoice{InvoicePeriod = "201107", Amount = 2000}
        }
    },
    new Shipment { PortOfOrigin = "USDAL", PortOfDestination = "UKLON", ShipmentUniqueIdentifier = Guid.NewGuid(), 
        Invoices = new List<Invoice>
        {
            new Invoice{InvoicePeriod = "201106", Amount = 3000}
        }
    }
};

void Main()
{
    //first flatten the data so it's easier to manipulate
    var query = from s in shipments
                from i in s.Invoices
                select new
                {
                    s.ShipmentUniqueIdentifier,
                    s.PortOfOrigin,
                    s.PortOfDestination,
                    i.InvoicePeriod,
                    i.Amount
                };

    //group the data as desired
    var grouping = query.GroupBy(q => new { q.PortOfOrigin, q.PortOfDestination, q.InvoicePeriod });

    //finally sum the amounts
    var results = from g in grouping
                  select new
                  {
                      g.Key.PortOfOrigin,
                      g.Key.PortOfDestination,
                      g.Key.InvoicePeriod,
                      Amount = g.Select(s => s.Amount).Sum(),
                      NumberOfShipments = g.Select(s => s.ShipmentUniqueIdentifier).Distinct().Count()
                  };

    //output the results
    results.Dump();
}