title: LINQ join query with multiple fields using left outer join
body: <p>I have Product table and Officer table as follow:</p>
  
  <h3>Product</h3>
  
  <pre><code>ProductID | ProductName | Officer1ID | Officer2ID | Officer3ID
  --------- | ----------- | ---------- | ---------- | ----------
  12        | Mouse       | 123        | 124        | 125
  13        | Keyboard    | 234        | 235        | 0
  </code></pre>
  
  <h3>Officer</h3>
  
  <pre><code>OfficerID | OfficerName 
  --------- | ----------- 
  123       | John       
  124       | Andy    
  125       | Mark
  </code></pre>
  
  <p><br /><br />
  I need to join 3 columns (Officer1ID, Officer2ID, Officer3ID) from Product table with OfficerID in Officer table to produce result like this:</p>
  
  <pre><code>ProductID | ProductName | Officer1Name | Officer2Name | Officer3Name
  --------- | ----------- | ------------ | ------------ | ------------
  12        | Mouse       | John         | Andy         | Mark
  13        | Keyboard    | Dave         | Fred         | Leon
  </code></pre>
  
  <p><br /><br />
  This is my attempt. I know how to join 1 field, but not multiple. Can anyone help? Thanks!</p>
  
  <pre><code>List&lt;Product&gt; lstProduct = GetProducts();
  
  List&lt;Officer&gt; lstOfficer = GetOfficers();
  
  var merge = from p in lstProduct
     join from o in lstOfficers on p.Officer1ID equals o.OfficerID
     select new { ProductID = p.ProductID, ProductName = p.ProductName, OfficerName = o.OfficerName };
  </code></pre>
  
  <p><strong>EDIT</strong>
  <br />
  OfficerIDs in Product table could be 0(not exist in Officer table).</p>