title: How to do sql joins in lambda?
body: <p>From time-to-time, I stumble on this problem that I use a subset of lambda joins. Given that I can use any LINQ extensions how should I go about implementing following joins:</p>
  
  <p><a href="https://i.stack.imgur.com/h9Pln.png" rel="noreferrer"><img src="https://i.stack.imgur.com/h9Pln.png" alt="enter image description here"></a></p>
  
  <p>For simplicity sake tables are defined as </p>
  
  <pre><code>CREATE TABLE [dbo].[TableA] (
      [Key]             INT            IDENTITY (1, 1) NOT NULL,
      [Value]           NVARCHAR (MAX) NULL,
      CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED ([Key] ASC)
  );
  
  CREATE TABLE [dbo].[TableB] (
      [Key]             INT            IDENTITY (1, 1) NOT NULL,
      [Value]           NVARCHAR (MAX) NULL,
      CONSTRAINT [PK_TableB] PRIMARY KEY CLUSTERED ([Key] ASC)
  );
  </code></pre>
  
  <p>or if you prefer code first</p>
  
  <pre><code>public class TableContext : DbContext
  {
      public DbSet&lt;B&gt; TableB { get; set; }
      public DbSet&lt;A&gt; TableA { get; set; }
  
      protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
      {
          optionsBuilder.UseSqlServer(ConnectionString);
      }
  
      protected override void OnModelCreating(ModelBuilder modelBuilder)
      {
          modelBuilder.Entity&lt;TableB&gt;().Property(o =&gt; o.Key).UseSqlServerIdentityColumn();
          modelBuilder.Entity&lt;TableA&gt;().Property(o =&gt; o.Key).UseSqlServerIdentityColumn();
      }
  }
  
  public class B : IKeyValue
  {
      public int Key { get; set; }
      public string Value { get; set; }
  }
  
  public class A : IKeyValue
  {
      public int Key { get; set; }
      public string Value { get; set; }
  }
  
  public interface IKeyValue
  {
      int Key { get; set; }
      string Value { get; set; }
  }
  </code></pre>
  
  <hr>
  
  <h2>As my effort</h2>
  
  <p><a href="https://www.wolframalpha.com/input/?i=((A%20intersect%20not%20B)%20union%20(A%20intersect%20B))" rel="noreferrer">((A intersect not B) union (A intersect B))</a></p>
  
  <p><a href="https://i.stack.imgur.com/Rneo4.gif" rel="noreferrer"><img src="https://i.stack.imgur.com/Rneo4.gif" alt="enter image description here"></a></p>
  
  <pre><code>var leftOuterJoin = TableA
    .GroupJoin(
      TableB, 
      a =&gt; a.Key,
      b =&gt; b.Key,
      (x, y) =&gt; new { TableA = x, TableA = y })
    .SelectMany(
      x =&gt; x.TableB.DefaultIfEmpty(),
      (x, y) =&gt; new { TableA = x.TableA, TableB = y});
  </code></pre>
  
  <p><a href="https://www.wolframalpha.com/input/?i=(A%20intersects%20B)" rel="noreferrer">(A intersects B)</a></p>
  
  <p><a href="https://i.stack.imgur.com/5RYgY.gif" rel="noreferrer"><img src="https://i.stack.imgur.com/5RYgY.gif" alt="enter image description here"></a></p>
  
  <pre><code>var innerJoin = TableA
    .Join(
      TableB, 
      a =&gt; a.Key,
      b =&gt; b.Key,
      (x, y) =&gt; x)
  </code></pre>
  
  <p><a href="https://www.wolframalpha.com/input/?i=(A%20union%20B)" rel="noreferrer">(A union B)</a> </p>
  
  <p><a href="https://i.stack.imgur.com/lkAcL.gif" rel="noreferrer"><img src="https://i.stack.imgur.com/lkAcL.gif" alt="enter image description here"></a></p>
  
  <pre><code>var fullOuterJoin = TableA
    .FullOuterJoin(
      TableB, 
      a =&gt; a.Key, 
      b =&gt; b.Key, 
      (x, y, Key) =&gt; new {x, y})
  </code></pre>