Converting SQL to LINQ, Part 8: Left/Right Outer Join (Bill Horst)

Converting SQL to LINQ, Part 8: Left/Right Outer Join (Bill Horst)

  • Comments 18

This post assumes you’ve read the previous posts in this series.

         

After my post on joins, I’ve had some questions about outer joins.  As you can see in part 6, VB9 doesn’t have smooth support for Left or Right join.  I showed how to get similar functionality with Group Join, but I’ve looked into the subject some more, and want to give everyone a more extensive explanation of how to get left, right, and full outer join results accurately with VB LINQ.  Admittedly, these queries are very complex for a relatively simple concept, but unfortunately the VB team didn’t have the resources to implement these features in VB9.  If this support is important to you, you can submit a suggestion to the Visual Studio team to have it included for a future release on our feedback page.

 

I’ll cover Left and Right Outer Join in this post and hopefully put up an additional post for Full Outer Join next week.

 

Assumptions

 

For my code examples, I’ll be using a slightly modified version of the classes I defined in my first post.  In the below declarations, I’ve declared some nullable types on members which previously couldn’t have a value of Nothing (e.g. Integer -> Integer?).

 


Class Customer

    Public CustomerID As Integer?

    Public ContactName As String

    Public Phone As String

    Public Address As String

    Public City As String

    Public State As String

    Public Zip As String

End Class

 

Class Order

    Public OrderID As Integer?

    Public CustomerID As Integer?

    Public Cost As Single?

    Public Phone As String

    Public OrderDate As DateTime?

    Public ShippingZip As String

    Public ItemName As String

End Class

 

 

I’ve also declared CustomerTable and OrderTable variables as follows, to show some example results:

 

 

Dim CustomerTable As Customer() = { _

    New Customer With {.ContactName = "Bill Horst", .CustomerID = 112}, _

    New Customer With {.ContactName = "John Doe", .CustomerID = 354}, _

    New Customer With {.ContactName = "Jane Doe", .CustomerID = 938}}

 

Dim OrderTable As Order() = { _

    New Order With {.OrderDate = #3/25/1982#, .CustomerID = 112}, _

    New Order With {.OrderDate = #3/13/2005#, .CustomerID = 112}, _

    New Order With {.OrderDate = #9/29/2007#, .CustomerID = 938}, _

    New Order With {.OrderDate = #1/31/2008#, .CustomerID = 444}}

 

 

Left Join

 

A SQL LEFT OUTER JOIN is like a standard Inner Join, but it returns results in the “Left Table” even if there is no matching result in the “Right Table”.  For example:

 

SQL

SELECT Contact.ContactName, Shipment.OrderDate

FROM CustomerTable Contact

LEFT OUTER JOIN OrderTable Shipment

   ON Contact.CustomerID = Shipment.CustomerID

 

 

Assuming the above data in CustomerTable and OrderTable, this query would give me results like the following:

 

Results:

ContactName     OrderDate

Bill Horst      3/25/1982

Bill Horst      3/13/2005

John Doe        NULL

Jane Doe        9/29/2007

 

If I want to re-create this query in VB code, I can start by setting up a Group Join between CustomerTable and OrderTable.  I don’t want to calculate any aggregate values, so I’ll use the Group keyword to provide an entry for each member of CustomerTable with a corresponding array of entries from OrderTable.

 

VB

From Contact In CustomerTable _

Group Join Shipment In OrderTable _

  On Contact.CustomerID Equals Shipment.CustomerID _

  Into RightTableResults = Group

 

Results:

{Contact={Customer}, RightTableResults = {Grouping}}

{Contact={Customer}, RightTableResults = {Order[]}}

{Contact={Customer}, RightTableResults = {Grouping}}

 

 

However, I don’t want a series of arrays, so I’ll add a From clause to cross-join each Contact with the individual members of the corresponding array.

 

VB

From Contact In CustomerTable _

Group Join Shipment In OrderTable _

  On Contact.CustomerID Equals Shipment.CustomerID _

  Into RightTableResults = Group _

From Shipment In RightTableResults.DefaultIfEmpty

 

Results:

{Contact = {Customer}, RightTableResults = {Grouping}, Shipment = {Order}}

{Contact = {Customer}, RightTableResults = {Grouping}, Shipment = {Order}}

{Contact = {Customer}, RightTableResults = {Order[]}, Shipment = Nothing}

{Contact = {Customer}, RightTableResults = {Grouping}, Shipment = {Order}}

 

 

Now I want to Select only the fields in which I’m I’m interested– Contact.ContactName and Shipment.OrderDate:

 

VB

From Contact In CustomerTable _

Group Join Shipment In OrderTable _

  On Contact.CustomerID Equals Shipment.CustomerID _

  Into RightTableResults = Group _

From Shipment In RightTableResults.DefaultIfEmpty _

Select Contact.ContactName, _

       Shipment.OrderDate

 

 

This code still has a problem, though – when I get to the entry where Shipment = Nothing, the Select statement will throw a NullReferenceException.  So, I need to do something to handle this case.  There are several ways you could work around this, such as defining your own method, but the way I’ve chosen is with some ternary operator calls:

 

VB

From Contact In CustomerTable _

Group Join Shipment In OrderTable _

  On Contact.CustomerID Equals Shipment.CustomerID _

  Into RightTableResults = Group _

From Shipment In RightTableResults.DefaultIfEmpty _

Select Contact.ContactName, _

       OrderDate = _

         If(Shipment Is Nothing, Nothing, _

            If(Shipment Is Nothing, New Order, Shipment).OrderDate)

 

Results:

{ContactName = "Bill Horst", OrderDate = {DateTime}}

{ContactName = "Bill Horst", OrderDate = {DateTime}}

{ContactName = "John Doe", OrderDate = Nothing}

{ContactName = "Jane Doe", OrderDate = {DateTime}}

 

 

Clearly, this code is quite complicated, but if will accomplish the equivalent of a LEFT OUTER JOIN without any Aggregate functions.  As you develop a deeper familiarity with LINQ, you may be able to find simpler ways to convert your specific OUTER JOIN queries.

 

For completeness, here is the corresponding Right Outer Join:

 

VB

Dim RightJoin = _

    From Shipment In OrderTable _

    Group Join Contact In CustomerTable _

      On Contact.CustomerID Equals Shipment.CustomerID _

      Into RightTableResults = Group _

    From Contact In RightTableResults.DefaultIfEmpty _

    Select ContactName = _

             If(Contact Is Nothing, _

                Nothing, _

                If(Contact Is Nothing, _

                   New Customer, _

                   Contact).ContactName), _

           Shipment.OrderDate

  

 

Please comment with additional topic ideas for this series.  Next time I plan to cover Full Outer Join.

 

-      Bill Horst, VB IDE Test

Leave a Comment
  • Please add 1 and 1 and type the answer here:
  • Post
  • Too bad all those new VB features are anusable in .aspx pages.

    Someone just forgot about VB.

    Again.

  • How would you do:

    SELECT Contact.ContactName, Shipment.OrderDate

    FROM CustomerTable Contact

    LEFT OUTER JOIN OrderTable Shipment

      ON Contact.CustomerID = Shipment.CustomerID

      AND Contact.ContactName = 'Matthew'

    ?? (As a side note, 95% of SQL developers I have asked will incorrectly predict the output of the above query)

    How would you do:

    SELECT Contact.ContactName, Shipment.OrderDate

    FROM CustomerTable Contact

    LEFT OUTER JOIN OrderTable Shipment

      ON Contact.CustomerID = Shipment.CustomerID

    WHERE Contact.ContactName = 'Matthew'

    ??

    (I use 'Matthew' merely as an example - feel free to change it as appropriate).

  • I think linq will find its uses elsewhere.  I can't imagine having to maintain this kid of stuff, or allowing anyone on my team to write it.  Possibly some tool can generate it for us, but I really think Linq to SQL not very useful in reality.  It is really cool for simple demos though.

  • 1. l want to connect fingerprint in visual basic and save print in database

    2. How do to save picture in MSSQL server database

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

  • Thanks for delving deeper and showing each step in the build-up of the final Linq query.  I'm still confused by the "Into RightTableResults = Group" phrase and exactly what it does and why.

    Thank you also for running into a situation including a DBNull in a field.

    I think that typed datasets should map to Nullable(of T) when "allow null" is set to True.  For example if a column is set to "integer" and allows DBNull, the Linq query should return a Nullable(of integer) or "integer?" and let the application test the "query.integerCol.HasValue" to see if it returned Nothing.  This would be a lot easier for application programmers to handle than what you had to do.

    I have put in this suggestion via connect feedback.

    I look forward to future articles and some "whole solution" articles to handle a "round trip" situation like converting the following SQL:

    UPDATE SecTbl SET SecTbl.DispOrderNum = SecTbl!DispOrderNum + 1

    WHERE (SecTbl.DispOrderNum >= 20);

    This shifts a table list of display items down so that I can insert a new display item at location 20.  All in one simple SQL command statement.

    All Linq examples I have seen only show extracting data.  I would like to see the "round trip" of extracting, changing, and updating back to the database.

    Great Series!

    Bill

  • Matthew - I probably know much less about SQL than most SQL developers (since I spend all my time in VB), so please correct me if I'm wrong.  One of my co-workers said he thought your first query was basically equivalent to the second one.  Here's how I would think to do it in LINQ:

    From Contact In CustomerTable _

    Group Join Shipment In OrderTable _

     On Contact.CustomerID Equals Shipment.CustomerID _

     Into RightTableResults = Group _

    From Shipment In RightTableResults.DefaultIfEmpty _

    Where Contact.ContactName = "Matthew" _

    Select Contact.ContactName, _

          OrderDate = _

              If(Shipment Is Nothing, Nothing, _

                  If(Shipment Is Nothing, New Order, Shipment).OrderDate)

    Andrew - I agree this is complicated and would be difficult to maintain.  I've submitted a request for Outer Joins (with simple syntax) to be added to a future VB release, based on the number of customers asking about it.  If you'd like to submit a suggestion as well, you can do so from http://connect.microsoft.com/ and it will probably increase the liklihood of the work actually getting done.

    You might also be able to create a custom query provider to accomplish this.  One of my co-workers wrote an article on the subject that can be found at http://blogs.msdn.com/msdnmagazine/archive/2007/07/31/4150265.aspx

    Regarding the MSSQL question - I don't know a whole lot about SQL Server.  Your best bet is probably to put a post on one of the SQL Server Forums - they can be found at http://forums.microsoft.com/MSDN/default.aspx?siteid=1

    Bill - Thanks for your feedback!

  • Thanks for a lot of LINQ. IMO this topic is way exageratted when it comes to vb9/vs.net 2008. It almost creates a reaction of repulsion. It seems that everything you did new or better in vs.net 2008 (vb/c#) is LINQ wich I hope to be false. However almost everything that I see is about LINQ. Please put it in it's right place and deal with other topics too. To me the whole LINQ thing seems so not significat - sorry... And it's implementation from the language point of view seems failed. VB has a coherence and a logic in it's syntax that LINQ completely lacks IMO. I've already have way too much of it. Please touch other subjects too - LINQ is abused on each and every place dealing with vb nowadays.

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

  • ここでは、このシリーズの 以前の投稿 ( 英語 ) をお読みになっていることを前提としています。 結合 について投稿した後で、外部結合について、いくつかの質問を受けました。パート 6 でご覧になったように、

  • Ovi, I'm amazed at your capability to say absolutely nothing other than "I don't like it".  Great job!

  • Only one thing I can't understand.  Why

    If(Shipment Is Nothing, Nothing, _

               If(Shipment Is Nothing, New Order, Shipment).OrderDate)

    and not simply

    If(Shipment Is Nothing, Nothing, _

               Shipment.OrderDate)

  • thank you for helpful <a href="http://www.7fasst.com">more</a>

  • there should be clear keywords for left,right and full outer join..I don't understand why Microsoft is not given some simple techniques to use left/right/outer joins as nice SQL ....This only thing which frustrate me to use LINQ

  • Select s.SubID,

      Title,

      Description,

      ISNULL(m.SubMemberID, 0) MemberID,

      ModifyDescription,

      m.UserID

    from Subscriptions s

    Left join SubscriptionMembers m on m.SubID = s.SubID and m.UserID='e44b144f-9ba5-4eee-b99b-a88b79efe866'

    Please help Convert to LINQ

Page 1 of 2 (18 items) 12