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.

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.

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.

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.

(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