How to do sql joins in lambda?

Question

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

Well, have you tried anything yet? Done any research? Hint: the LINQ Join operation is an inner join. — Jon Skeet
– Jon Skeet, Commented Apr 27, 2016 at 6:57
@JonSkeet I thought about adding my examples, but I thought it would balance how answers are given. — Margus
– Margus, Commented Apr 27, 2016 at 6:59
Well at the moment the question looks very much like "Do my homework for me." I dare say this isn't homework, but it's not a good Stack Overflow question. — Jon Skeet
– Jon Skeet, Commented Apr 27, 2016 at 7:00
You use a combination of Join, Left Join, except, and union in the right order. Or you can use github.com/morelinq/MoreLINQ for most of them if you are working with IEnumerables. — Robert McKee
– Robert McKee, Commented Apr 27, 2016 at 7:05

schlonzo · Accepted Answer · 2016-04-27 08:31:28Z

The most important thing for you, is to know how to perform an INNER JOIN and an OUTER JOIN.

For the INNER JOIN you use JOIN from LINQ like so:

var result = 
    TableA
    .Join(TableB, left => left.Id, right => right.ForeignKeyToTableA, 
       (left, right) => new { TableAColumns = left, TableBColumns = right });

The OUTER JOIN you already showed in your example.

Now you need to mix what you know, to get the desired results.

For example to perform a FULL OUTER JOIN do something like this pseudocode in LINQ:

SELECT TableA.*, TableB.* FROM TableA LEFT OUTER JOIN TableB
UNION
SELECT TableA.*, TableB.* FROM TableB LEFT OUTER JOIN TableA

This would be in LINQ as follows:

var fullOuterJoin =
            (
                TableA
                .GroupJoin(TableB, 
                    left => left.Id, right => right.ForeignKeyId, 
                    (left, right) => new { TableA = left, TableB = right })
                .SelectMany(p => p.TableB.DefaultIfEmpty(), (x, y) => 
                    new { TableA = x.TableA, TableB = y })
            )
            .Union
            (
                TableB
                .GroupJoin(TableA, 
                    left => left.Id, right => right.ForeignKeyId, 
                    (left, right) => new { TableA = right, TableB = left })
                .SelectMany(p => p.TableA.DefaultIfEmpty(), (x, y) => 
                    new { TableA = y, TableB = x.TableB })
            );

The very last example of your image would then be:

var fullOuterJoinOnlyWithNulls =
            fullOuterJoin
            .Where(p => p.TableA == null || p.TableB == null);

A RIGHT OUTER JOIN is nothing but a LEFT OUTER JOIN where you swap your result columns like this:

var rightOuterJoin =
            (
                TableB
                .GroupJoin(TableA,
                    left => left.Id, right => right.ForeignKeyId,
                    (left, right) => new { TableA = right, TableB = left })
                .SelectMany(p => p.TableA.DefaultIfEmpty(), (x, y) =>
                    new { TableA = y, TableB = x.TableB })
            );

Like this you can construct all your example scenarios. Just check the tables for null when needed.

Collectives™ on Stack Overflow

How to do sql joins in lambda?

As my effort

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

As my effort

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related