INNER JOIN,RIGHT JOIN,LEFT JOIN USING LINQ IN Multiple Table C#

In this post, I will explain to you linq joins.i will also explain to you joins on multiple tables using linq . I have created three table, Teachers, Department, Adresss.

INNER JOINS

Inner join returns only row which exists in both the tables.That means inner join only returns common records between two table.
Now I want to perform the inner joins between Teachers and Department table.
Using Sql query:

select t.NAME,d.DepName from Teacher t

inner join Department d on t.Dep=d.Depid

Linq Query

LinqtoSqlDbDataContext db = new LinqtoSqlDbDataContext();
            var result = from t in db.Teachers
                         join d in db.Departments on t.Dep equals d.Depid
                         select new {
                             t.Name,
                             d.DepName
                         };

Linq Joins on multiple table
Sql Query:-

select t.Name,d.DepName,a.Adressline2,c.CountryName from Teacher t
inner join Department d on t.Dep=d.Depid
inner join Adressstbl a on t.Adress=a.Addid
inner join Countrytbl c on a.Countryid=c.id

Linq Query:-

LinqtoSqlDbDataContext db = new LinqtoSqlDbDataContext();
            var result = from t in db.Teachers
                         join d in db.Departments on t.Dep equals d.Depid
                         join a in db.Adressstbls on t.Adress equals a.Addid
                         join c in db.Countrytbls on a.Countryid equals c.id
                         select new {
                             t.Name,
                             d.DepName,
                             a.Adressline2,
                             c.CountryName
                         };

 

LEFT JOIN

LEFT JOIN or LEFT OUTER JOIN
LEFT JOIN returns all rows from left table and from right table returns only common or matched records.
If there are no columns matching in the right table, it returns NULL values.
Sql Query:

select t.Name,d.DepName from Teacher t
left join Department d on t.Dep=d.Depid

Linq Query:

LinqtoSqlDbDataContext db = new LinqtoSqlDbDataContext();
            var result = from t in db.Teachers
                         join d in db.Departments on t.Dep equals d.Depid into td
                         from dt in td.DefaultIfEmpty()
                         select new {
                             t.Name,
                             dt.DepName,

Left join on multiple tables 
Sql Query:

select t.Name,d.DepName,a.Adressline2,c.CountryName from Teacher t
left join Department d on t.Dep=d.Depid
left join Adressstbl a on t.Adress=a.Addid
left join Countrytbl c on a.Countryid=c.id

Linq Query:

LinqtoSqlDbDataContext db = new LinqtoSqlDbDataContext();
            var result = from t in db.Teachers
                         join d in db.Departments on t.Dep equals d.Depid into td
                         from dt in td.DefaultIfEmpty()
                         join a in db.Adressstbls on t.Adress equals a.Addid into ta
                         from at in ta.DefaultIfEmpty()
                         join c in db.Countrytbls on at.Countryid equals c.id into ac
                         from ca in ac.DefaultIfEmpty()
                         select new {
                             t.Name,
                             dt.DepName,
                             at.Adressline2,
                             ca.CountryName
                         }; 

 

Right JOIN :-

REIGHT JOIN returns all rows from right table and from left table returns only matched records.
If there are no columns matching in the left table, it returns NULL values.
Sql Query:

select t.Name,d.DepName from teacher t 
right join department d on t.Dep=d.Depid

Linq Query:
For right join just swap the table

LinqtoSqlDbDataContext db = new LinqtoSqlDbDataContext();
            var result = from t in db.Departments
                         join d in db.Teachers on t.Depid equals d.Dep into td
                         from dt in td.DefaultIfEmpty()
                         select new {
                             dt.Name,
                             t.DepName
                           
                         };