Linq inner outer join

I had been asked how to write a Left join for Linq to SQL recently, I totally forget the SQL style syntax of Left Join.

So, I decided to write several examples/samples and put it in my blog. Hopefully, no people will get stuck with this again.

How can we do Cross-Join in Linq?

SQL:

SELECT TableX.*, TableY.*

FROM TableX, TableY

Linq in SQL Syntax:

       from x in TXs

       from y in TYs

       select new { x, y };

Linq in .net syntax:

TXs.SelectMany(x => TYs, (x, y) => new { x, y });

How can we do Inner-Join in Linq?

SQL:

SELECT TableX.*, TableY.*

FROM TableX INNER JOIN TableY ON TableX.YID = TableY.YID

               

Linq in SQL Syntax:

       from x in TXs

       from y in TYs

       where x.YId == y.YId

       select new { x, y }

Linq in .net syntax:

TXs.SelectMany(x => TYs.Where(y => x.YId == y.YId), (x, y) => new { x, y });

How can we do Left-Join in Linq?

SQL:

SELECT TableX.*, TableY.*

FROM TableX LEFT JOIN TableY ON TableX.YID = TableY.YID

               

Linq in SQL Syntax:

       from x in TXs

       join y in TYs on x.YId equals y.YId into yG

       from y1 in yG.DefaultIfEmpty()

       select new { X = x, Y =y1 }

Linq in .net syntax:

TXs.GroupJoin(TYs, x => x.YId, y => y.YId, (x, g) => new { x, g })

          .SelectMany(y => y.g.DefaultIfEmpty(), (item, y) => new { item.x, y });

In my next blog, I will explain how you could translate the following SQL query into Linq query.

       SELECT *

       FROM TableX LEFT JOIN TableY ON TableX.YID = TableY.YID

                   LEFT JOIN TableZ ON TableY.ZID = TableZ.ZID