Many developers encounters problems when trying to mimic the functionality of a Left Outer Join in LINQ. To be honest, the needed code is on first sight a bit strange. You can use a Group Join … Into .. and the DefaultIsEmpty() method. Let’s start with a regular (Inner) Join example. It uses the NorthWnd database and will retrieve all customers with at least one order.

This is the standard SQL which we have to use:

SELECT c.CustomerID, c.CompanyName,
       o.OrderID, o.OrderDate
FROM dbo.Customers c
JOIN dbo.Orders o
ON c.CustomerID = o.CustomerID
Order By o.OrderID

When you write this query in Visual Basic .NET with LINQ, the code would like this:

Dim result = From c In _context.Customers
             Join o In _context.Orders
             On c.CustomerID Equals o.CustomerID
             Order By o.OrderID
             Select New MyModel With {.CustomerID = c.CustomerID,
                                      .CompanyName = c.CompanyName,
                                      .OrderID = o.OrderID,
                                      .OrderDate = o.OrderDate}

I’ve created a class MyModel to project my data. The two Order properties (OrderID As Integer? and OrderDate As Date?) are marked as Nullable, because when using this model in cases where there is no order information, we don’t want to throw an exception.

Public Class MyModel
     
  Public Property CustomerID As String
  Public Property CompanyName As String
  Public Property OrderID As Integer?
  Public Property OrderDate As Date?
 
End Class

But what if we want to show also customers who didn’t ordered anything? So, what to do if you would like to use in SQL the Left Outer Join? This SQL will retrieve also customers without any orders:

	
SELECT c.CustomerID, c.CompanyName,
       o.OrderID, o.OrderDate
FROM dbo.Customers c
LEFT OUTER JOIN dbo.Orders o
ON c.CustomerID = o.CustomerID
Order By o.OrderID

To get the same results in LINQ, one option is to use a Group Join … Into statement, in combination with the DefaultIfEmpty() extension method. If you take a closer look at the LINQ statement below, you will notice that instead of a normal Join, we now use a Group Join. When using a Group Join you must also specify the ‘Into-part’. In this example I gave the group the alias ‘co’, but you can omit this. However, this is not the only difference with the original code. There is now an extra ‘From’ statement too. As you can see we have to use the Group.DefaultIfEmpty() extension method, or more precisely, the co.DefaultIfempty() statement. This method returns the elements of an IEnumerable(Of T), or a default valued singleton collection if the sequence is empty.

	
Dim result = From c In _context.Customers
             Group Join o In _context.Orders
             On c.CustomerID Equals o.CustomerID Into co = Group
             From o In co.DefaultIfEmpty()
             Order By o.OrderID
             Select New MyModel With {.CustomerID = c.CustomerID,
                                      .CompanyName = c.CompanyName,
                                      .OrderID = o.OrderID,
                                      .OrderDate = o.OrderDate}

For developers who likes the use of lambdas more, here is the same LINQ statement, without the use of a Group Join. It’s a bit shorter, which I prefer.

Dim result = From c In _context.Customers
             From o In _context.Orders.Where(Function(o) o.CustomerID = c.CustomerID).DefaultIfEmpty()
             Order By o.OrderID
             Select New MyModel With {.CustomerID = c.CustomerID,
                                      .CompanyName = c.CompanyName,
                                      .OrderID = o.OrderID,
                                      .OrderDate = o.OrderDate}

In the screenshot you see that we have also two customers without any orders. One note has to made… this will only work in your application running on .NET Framework 4.0 or higher.

leftouterjoinlinqvb