How to do sql joins in lambda?

asked10 years ago
last updated10 years ago
viewed14.5k times
Up Vote13Down Vote

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:

For simplicity sake tables are defined as

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)
);

or if you prefer code first

public class TableContext : DbContext
{
    public DbSet<B> TableB { get; set; }
    public DbSet<A> TableA { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(ConnectionString);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<TableB>().Property(o => o.Key).UseSqlServerIdentityColumn();
        modelBuilder.Entity<TableA>().Property(o => 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; }
}

As my effort

((A intersect not B) union (A intersect B))

var leftOuterJoin = TableA
  .GroupJoin(
    TableB, 
    a => a.Key,
    b => b.Key,
    (x, y) => new { TableA = x, TableA = y })
  .SelectMany(
    x => x.TableB.DefaultIfEmpty(),
    (x, y) => new { TableA = x.TableA, TableB = y});

(A intersects B)

var innerJoin = TableA
  .Join(
    TableB, 
    a => a.Key,
    b => b.Key,
    (x, y) => x)

(A union B)

var fullOuterJoin = TableA
  .FullOuterJoin(
    TableB, 
    a => a.Key, 
    b => b.Key, 
    (x, y, Key) => new {x, y})