Implementing a Left Join with LINQ, Page 2

Listing 3: All of the code to reproduce the data and run the sample.

Imports System.Data.Linq
Imports System.Data.Linq.Mapping
Imports System.IO


Module Module1

   Public connectionString As String = _
      "Data Source=BUTLER;Initial Catalog=Northwind;" + _
      "Integrated Security=True"


   Sub Main()
      ' Use LINQ to SQL to get the data - context represents
      ' the database
      Dim orderContext As DataContext =
         New DataContext(connectionString)
      Dim detailsContext As DataContext =
         New DataContext(connectionString)

      ' generic table does the ORM association
      Dim orders As Table(Of Order) =
         orderContext.GetTable(Of Order)()
      Dim details As Table(Of OrderDetail) =
         orderContext.GetTable(Of OrderDetail)()

      Dim allDetails = From detail In details _
                       Select detail

      For Each d In allDetails
         Console.WriteLine(d.ProductID)
      Next

      Console.ReadLine()

      ' make sure we have some data
      orders.Write(Console.Out)
      details.Write(Console.Out)

      ' define the left join - a group join with a twist
      Dim leftJoin = (From order In orders _
                      Group Join detail In details On _
                      order.OrderID Equals detail.OrderID _
                      Into children = Group _
                      From child In children.DefaultIfEmpty _
                      Select New With { _
                      .CustomerID = order.CustomerID, _
                      .OrderID = order.OrderID, _
                      .OrderDate = order.OrderDate, _
                      .ProductID = child.ProductID, _
                      .UnitPrice = child.UnitPrice, _
                      .Quantity = child.Quantity, _
                      .Discount = child.Discount}).Take(5)


      leftJoin.Write(Console.Out)
      Console.ReadLine()
   End Sub

   Function WriteLine(ByVal obj As Object) As Object
      Console.WriteLine(obj)
      Return Nothing
   End Function

   <System.Runtime.CompilerServices.Extension()> _
   Public Function Write(Of T)(ByVal obj As T, _
                               ByVal writer As TextWriter)

      If (TypeOf obj Is IEnumerable) Then
         Dim list As IEnumerable = obj
         For Each item In list
            Write(item, writer)
         Next
      End If

      Dim formatted = From info In obj.GetType().GetFields() _
                      Let value = info.GetValue(obj) _
                      Select New With {.Name = info.Name, _
                      .Value = IIf(value Is Nothing, "", value)}

      If (formatted.Count > 0) Then
         For Each one In formatted
            writer.WriteLine(one)
         Next
      Else
         Dim alternate = From info In obj.GetType().GetProperties() _
            Let value = info.GetValue(obj, Nothing) _
            Select New With {.Name = info.Name, _
            .Value = IIf(value Is Nothing, "", value)}
         For Each one In alternate
            writer.WriteLine(one)
         Next

      End If

      writer.WriteLine()

      Return Nothing
   End Function


End Module



<Table(Name:="Orders")> _
Public Class Order
   <Column()> _
   Public OrderID As Integer
   <Column()> _
   Public CustomerID As String
   <Column()> _
   Public EmployeeID As Integer
   <Column()> _
   Public OrderDate As DateTime
   <Column()> _
   Public ShipCity As String
End Class


<Table(Name:="Order Details")> _
Public Class OrderDetail
   <Column()> _
   Public OrderID As Integer
   <Column()> _
   Public ProductID As Integer
   <Column()> _
   Public UnitPrice As Decimal
   <Column()> _
   Public Quantity As Int16
   <Column()> _
   Public Discount As Single
EndClass

Summary

A left join is generally of the records in one set and only those records in the other set that are correlated to the records in the first set. I use the word records out of habit synonymously with objects. (Although in your example, rows of database data were used.) Although LINQ has no left join key phrase, the left join is supported through a group join and the DefaultIfEmpty method.

DefaultIfEmpty provides a default object when there are no child objects. Default child objects are necessary because LINQ supports defining a projection from parent and child objects, but in the left join, again, there may be no child and the object would in effect be null.

See you next month, same bate time, same bat channel.

About the Author

Paul Kimmel is the VB Today columnist for www.codeguru.com and has written several books on object-oriented programming and .NET. Check out his upcoming book, LINQ Unleashed for C#, due in July 2008. Paul Kimmel is an Application Architect for EDS. You may contact him for technology questions at pkimmel@softconcepts.com.

If you are interested in joining or sponsoring a .NET Users Group, check out www.glugnet.org. Glugnet opened a users group branch in Flint, Michigan in August 2007. If you are interested in attending, check out the www.glugnet.org web site for updates.

Copyright © 2008 by Paul T. Kimmel. All Rights Reserved.

Page 2 of 2