[Solved]-LEFT OUTER JOIN in LINQ With Where clause in C#

In a LEFT JOIN, if all records of the LEFT table are returned by matching the RIGHT table. If the RIGHT table is not matched, then ‘NULL’ (no value) is returned. LINQ is used in C# to query field objects from different types of data in the same way that we use SQL Language to query a Relational Database.

As a query, we retrieve data from a Data Source based on any Specific Condition or Criteria and Access and Manipulate that Retrieved Data according to your need. The query can be of different types, but each type of query returns some kind of data collection.

In this post, I will explain LINQ joins on multiple tables. I have created two tables: Department and Employee.

 

Table Sql Script:

//Department table
CREATE TABLE [dbo].[Department](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [DepartmentName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Departments] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
//Employee Table

CREATE TABLE [dbo].[Employee](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](150) NULL,
    [Address] [nvarchar](500) NULL,
    [Salary] [decimal](18, 2) NULL,
    [DepId] [int] NULL,
 CONSTRAINT [PK_Employee_1] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Linq Left Join With where Clouse .
Sql Query:-

select e.Name,d.DepartmentName from Employee e
LEFT JOIN Department d on e.DepId=d.Id
where e.Address='Paris'

Linq Query:-

var query = from person in db.Employees
                        join dep in db.Departments on person.DepId equals dep.Id into gj
                        from subdata in gj.DefaultIfEmpty()
                        where person.Address== "Paris"
                        select new { person.Name, DepartmentName = subdata.DepartmentName };
            

 

This code snippet is a LINQ query written in C#. Let's break it down:

1. `var query`: This declares a variable named `query` to store the result of the LINQ query.

2. `from person in db.Employees`: This part of the query indicates that we are selecting data from the `Employees` table in the database (`db`). It assigns each employee record from the `Employees` table to the variable `person`.

3. `join dep in db.Departments on person.DepId equals dep.Id into gj`: This performs a join operation between the `Employees` table and the `Departments` table based on the condition that the `DepId` field of the `Employees` table matches the `Id` field of the `Departments` table. The `into gj` part groups the results of the join operation.

4. `from subdata in gj.DefaultIfEmpty()`: This line handles the left join operation. It specifies that for each record in the `Employees` table, if there is no matching record in the `Departments` table (`DefaultIfEmpty()`), it will still include the record with a `null` value for the `Department`.

5. `where person.Address == "Paris"`: This filters the records in the `Employees` table where the `Address` field is equal to "Paris".

6. `select new { person.Name, DepartmentName = subdata.DepartmentName }`: This part of the query selects specific fields from the joined result set. It creates an anonymous object with properties `Name` from the `Employees` table and `DepartmentName` from the `Departments` table. If the `Department` record is null (due to the left join), `DepartmentName` will be null.

So, in this LINQ query retrieves employee names (`Name` field from `Employees` table) and their corresponding department names (`DepartmentName` field from `Departments` table), specifically for employees whose address is "Paris". It performs a left join between the `Employees` and `Departments` tables, ensuring that all employee records are included, even if there's no corresponding department record.