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

INNER JOINS

An inner join returns only rows that exist in both tables. This means that an inner join only returns the common records between the two tables. Now, I want to perform inner joins between the Teachers and Department tables.
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
                         };

• `from t in db.Teachers`: This part of the query starts with a "from" clause, indicating that we are selecting data from the Teachers table. "t" is an alias used to refer to each record from the Teachers table in subsequent parts of the query.
• `join d in db.Departments on t.Dep equals d.Depid`: This is the join clause. It specifies that we are joining the Teachers table ("t") with the Departments table ("d") based on a common attribute. In this case, it appears that both tables have a field related to the department (possibly "Dep" in the Teachers table and "Depid" in the Departments table), and the join is performed where these fields are equal.
•  `select new { t.Name, d.DepName }`: This part of the query specifies what data we want to select from the joined result. It creates a new anonymous type with two properties: "Name" from the Teachers table ("t") and "DepName" from the Departments table ("d"). This essentially creates a new dataset containing the names of teachers and their corresponding department names.

This LINQ query performs an inner join between the Teachers and Departments tables, selecting the name of each teacher along with the name of the department they belong to.

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

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

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 :-

A RIGHT JOIN returns all rows from the right table and only the matched records from the left table. If there are no matching columns 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
                           
                         };

Joining Departments with Teachers:

join d in db.Teachers on t.Depid equals d.Dep into td: This performs a group join between the Departments and Teachers tables. It matches each department (t) with teachers (d) based on the condition that the department ID (Depid from Departments) is equal to the department field (Dep from Teachers). The results of the join are placed into a collection td. This is essentially the opposite of the first example, where we started with Teachers and joined Departments.

Rigt Join for Teachers:

• from dt in td.DefaultIfEmpty(): This step transforms the group join into a left outer join, allowing departments without any associated teachers to be included in the result. For departments with no matching teachers, dt will be null.

Selecting Data:

select new { dt.Name, t.DepName }: This line constructs a new anonymous object for each resulting pair from the join operation. However, there's a crucial detail to note:

• dt.Name: Since dt represents teachers, this field attempts to access the Name of a teacher. For departments with no teachers, dt will be null, which could lead to a runtime error if not handled properly because you cannot access the Name property of null.
• t.DepName: This is the name of the department, which will always be present since the iteration starts with the Departments table.