Practical .NET

Getting All the Results in LINQ with Methods

Peter follows up on advice for joining collections with LINQ and getting all of the results, but this time, he uses the method-based syntax.

• By Peter Vogel
• 04/07/2017

A little while back, I did a column on how to join two collections in LINQ to generate queries like, "Get me all of the customers, with their salesorders (if they have any)," or, "Get me all of the customers who have no salesorders." Generating these queries requires something like the SQL outer join and I walked through constructing that query in the column.

I got some feedback from readers of that column who use the LINQ method-based syntax (I used the SQL-like syntax in the column). This tip addresses that feedback by showing the method-based syntax, again in both Visual Basic and C# (though, I suspect that most programmers could translate from one syntax to the other).

Joining Collections with LINQ
Your first step is to call the GroupJoin method from the collection you want all the objects from (that's Customers in my example). You must pass the GroupJoin method four parameters:

• The collection you want to join to (the collection that might be missing items; SalesOrders, in this case)
• A lambda expression specifying the property from the collection you called GroupJoin from that you want to use in the join (the Id from the Customer object)
• A lambda expression specifying the property from the collection in the first parameter that you want to use in the join (the CustomerId from the SalesOrder object)
• The equivalent of a select statement that's used to build the object that you want returned

That final lambda expression is passed one object from the initial collection (Customers, in my case) and a collection of all the matching objects from the other collection (SalesOrders, in my case). In this example, I'm using that last lambda expression to build an anonymous object with two properties:

• One called Key that holds the CustomerId
• One called SalesOrders that holds all the SalesOrders

The resulting code looks like this in Visual Basic:

Dim = db.Customers.GroupJoin(db.SalesOrders,
                             Function(c) c.Id,
                             Function(s) s.CustomerId,
                             Function(c, s) New With {.Key = c, .SalesOrders = s})

In C#, it looks like this:

var mos = db.Customers.GroupJoin(db.Salesorders,
                                 c => c.Id,
                                 s => s.CustomerId,
                                 (c, s) => new { Key = c.Id, SalesOrders = s })

That will give you a result. Specifically, you'll end up with a collection of objects with each object having a property holding single CustomerId and another property holding the collection of SalesOrder objects for that CustomerId. That may not be the result you want.

Getting a Flattened Result
What you probably want is a collection of objects with each object having the property with the single Customer Id and another property holding a single SalesOrder object (the Customer Id will repeat across multiple objects, once for each SalesOrder). For this kind of transformation ("flattening" a result), you need to call the SelectMany method.

A warning: My SelectMany call is a little ugly, for two reasons. First, I need to call the DefaultIfAnyMethod on the SalesOrders collection -- this method will provide a null/Nothing value when there's no matching SalesOrder for a Customer (this prevents LINQ dropping Customers because there's no matching object). Second, I need to use an Immediate If to handle working with that null value when there's a Customer with no matching SalesOrder. In the following code, I've used the name ("mo") to refer to the objects returned from my GroupJoin method.

The SelectMany method requires two parameters (both lambda expressions). The first parameter specifies where matching objects are drawn from. In this case that's the property holding my SalesOrders collection. I call the DefaultIfEmpty method here to supply a value, even when the property is empty because there were no matching salesorders for the Customer.

In C#, the code looks like this:

var res = mos.SelectMany(mo => mo.SalesOrders.DefaultIfEmpty(),

In Visual Basic, like this:

Dim res = mos.SelectMany(Function(mo) mo.SalesOrders.DefaultIfEmpty(),

The second parameter is like a Select method: It's where you specify the object you'll return from the SelectMany call. The lambda expression you pass here must accept two parameters: each object from the collection you're processing (in this case, that's the object from my GroupJoin) and the matching object from the first parameter.

I return an anonymous object consisting of the Customer Id and the OrderId from the SalesOrder selected in the first parameter. To handle the null/Nothing values I get from that first parameter, I use an Immediate If to supply a default value.

Putting that all together, my SelectMany looks like this in C#:

Dim res = mos.SelectMany(mo => mo.SalesOrders.DefaultIfEmpty(),
                        (c, s) => new { c.Key.Id, OrderId = s == null ? "Not Found" : s.Id.ToString() });

and this in Visual Basic:

Dim res = mos.SelectMany(Function(mo) mo.SalesOrders.DefaultIfEmpty(),
                         Function(c, s) New With {c.Key.Id, .OrderId = If(s Is Nothing, "Not Found", s.Id.ToString)})

If you want to do it in one LINQ statement (in order, I suspect, to frighten the next programmer), it looks like this in C#:

var res = db.Customers.GroupJoin(db.Salesorders,
                                 c => c.Id,
                                 s => s.CustomerId,
                                (c, s) => new { Key = c, SalesOrders = s }).
                       SelectMany(mo => mo.SalesOrders.DefaultIfEmpty(),
                                 (c, s) => new { c.Key.Id, OrderId = s == null ? "Not Found" : s.Id.ToString() });

and this in Visual Basic:

Dim = db.Customers.GroupJoin(db.SalesOrders,
                             Function(c) c.Id,
                             Function(s) s.CustomerId,
                             Function(c, s) New With {.Key = c, .SalesOrders = s}).
                    SelectMany(Function(mo) mo.SalesOrders.DefaultIfEmpty(),
                               Function(c, s) New With {c.Key.Id, .OrderId = If(s Is Nothing, "Not Found", s.Id.ToString)})

(Degree of fright is dependent on the next programmer's level of expertise, of course ...)