A group blog from members of the VB team
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 Cost As Single?
Public OrderDate As DateTime?
Public ShippingZip As String
Public ItemName As String
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
{Contact={Customer}, RightTableResults = {Grouping}}
{Contact={Customer}, RightTableResults = {Order[]}}
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.
Into RightTableResults = Group _
From Shipment In RightTableResults.DefaultIfEmpty
{Contact = {Customer}, RightTableResults = {Grouping}, Shipment = {Order}}
{Contact = {Customer}, RightTableResults = {Order[]}, Shipment = Nothing}
Now I want to Select only the fields in which I’m I’m interested– Contact.ContactName and Shipment.OrderDate:
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:
OrderDate = _
If(Shipment Is Nothing, Nothing, _
If(Shipment Is Nothing, New Order, Shipment).OrderDate)
{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:
Dim RightJoin = _
From Shipment In OrderTable _
Group Join Contact In CustomerTable _
From Contact In RightTableResults.DefaultIfEmpty _
Select ContactName = _
If(Contact Is Nothing, _
Nothing, _
New Customer, _
Contact).ContactName), _
Please comment with additional topic ideas for this series. Next time I plan to cover Full Outer Join.
- Bill Horst, VB IDE Test
Dim var = (From s In Me.Context.Subscriptions
From sm In Me.Context.SubscriptionMembers.Where(Function(v) v.SubID = s.SubID).DefaultIfEmpty()
Where sm.UserID = UserID).ToList
i am trying this way but no luck please help