How to do sql joins in lambda?

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 TableBUNIONSELECT 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.