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

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

In LEFT JOIN if all records of the LEFT table are returned by matching RIGHT table. If the RIGHT table is not matched then ‘NULL’ (no value) returns.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 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 i.e,Department, 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 };
            

 

Ashok Patel

I'm an electronic engineer working in a multinational company,having good experience on Electronics and electrical engineers design and oversee production of electronic equipment such as radios, televisions, computers, washing machines and telecommunication systems.I like to do RND and Research.I also have hands on experience graphic design software and in web designing having great command on ASP.NET, HTML5, JavaScript, T-SQL, JQuery.

Add comment

Donate for Corona Victim

Corona Virus Relief Fund

Your Header Sidebar area is currently empty. Hurry up and add some widgets.