title: C# LINQ Query to project and concat two sequences of unlike entities
body: <p>I have three tables in a db that correspond to Entities in our code. (I am dumbing these classes down for simplicity)</p>
  
  <pre><code>public class Current{
      [Key]
      public int CurrentId {get; set;}
  
      [Required]
      public int TableKeyId {get; set;}
  
      public string Value {get; set;}
  }
  
  public class Promotion{
      [Key]
      public int PromtionId {get; set;}
  
      [Required]
      public int TableKeyId {get; set;}
  
      public string Value {get; set;}
  }
  
  public class TableKey{
      [Key]
      public int TableKeyId {get;set;}
  
      [Required]
      public string PlanCode {get;set;}
  
      [Required]
      public byte Environment {get;set;}
  
      [Required]
      public byte FileId {get;set;}
  
      [Required]
      public string TableNumber {get;set;}
  
  }
  </code></pre>
  
  <p>(The Current and Promotion both have TableKeyId Foreign Key properties).</p>
  
  <p>What I need to do is pull from all three repositories retrieving the Currents and Promotions  getting their value and the TableKey properties. <strong>I need all of the Promotions if they exist and the Currents where their TableKeyId does not equal any TableKeyId from the Promotions</strong>. I tried to accomplish this with the following query:</p>
  
  <pre><code>var query = (from c in this.currentRepository.Query()
                       join t in this.tableKeyRepository.All() on c.TableKeyId equals t.TableKeyId
                       join p in this.promotionRepository.Query() on c.TableKeyId equals p.TableKeyId into pc
                       from p in pc.DefaultIfEmpty()
                       where (t.PlanCode == command.PlanCode &amp;&amp; t.Environment == command.Environment)
                       select new Temptable
                       {
                           TableNumber = t.TableNumber,
                           PlanCode = t.PlanCode,
                           FileId = t.FileId,
                           Value = (p == null ? c.Value : p.Value),
                           TableKeyId = c.TableKeyId
                       });
  </code></pre>
  
  <p>This query <strong>almost</strong> works, except when a Promotion exists that has a TableKeyId that does not correspond to a Current with the same TableKeyId - it won't retrieve those because it's a left outer join. I know I could do it with multiple queries, but I really wanted one single query. Also keep in mind that each table could have around 1million records, so I have to optimize the query.</p>
  
  <p>So if I had the following:</p>
  
  <p>TableKeys</p>
  
  <pre><code>TableKeyId,PlanCode,Environment,FileId,TableNumber
  --------------------------------------------------
  1,"001",2,"PFT","A1234"
  2,"001",2,"PFT","A1235"
  3,"001",2,"PFT","A1236"
  4,"001",2,"PFT","A1237"
  </code></pre>
  
  <p>Currents</p>
  
  <pre><code>CurrentId,TableKeyId,Value
  --------------------------
  1,1,"A"
  2,2,"B"
  3,4,"C"
  </code></pre>
  
  <p>Promotions</p>
  
  <pre><code>PromotionId,TableKeyId,Value
  ---------------------------
  1,1,"G"
  2,2,"H"
  3,3,"I"
  </code></pre>
  
  <p>I would expect the result set if Environment was 2 and PlanCode was "001" in my query to be</p>
  
  <pre><code>TableKeyId,PlanCode,Environment,FileId,TableNumber,Value
  --------------------------------------------------------
  1,"001",2,"PFT","A1234","G"
  2,"001",2,"PFT","A1235","H"
  3,"001",2,"PFT","A1236","I"
  4,"001",2,"PFT","A1237","C"
  </code></pre>
  
  <p>I really appreciate all feedback :)</p>
  
  <p>Thank you!</p>