GroupBy with Linq to Objects

Question

I am strugling with a GroupBy for Linq. I guess I can't do it in one expression but have no clue how to solve the problem.

I have the following 2 classes:

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;} }

I have a collection of Shipments that all have a collection of Invoice. Here is an example.

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} } } };

Now I want to group by the following:

Shipment.PortOfOrigin, Shipment.PortOfDestionation, Shipment.Invoices.InvoicePeriod.

So I want the result like this

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

Is this even possible to do a group like this where I want to group on the Invoices.InvoicePeriod?

+1 for the example data and the example of the results you want. It's rare to get a question which is so clear and well defined :-) — Doctor Jones
– Doctor Jones, Commented Aug 9, 2011 at 9:00

Doctor Jones · Accepted Answer · 2011-08-11 08:07:28Z

UPDATE: I've updated my answer to include the number of Shipments. This requires the shipment to have an ID field.

The following will do the trick:

//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() };

Here's the output:

enter image description here

UPDATE: As promised, here's a complete working example:

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(); }

Thanks. Yes I thought about flatterned the data, but I was thinking that I needed to create a new class to do that, but I don't have to do that if I use your first linq statement.
Is there a way to get the right number of shipments also included here. If I only add the g.Count() to it then I will get the same numbers as I have invoices for each group (since it's flattende out). So how could I only get the numbers of shipments for this example above it should be: 2,2 and 1. And not 3,2 and 1
@Magnus I've updated my answer to give the number of shipments (an upvote would be a nice reward ;-))
I tried it out in my world and it didn't work... I addedd this line: SummarizedShipments = g.Distinct(s => s.ShipmentUniqueIdentifier).Count() and got the exception: Cannot convert lambda expression to type 'System.Collections.Generic.IEqualityComparer<AnonymousType#1>' because it is not a delegate type. !?!?!?

Collectives™ on Stack Overflow

GroupBy with Linq to Objects

1 Answer 1

8 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Related