Subquery in using LINQ to SQL

Question

I have two tables.

1) Employee which have Id, Name, DeptId columns.

2) Department which have Id and DeptName columns.

I want to find all department which have no any employee using Linq to sql.

IN sql I use below query:

Select * from Department Where Id not in 
(Select DeptId from Employee where DeptId Is not Null )

Answer 1

I hope this will work for you.

var empWithDepartments = from emp in Employee where emp.DeptId != NULL select emp.DeptId;
var result = from dep in Department where !empWithDepartments.Contains(dep.Id) select dep;

Answer 2

You can do this using LINQ left join like this:-

var result = from d in departments
             join e in employees
             on d.Id equals e.DeptId into es
             from x in es.DefaultIfEmpty()
             where x == null
             select new Department { Id = d.Id, Name = d.Name};

Check this Fiddle for example with some custom objects.

In SQL this is equivalent to:-

SELECT D.*
FROM Department D
LEFT JOIN Employee E
   ON D.Id = E.DeptId
WHERE E.id IS NULL

Answer 3

Try this:

var query = from d in Departments
 where !(from e in Employees select e.FkDepartmentId).Contains(d.DepartmentId)
select d;

According to this post this would work with LINQ-TO-SQL but not with LINQ-TO-EF.