title: left join two tables in same DataContext LinQ in c#
body: <p>I have two table in the same DataContext as follows.</p>
  
  <pre><code> Table PersonnelInfo
      { 
  
          personnelId,
          personnelName ,          
          description,
          deathMonthYear,
          updatedBy,
          updatedAt
  
      }
  
  
  Table PersonnelInfoOther
      { 
  
          personnelId,
          personnelName ,                      
          updatedBy,
          updatedAt
  
      }
  </code></pre>
  
  <p>I define a class as follows:</p>
  
  <pre><code>  public class PersonnelInfoAll
      { 
  
          public short personnelId{get;set;}
          public string personnelName { get; set; }
          public string personnelNameOtherLan { get; set; }
          public string description { get; set; }
          public string deathMonthYear { get; set; }
          public int updatedBy { get; set; }
          public DateTime updatedAt { get; set; }  
  
  
      }
  
  
  I need to left join first table with the second one and retrieve all the data as PersonnelInfoAll format:
  
  
  
  public List&lt;PersonnelInfoAllLan&gt; GetPersonnelInfosAll()
      {
          var context = new BookDataClassesDataContext { ObjectTrackingEnabled = false };
  
          var personnelInfo = from u in context.PersonnelInfos
                              join b in context.PersonnelInfoOtherLans
                             on u.personnelId equals b.personnelId
                              select new PersonnelInfoAllLan
                              {
                                  personnelId = u.personnelId,
                                  personnelName = u.personnelName,
                                  personnelNameOtherLan = b.personnelName,
                                  description = u.description,
                                  deathMonthYear = u.deathMonthYear,
                                  updatedBy = u.updatedBy,
                                  updatedAt = u.updatedAt
                              };
  
  
  
  
  
          return personnelInfo.ToList();
      }
  </code></pre>
  
  <p>This gives me only one row which matches with both. But I need all the records from the first table. Is there any one to help.</p>