{"id":4733,"date":"2008-01-31T14:37:00","date_gmt":"2008-01-31T14:37:00","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/vbteam\/2008\/01\/31\/converting-sql-to-linq-part-8-leftright-outer-join-bill-horst\/"},"modified":"2024-07-05T14:33:31","modified_gmt":"2024-07-05T21:33:31","slug":"converting-sql-to-linq-part-8-leftright-outer-join-bill-horst","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/vbteam\/converting-sql-to-linq-part-8-leftright-outer-join-bill-horst\/","title":{"rendered":"Converting SQL to LINQ, Part 8: Left\/Right Outer Join (Bill Horst)"},"content":{"rendered":"

This post assumes you’ve read the previous posts<\/a> in this series.<\/p>\n

<\/span><\/p>\n

          <\/span><\/p>\n

<\/span><\/p>\n

After my post on joins<\/a>, I’ve had some questions about outer joins.  <\/span>As you can see in part 6, VB9 doesn’t have smooth support for Left or Right join.  <\/span>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.  <\/span>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.  <\/span>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<\/a>.<\/p>\n

<\/strong><\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/b><\/p>\n

I’ll cover Left and Right Outer Join in this post and hopefully put up an additional post for Full Outer Join next week.<\/p>\n

<\/span><\/p>\n

 <\/span><\/p>\n

<\/span><\/p>\n

Assumptions<\/p>\n

<\/span><\/b><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

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

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n\n\n\n

<\/span><\/p>\n

Class<\/span> Customer<\/p>\n

<\/span><\/p>\n

    <\/span>Public<\/span> CustomerID As<\/span> Integer<\/span>?<\/p>\n

<\/span><\/p>\n

    <\/span>Public<\/span> ContactName As<\/span> String<\/p>\n

<\/span><\/span><\/p>\n

    <\/span>Public<\/span> Phone As<\/span> String<\/p>\n

<\/span><\/span><\/p>\n

    <\/span>Public<\/span> Address As<\/span> String<\/p>\n

<\/span><\/span><\/p>\n

    <\/span>Public<\/span> City As<\/span> String<\/p>\n

<\/span><\/span><\/p>\n

    <\/span>Public<\/span> State As<\/span> String<\/p>\n

<\/span><\/span><\/p>\n

    <\/span>Public<\/span> Zip As<\/span> String<\/p>\n

<\/span><\/span><\/p>\n

End<\/span> Class<\/p>\n

<\/span><\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

Class<\/span> Order<\/p>\n

<\/span><\/p>\n

    <\/span>Public<\/span> OrderID As<\/span> Integer<\/span>?<\/p>\n

<\/span><\/p>\n

    <\/span>Public<\/span> CustomerID As<\/span> Integer<\/span>?<\/p>\n

<\/span><\/p>\n

    <\/span>Public<\/span> Cost As<\/span> Single<\/span>?<\/p>\n

<\/span><\/p>\n

    <\/span>Public<\/span> Phone As<\/span> String<\/p>\n

<\/span><\/span><\/p>\n

    <\/span>Public<\/span> OrderDate As<\/span> DateTime?<\/p>\n

<\/span><\/p>\n

    <\/span>Public<\/span> ShippingZip As<\/span> String<\/p>\n

<\/span><\/span><\/p>\n

    <\/span>Public<\/span> ItemName As<\/span> String<\/p>\n

<\/span><\/span><\/p>\n

End<\/span> Class<\/p>\n

<\/span><\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/p>\n

 <\/p>\n

<\/span><\/b><\/p>\n

I’ve also declared CustomerTable and OrderTable variables as follows, to show some example results:<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n\n\n\n
\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

Dim<\/span> CustomerTable As<\/span> Customer() = { _<\/p>\n

<\/span><\/p>\n

    <\/span>New<\/span> Customer With<\/span> {.ContactName = “Bill Horst”<\/span>, .CustomerID = 112}, _<\/p>\n

<\/span><\/p>\n

    <\/span>New<\/span> Customer With<\/span> {.ContactName = “John Doe”<\/span>, .CustomerID = 354}, _<\/p>\n

<\/span><\/p>\n

    <\/span>New<\/span> Customer With<\/span> {.ContactName = “Jane Doe”<\/span>, .CustomerID = 938}}<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

Dim<\/span> OrderTable As<\/span> Order() = { _<\/p>\n

<\/span><\/p>\n

    <\/span>New<\/span> Order With<\/span> {.OrderDate = #3\/25\/1982#, .CustomerID = 112}, _<\/p>\n

<\/span><\/p>\n

    <\/span>New<\/span> Order With<\/span> {.OrderDate = #3\/13\/2005#, .CustomerID = 112}, _<\/p>\n

<\/span><\/p>\n

    <\/span>New<\/span> Order With<\/span> {.OrderDate = #9\/29\/2007#, .CustomerID = 938}, _<\/p>\n

<\/span><\/p>\n

    <\/span>New<\/span> Order With<\/span> {.OrderDate = #1\/31\/2008#, .CustomerID = 444}}<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/p>\n

 <\/p>\n

<\/span><\/b><\/p>\n

<\/p>\n

<\/span><\/b><\/p>\n

Left Join<\/p>\n

<\/span><\/b><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/b><\/p>\n

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”.  <\/span>For example:<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n\n\n\n\n
\n

SQL<\/p>\n

<\/span><\/b><\/p>\n<\/td>\n<\/tr>\n

\n

SELECT Contact.ContactName, Shipment.OrderDate<\/p>\n

<\/span><\/p>\n

FROM CustomerTable Contact<\/p>\n

<\/span><\/p>\n

LEFT OUTER JOIN OrderTable Shipment<\/p>\n

<\/span><\/p>\n

   <\/span>ON Contact.CustomerID = Shipment.CustomerID<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

Assuming the above data in CustomerTable and OrderTable, this query would give me results like the following:<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n\n\n\n\n
\n

Results:<\/p>\n

<\/span><\/p>\n<\/td>\n<\/tr>\n

\n

ContactName     <\/span>OrderDate<\/p>\n

<\/span><\/b><\/p>\n

Bill Horst      <\/span>3\/25\/1982<\/p>\n

<\/span><\/p>\n

Bill Horst      <\/span>3\/13\/2005<\/p>\n

<\/span><\/p>\n

John Doe        <\/span>NULL<\/p>\n

<\/span><\/p>\n

Jane Doe        <\/span>9\/29\/2007<\/p>\n

<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

If I want to re-create this query in VB code, I can start by setting up a Group Join between CustomerTable and OrderTable.  <\/span>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.<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n\n\n\n\n
\n

VB<\/p>\n

<\/span><\/b><\/p>\n<\/td>\n<\/tr>\n

\n

From<\/span> Contact In<\/span> CustomerTable _<\/p>\n

<\/span><\/p>\n

Group<\/span> Join<\/span> Shipment In<\/span> OrderTable _<\/p>\n

<\/span><\/p>\n

  <\/span>On<\/span> Contact.CustomerID Equals<\/span> Shipment.CustomerID _<\/p>\n

<\/span><\/p>\n

  <\/span>Into<\/span> RightTableResults = Group<\/p>\n

<\/span><\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n

Results:<\/p>\n

<\/span><\/p>\n

{Contact={Customer}, RightTableResults = {Grouping}}<\/p>\n

<\/span><\/p>\n

{Contact={Customer}, RightTableResults = {Order[]}}<\/p>\n

<\/span><\/p>\n

{Contact={Customer}, RightTableResults = {Grouping}}<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

<\/p>\n

 <\/p>\n

<\/span><\/i><\/p>\n

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.<\/p>\n

<\/span><\/p>\n

<\/p>\n

 <\/p>\n

<\/span><\/p>\n\n\n\n\n
\n

VB<\/p>\n

<\/span><\/b><\/p>\n<\/td>\n<\/tr>\n

\n

From<\/span> Contact In<\/span> CustomerTable _<\/p>\n

<\/span><\/p>\n

Group<\/span> Join<\/span> Shipment In<\/span> OrderTable _<\/p>\n

<\/span><\/p>\n

  <\/span>On<\/span> Contact.CustomerID Equals<\/span> Shipment.CustomerID _<\/p>\n

<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":260,"featured_media":8818,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[20,195],"tags":[41,49,94,166],"class_list":["post-4733","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-converting-sql-to-linq","category-visual-basic","tag-bill-horst","tag-converting-sql-to-linq","tag-linqvb9","tag-vb2008"],"acf":[],"blog_post_summary":"

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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/4733","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/users\/260"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/comments?post=4733"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/4733\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media\/8818"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media?parent=4733"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/categories?post=4733"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/tags?post=4733"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}