title: VB.NET LINQ to XML Left Join with ambiguous column name
body: <p>I need to write a LINQ TO XML query, which queries two XML files exported from Access database tables. The original Access DB query looks like this:</p>
  
  <pre><code>SELECT 
  (
      [TableB].[Code] Is Null,[TableA].[Code],
      LCase(Left([TableA].[Code],1)) &amp; ":" &amp; [TableB].[code]
  ) AS Code, 
  
  Trim
  (
      [TableB].[Description] &amp; " " &amp; [TableA].[Description]
  ) AS Description
  FROM TableA LEFT JOIN TableB 
  ON TableA.Code = TableB.SubProduct;
  </code></pre>
  
  <p>When I convert it to LINQ to XML, I have the problem of the right part of the left join is not available. My LINQ look like this:</p>
  
  <pre><code>Dim results = _
          From a In TableA.Descendants("Product")
          Group Join b In TableB.Descendants("Product")
          On a.Element("Code").Value Equals b.Element("SubProduct").Value Into leftJoinGroup Group
          From p In leftJoinGroup.DefaultIfEmpty
          Select New With
          {
               I DON KNOW HOW TO WRITE IT
          }
  </code></pre>
  
  <p>Both tables have the column named "Code". However, the variable TableB seems to be unavailable inside my <code>Select</code>clause. I only have <code>a</code> and <code>p</code> available so I can't get the <code>Code</code> from TableB (b). How should I do that?</p>