Left Outer Join using Entity Framework and lambda expressions

Question

Employee id, name, datejoin, deptID 

and another table

 Marketing id, name, deptID 

What I'm trying to do is search for the deptID from the Employee table in Marketing to find out if it exists, if it does not exist, want to add the name and deptID from the Employee table to the marketing table. Basically, I think this is an outer join.

I am trying to use lambda expressions, and EF is new to this area, so I was wondering how to do this. I created the objects needed to define a lambda expression

 tTlEntities sde = new tTlEntities(); sde.t_Marketing.Where(x=>x.deptID == t_Employee.deptID). 

This is how far I have gone. I know some external connection is needed. Help Please

Answer 1

This can be done using the employee’s left outer join for marketing and checking zero marketing results for the employee, or you can use none that exists in LINQ to Entities:

 tTlEntities sde = new tTlEntities(); var employeeQuery = sde.t_Employee.Where( e=> !sde.t_Marketing.Any(m => m.deptID == e.deptID)); 

Answer 2

You need DefaultIfEmpty() . Also, the type must be anonymous, not an employee, as it requires more properties than an employee.

 var leftList = (from emp in Employees join d in Departments on emp.deptID equals d.Id into output from j in output.DefaultIfEmpty() select new {id = emp.Id, name = emp.name, datejoin = emp.datejoin, deptname = d.name }); 

Answer 3

If your navigation property has a nullable foreign key id , EF will automatically create a left outer join . If it is not null, the result will be inner join . no need to explicitly write the connection. just use the Include extension method for a navigation property that has a nullable foreign key id .