Is there a not in sub-query for Linq to Enties as in this T-SQL

linq, linq-to-entities

Solution

How about something like this:

from e in context.Employee
where e.Region == "Region 1" 
&& !(from e2 in context.Employee
     where e2.Region == "Region 1"
     select e2.Id).ToList().Contains(e.ReportsTo)
select e;

Problem

Assume the classic self-referencing Employee table where each employee may have at most one ReportsTo, as created with this T-SQL snippet and sample data: ``` create table Employees ( Id int identity primary key, Name nvarchar(30), Region nvarchar(10), ReportsTo int null foreign key(ReportsTo) references Employees(Id) ) insert into Employees values('Boss','HO',null) insert into Employees values('Underling', 'HO', (select Id from Employees where Name='Boss')) insert into Employees values('Self Important', 'Region 1', (select Id from Employees where Name='Underling')) insert into Employees values('Very Underling', 'Region 1', (select Id from Employees where Name='Self Important')) ``` I can select the manager for Region 1 with this T-SQL ``` select * from Employees where Region = 'Region 1' and ReportsTo not in (select Id from Employees where Region = 'Region 1') ``` In other words, the manager is an employee with not reports to in his region. Now, how do I determine the manager for Region 1 using Linq?

Original source