the two common joins are the inner join (or just join in linq) and the left join. suppose you have two collections of data. one you will call the master or left collection, and the other you'll call the detail or right collection. a left join is a join whereby all of the elements from the left collection are returned and only elements from the right collection that have a correlated value in the left sequence. usually, the correlation is a key or some kind of unique identifier. using another analogy, if the left collection is the parent and the right is the child, a left join is all parents but only children with parents. (a right join returns orphans but no childless parents. gotta love these computer analogies.)

in this article, i will demonstrate the group join because that's how you get to a left join. you also will see some code for linq to sql that is pretty straightforward and my last article, "search and replace with regular expressions," and my upcoming book, linq unleashed: for c#, cover linq to sql in detail. i won't repeat that explanation here.

defining a group join

a group join in linq is a join that has an into clause. the parent information is joined to groups of the child information. that is, the child information is coalesced into a collection and the child collection's parent information occurs only once. (the difference between a join—really an inner join—and a group join is that inner joins repeat the parent information for each child.)

the fragment in listing 1 assumes you have a collection of orders and a collection or order details. (you do. the final listing demonstrates how to get these datum from the northwind traders database using linq to sql.) the code demonstrates a group join followed by an array to display the parent and a nested array to display the children of each parent.

listing 1: a group join on the northwind traders orders and order details tables.

dim groupjoin = (from order in orders _
                 group join detail in details on _
                 order.orderid equals detail.orderid _
                 into child = group _
                 select new with { _
                 .customerid = order.customerid, _
                 .orderid = order.orderid, _
                 .orderdate = order.orderdate, _
                 .details = child}).take(5)

dim line as string = new string("-", 40)
for each ord in groupjoin
   console.writeline("{0} on {1}", ord.orderid, _
      ord.orderdate)
   console.writeline(line)
   for each det in ord.details
      console.writeline("product id: {0}", det.productid)
      console.writeline("unit price: {0}", det.unitprice)
      console.writeline("quantity:   {0}", det.quantity)
      console.writeline("discount:   {0}", det.discount)
      console.writeline()
   next
      console.writeline(line)
   next

   'leftjoin.write(console.out)
   console.readline()

the linq query starts with the anonymous variable groupjoin. (any legal name will do here.) the clause from order in orders defines the range variable order on the collection orders. the range variable is like the iterator variable in a for loop. the clause group join detail in details defines the child range detail on the details sequence. the on..equals clause describes the correlation in the equijoin. and, into child = group coalesces all of the child sequence data into a group. the last part take(5) works like the top keyword in sql. take is an extension method that operates on sequences (which is what linq returns).

the result of the linq query as defined in listing 1 is that you have a new object (called a projection) comprised of customerid, orderid, and orderdate, with a child sequence property, details. details is an attribute of the projection (the new type created with select new with). the last part of the listing displays the outer data and then the grouped detail data.

converting a group join to a left join

a group join is essentially a master detail in-memory relationship. a left join flattens out the data from the detail sequence and puts it on par with the master data. that is, where the group join has a nested detail property with its own properties, the left join will put the properties of the master and detail information as sibling properties.

the difference is that with a left join the right sequence may not have any data. you have to allow for nulls or linq would throw a null exception when it tried to access non-existent elements of the right sequence (order details in this example). you can convert a group join into a left join by adding an additional from clause and range variable on the group and adding a call to the defaultifempty method on the group variable. the revised fragment in listing 2 demonstrates. all of the code is provided in listing 3.

listing 2: a left join uses an additional from clause and range variable after the group and invokes the defaultifempty method to handle missing children.

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)

notice that the projection in listing 2 defines elements from orders and order details as siblings in the new projected type. here is the complete listing and some additional code for looking at the object state.

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