Convert nested SQL with count to linQ

Question

I have an employee table containing a list of employees. Each mployee have an ID, a corporate ID, COR_N_ID and a mother, MOTHER. The MOTHER field is actually the ID of another employee. Some employees have another Employee attached to them and in this case, the MOTHER field is filled. Else it is left as null. Employees can have a list of employees attached to them and some have none.

I have an SQL request to get a list of employees having no attached employees:

select *
from Employee a
where COR_N_ID = 99
and (select count(ID) from Employee b where b.MOTHER = a.ID) = 0;

However, I am stuck at converting this to linQ. What I have done so far:

 var query = (from emp in Employee.FindAll(item => ( 
                         item.COR_N_ID == id 
                         ))
                         select new KeyValuePair
                         {
                             Key = business.Id.ToString(),
                             Value = business.CBA_CH_NAME
                         }
            );

Any help please?

I think we can remove the COR_N_ID condition here.

I just want a list of all employees having no other employees attached to them. Note that the field linking an employee to another one is the MOTHER field.

I have tried this:

var query = (from emp in Employee.FindAll(item => ( 
                         item.COR_N_ID == id 
                         ))
                         .where(item2 => !Employee.FindAll(item => item.MOTHER == business.ID))

                         select new KeyValuePair
                         {
                             Key = business.Id.ToString(),
                             Value = business.CBA_CH_NAME
                         }
            );

But this does not work.

I have this:

But I want this:

Answer 1

Summary

1. if you have

   

2. and you run

   db.Employee
    .Where(level1 => !db.Employee.Any(level2 => level2.MOTHER == level1.ID))
    .ToList();
   

3. you get

   

Sql to linq

 -----------------------------------------------------------------------------
 |  SQL                                     |   LINQ                         |
 -----------------------------------------------------------------------------
 |  SELECT * From Employee AS A WHERE       |   db.Employee.Where(A =>       |
 -----------------------------------------------------------------------------
 |  (SELECT COUNT(ID) from Employee AS B    |   (db.Employee.Count(B =>      |
 -----------------------------------------------------------------------------
 |  WHERE B.MOTHER = A.ID) = 0              |   B.MOTHER == A.ID))  == 0)    |
 -----------------------------------------------------------------------------

More ways

db.Employee
.Where(level1 => level1.COR_N_ID == 99)
.Where(level2  => db.Employee.Where(level3=> level3.MOTHER == level2.Id).Count() == 0)

or

db.Employee
.Where(level1 => level1.COR_N_ID == 99)
.Where(level2  => !db.Employee.Any(level3  => level3.MOTHER == level2.Id))

or

db.Employee
 .Where(level1 => level1.COR_N_ID == 99 && !db.Employee.Any(level2 => level2.MOTHER == level1.Id))

or

db.Employee
.Where(level1 => !db.Employee.Any(level2 => level2.MOTHER == level1.Id))

Full code

using System;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Linq;

namespace stackoverflow305092
{   
    class Program
    {

        static void Main(string[] args)
        {
            var db = new Model1();
            var emps = db.Employee
            .Where(level1 => !db.Employee.Any(level2 => level2.MOTHER == level1.ID))
                .ToList();
            Console.WriteLine($"ID \t MOTHER \t NAME");

            foreach (var emp in emps)
            {
                Console.WriteLine($"{emp.ID} \t {emp.MOTHER}  \t \t {emp.NAME}");
            }
            Console.ReadLine();
        }
    }

    public class Model1 : DbContext
    {
        public Model1() : base("data source=.;initial catalog=stackoverflow54275000;integrated security=True;") { }
        public virtual DbSet<Employee> Employee { get; set; }
    }


    [Table("Employee")]
    public class Employee
    {
        public int ID { get; set; }
        public int? MOTHER { get; set; }
        public string NAME { get; set; }
    }

    //    CREATE TABLE[dbo].[Employee]
    //    (
    //      [ID][int] NOT NULL,
    //     [MOTHER] [int] NULL,
    //     [NAME] [nvarchar] (250) NOT NULL
    //    )  

}

ref

• c# - How to do a subquery in LINQ? - Stack Overflow

Answer 2

Below I show how to do something equivalent to subquery using extension methods of LINQ (all coments in code :) ):

public static void Main(string[] args)
{
  List<Employee> list = new List<Employee>();

  list = list
    // Here we are using equivalent of a subquery, but in order to include new column,
    // we use Tuple here. You can read about Tuples on Micsorosft pages and this site.
    // You can even name items in Tuple, but I leave it up to you.
    .Select(e => (e.ID, e.MOTHER, e.COR_N_ID, list.Count(innerEmployee => innerEmployee.MOTHER == e.ID)))
    // Now we can use result of our "subquery" in where method.
    .Where(e => e.Item4 == 0 && e.COR_N_ID == 99)
    .ToList();
}

// Sample class for presentation needs :)
public class Employee
{
  public int ID;
  public int MOTHER;
  public int COR_N_ID;
}

Answer 3

var res= Employee.FindAll(x=>x.Mother!=id && x.COR_N_ID==99);