Converting SQL to LINQ, Part 9: Full Outer Join (Bill Horst)

Converting SQL to LINQ, Part 9: Full Outer Join (Bill Horst)

  • Comments 9

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

Leave a Comment
  • Please add 2 and 6 and type the answer here:
  • Post
  • Si quieres aprender LINQ para Visual Basic 2008, quizás te interesen los siguientes artículos que el

  • Si quieres aprender LINQ para Visual Basic 2008, quizás te interesen los siguientes artículos que el

  • Your DBA will nog be happy with this solution.

  • hi , my name is  saleem

    can you answer my qestions please

    1- i made a simple media player to play sound and video i just want to know how can i get all file names

    i selected and pressed enter

    i tried a startupnextinstance event

    and i got all file names but this was a very slow way and doesn't work well when you have alot of files (more than 100)

    i think i should use somthing exist in windows

    i think  somthing related to user32.dll or another file

    please can you tell me what the way is

    ( i want the same way that is used in windows media player to open many files)

    2-i can drag and drop files from explorer to my play list using dragdrop event but this doesn't work when i drag files from openfiledialog to my playlist

    my listbox doesn't feel dragdrop event

    3- how can i next the song using nextmediabutton

    while my application is minimized and i am working  on another program

    4- sorry if my English is not very good

    and thank you for every thing

  • Can this be translated from c#?  

    var q3 = from c in dcAW.Customers

            join o in dcAW.SalesOrderHeaders on c.CustomerID equals o.CustomerID

            into temp

            from t in temp.DefaultIfEmpty()

            select c;

  • ここでは、このシリーズの 以前の投稿 ( 英語 ) をお読みになっていることを前提としています。前提とする状況はパート 8 と同じで、前回の続きとなる、完全外部結合の変換についてお話します。これが理想的な方法でないことはわかっていますので、将来の

  • On behalf of the Microsoft Visual Basic team, I'd like to wish all of our readers a happy & healthy

  • On behalf of the Microsoft Visual Basic team, I'd like to wish all of our readers a happy & healthy

  • application was a nice thank you to our business will be wound

Page 1 of 1 (9 items)