Converting SQL to LINQ, Part 9: Full Outer Join (Bill Horst)
This post assumes you’ve read the previous posts in this series. It makes the same assumptions as Part 8, and I will pick up where I left off, covering Full Outer Join. Once again, I recognize this isn’t an ideal situation, so if you’d like to see better support of Full Outer Join in a future VB release, you can file a suggestion from our product feedback site.
A Full Outer Join is basically like the union of the results of a Left Outer Join and Right Outer Join. Records are joined together based on common fields, and all records with no match are also displayed, whether in the “left” or “right” table.
|
SQL |
|
SELECT Contact.ContactName, Shipment.OrderDate
FROM CustomerTable Contact
FULL OUTER JOIN OrderTable Shipment
ON Contact.CustomerID = Shipment.CustomerID
|
Using the same data as my last post, this query would give me results like the following:
|
Results: |
|
ContactName OrderDate
John Doe NULL
Bill Horst 3/25/1982
Bill Horst 3/13/2005
Jane Doe 9/29/2007
NULL 1/31/2008 |
Like Left and Right Join, Full Outer Join is not supported directly with LINQ, but you can write a query to produce the same results. Currently, the simplest way to do this is to concatenate three subqueries together.
The first query provides the results where the “right” table has a null value.
|
VB |
|
From Contact In CustomerTable _
Group Join Shipment In OrderTable _
On Contact.CustomerID Equals Shipment.CustomerID _
Into HaveMatch = Any() _
Where Not HaveMatch _
Select Contact.ContactName, OrderDate = NothingDate
Results:
{ContactName = "John Doe", OrderDate = Nothing}
|
The second query provides the results of an Inner Join.
|
VB |
|
From Contact In CustomerTable _
Join Shipment In OrderTable _
On Contact.CustomerID Equals Shipment.CustomerID _
Select Contact.ContactName, Shipment.OrderDate
Results:
{ContactName = "Bill Horst", OrderDate = #3/25/1982#}
{ContactName = "Bill Horst", OrderDate = #3/13/2005#}
{ContactName = "Jane Doe", OrderDate = #9/29/2007#}
|
The third query provides the results where the “left” table has a null value.
|
VB |
|
From Shipment In OrderTable _
Group Join Contact In CustomerTable _
On Contact.CustomerID Equals Shipment.CustomerID _
Into HaveMatch = Any() _
Where Not HaveMatch _
Select ContactName = NothingString, Shipment.OrderDate
Results:
{ContactName = Nothing, OrderDate = #1/31/2008#}
|
When the results are put together, we get a full outer join.
|
VB |
|
(From Contact In CustomerTable _
Group Join Shipment In OrderTable _
On Contact.CustomerID Equals Shipment.CustomerID _
Into HaveMatch = Any() _
Where Not HaveMatch _
Select Contact.ContactName, OrderDate = NothingDate) _
.Concat(From Contact In CustomerTable _
Join Shipment In OrderTable _
On Contact.CustomerID Equals Shipment.CustomerID _
Select Contact.ContactName, Shipment.OrderDate) _
.Concat(From Shipment In OrderTable _
Group Join Contact In CustomerTable _
On Contact.CustomerID Equals Shipment.CustomerID _
Into HaveMatch = Any() _
Where Not HaveMatch _
Select ContactName = NothingString, Shipment.OrderDate)
Results:
{ContactName = "John Doe", OrderDate = Nothing}
{ContactName = "Bill Horst", OrderDate = #3/25/1982#}
{ContactName = "Bill Horst", OrderDate = #3/13/2005#}
{ContactName = "Jane Doe", OrderDate = #9/29/2007#}
{ContactName = Nothing, OrderDate = #1/31/2008#}
|
Hopefully this is helpful to some. As noted above, I realize this is not an ideal way to implement a Full Outer Join. If you’d like to see support for a Full Outer Join with simple syntax in a future VB release, please file a suggestion from our product feedback site.
Also, I don't have much more material to post about LINQ, so if anyone would like to see a post on a different topic, I'm open to suggestions.
- Bill Horst, VB IDE Test
