Practical .NET

Grouping in LINQ with Methods

Peter follows up -- yet again – on a column on how to group results with LINQ using the SQL-like syntax with the same solution using the method-based syntax. And, no, you're not seeing double.

• By Peter Vogel
• 04/14/2017

A little while back, I did a column on how to group results in LINQ to handle problems like "Your users want to see the 10 customers who have bought the most from you along with their related sales orders." As I pointed out, this isn't an unusual query: Sometimes users don't want the record: they want a group of records that meet a condition. That's were the ability of LINQ to create groups of objects that share a common value is useful -- for this problem, for example, you want to group all the salesorders that share a CustomerId.

In the original column, I used the SQL-like syntax and got some feedback from readers who wanted to see the method-based syntax (and, yes, this is different from the last column based on feedback from readers). So that's what this tip shows. As in the column, I'll do both Visual Basic and C#. However, for the method-based syntax, that isn't really necessary because the two solutions are almost identical in the two languages.

In the method-based syntax, you use the GroupBy method passing a lambda expression that returns the property you want to group by. Here's the C# code: var OrdersByCustomer = db.Salesorders.GroupBy(so => so.CustomerId). And here's the Visual Basic code:

Dim OrdersByCustomer = db.Salesorders.GroupBy(Function(so) so.CustomerId).

The GroupBy method returns a collection of IGrouping objects so, in the subsequent method calls, it's a collection of IGrouping objects that you'll be working with. An IGrouping object is a collection of objects (of SalesOrder objects, in this case). Unlike other collections, it has an additional property called Key that holds the shared value (in this case, that's the CustomerId that all the SalesOrders share).

Just to demonstrate that an IGrouping object can be treated like any other collection, in the following LINQ statement, I've thrown in a Where method to select only those collections that have at least five SalesOrders in them by checking the Count of items in each IGrouping collection. Presumably, this weeds out "customers we're not interested in" because they don't do enough business with us. Here's the Visual Basic code:

Dim OrdersByCustomer = db.Salesorders.GroupBy(Function(so) so.CustomerId).
                                      Where(Function(tos) tos.Count() > 5).

And here's the C# code:

var OrdersByCustomer = db.Salesorders.GroupBy(so => so.CustomerId).
                                      Where(tos => tos.Count() > 5).

Finally, I need a Select method to create the object that the user wants. This example gives back a collection of anonymous objects that have a:

• CustomerId property (pulled from the IGrouping collection's Key property)
• TotaledValue property (derived by summing the IGrouping collection's members)
• A list of the SalesOrders for that CustomerId (derived by doing a ToList on the IGrouping collection's members)

And, of course, I need to take only the first 10 objects.

Here's the code in C#:

var OrdersByCustomer = db.Salesorders.GroupBy(so => so.CustomerId).
                                      Where(tos => tos.Count() > 5).
                                      Select(tos => new {CustomerId = tos.Key, 
                                                         TotaledValue = tos.Sum(s => 
                                                           s.TotalValue),
                                                         Orders = tos.ToList()}).Take(10);

And then in Visual Basic:

Dim OrdersByCustomer = db.SalesOrders.GroupBy(Function(so) so.CustomerId).
                                        Where(Function(tos) tos.Count() > 5).
                                        Select(Function(tos) New With {.CustomerId = tos.Key,
                                                                       .TotaledValue = 
                                                                         tos.Sum(
                                                                         Function(s) 
                                                                         s.TotalValue),
                                                                       .Orders = 
                                                                         tos.ToList()}).Take(10)

Next time, we return to the regularly scheduled blog of random cool ways to do stuff without involving code.