C# Linq finding value

Question

I want to return the depart number that is not found Employee Table by comparing Department table.

Person Table

ID        name   salary     job              commision  DeptID 
--------------------------------------------------------------
P001      Jon     2000   Manager    NULL    1
P002      Skeet     1000   Salesman   2000    1
P003      James     2340   Developer  NULL    2
P004      greed     4500   Developer  NULL    2
P005      Joel     1330   Salesman   1200    1
P006      Deol     5000   Architect  NULL    2

Department Table

DeptID  DeptName

    1   Management
    2   Software  
    3   ERP

SQL

select DeptId from dept

where deptId not in (select deptid from person)

When i try to execute the below code

LINQ

var qry = from n in context.Persons
          where n.DeptID !=
          (from m in context.Depts select m.DeptId)
           select new { DeptID = n.DeptID };

I receive the following error

Operator '!=' cannot be applied to operands of type 'int?' and 'System.Linq.IQueryable'

Answer 1

It sounds that your DeptID field in SQL is set to allow nulls. In that case you'd probably want something along the lines of this:

var qry = from n in context.Persons
          where n.DeptID.Value !=
          (from m in context.Depts select m.DeptId)
           select new { DeptID = n.DeptID.Value };

Answer 2

var qry = from n in context.Persons
          where n.DeptID !=
          (from m in context.Depts select m.DeptId).FirstOrDefault()
           select new { DeptID = n.DeptID };

You are trying to compare DeptID with a collection 1 or more department Ids. Even if there would only logically be one result for a DeptID, syntactically you need to specify that you want the first hit.

Answer 3

Suggested rephrasing:

var q = from m in context.Depts
        where
        !context.Persons.Select(p => p.DeptID).Contains(m.DeptID)
        select new { DeptID = m.DeptID };

Answer 4

I think it should be something like that. I tried to get a list of DeptID's first and then implement a NOT IN with contains :

var deptIDs = context.Persons
     .Where( p => !context.Depts
             .Select(d => new {DeptID = d.DeptID})
             .Contains( p.DeptID ) 
            )
     .Select( p => new { DeptID = n.DeptID } );