Using linq to check if a condition exists

Question

Here is the situation I'm trying to solve

Lets take the table Employee

Create Table Employee ( Employeeid int primary key, EMPname varchar(50), ManagerEmplId int reference key Employee (EmployeeID) TreeLevel int, .... ) 

Here I need to find all employees at the sheet level.

Leaf-level employees are all employees who have a manager, but they have no one to inform them. I have a little help from db that has a TreeLevel column where I can point to pick someone at level 3, but I need a UNIONclause that will force me to all employees at tree level 2 that don't have any employees. I have only 3 levels of the tree if this helps in creating the linq query.

  return ((from b in _db.Employees && b.TreeLevel==3 && b.DeletedDate== null select b) .Union (from b in _db.Employees select b) ) .ToDictionary(k => k.EmployeeID, v => v.EMPname); 

UPDATE: Real query:

 (from fi in firm join bra in _db.Branches on fi.BranchID equals bra.ParentBranchID into g from sc in g.DefaultIfEmpty() where fi.DeletedDate == null && g == null select fi) .ToList() .ToDictionary(k => k.BranchID, v => v.BranchName); 

Error:

 Cannot compare elements of type 'System.Collections.Generic.IEnumerable`1'. Only primitive types (such as Int32, String, and Guid) and entity types are supported. 

Answer 1

You can try the right outer join and make sure the left side is empty.

In this post How do I make a full outer join in Linq? you can find a good example of how to do this in linq.

 from b in _db.Employees from c in _db.Employees.Where(o=> o.ManagerEmplId == b.Id).DefaultIfEmpty() where c == null 

Answer 2

This request should do the trick, regardless of the depth of the tree:

 var leafEmps = (from emp in _db.Employees where !_db.Employees.Any(e => e.ManagerEmplId == emp.EmployeeId) select emp); 

Answer 3

 var managersids = _db.Employees.Select(emp => emp.ManagerEmplId ).Distinct(); var leafemps = _db.Employees.where(emp => !managersids.contains(emp.Employeeid)); 

To make it simple, get all the managers, and then search for people who are not managers

 var leafemps = from emp in _db.Employees let managersids = _db.Employees.Select(emp => emp.ManagerEmplId ).Distinct() where !managersids.contains(emp.Employeeid) select emp