How to use Linq "NOT IN"

Question

I am using Entity Framework
Therefore, I want to write a sql command using two tables - the tblContractor and tbSiteByCont tables. It is like SQL

SELECT PKConID, Fname, Lname FROM tblContractor WHERE (PKConID NOT IN (SELECT FKConID FROM tbSiteByCont WHERE (FKSiteID = 13))) 

but I don’t know how to write in Linq.

I tried like this

  var query1 = from s in db.tblSiteByConts where s.FKSiteID == id select s.FKConID; var query = from c in db.tblContractors where c.PKConID != query1.Any() select Contractor; 

But that does not work. So how do I write it? What is the procedure? I am new to Linq.

Answer 1

 var _result = from a in tblContractor where !(from b in tbSiteByCont where FKSiteID == 13 select b.FKConID) .Contains(a.PKConID) select a; 

or

 var siteLst = tbSiteByCont.Where(y => y.FKSiteID == 13) .Select(x => x.FKConID); var _result = tblContractor.Where(x => !siteLst.Contains(x.PKConID)); 

Answer 2

I would use a HashSet, this ensures that you only evaluate the sequence once.

 var result = from p in tblContractor let hasht = new HashSet<int>((from b in tbSiteByCont where b.FKSiteID == 13 select b.PKConID).Distinct()) where !hasht.Contains(p.PKConID) select p; 

Answer 3

maybe it works too

 var _result = from a in tblContractor .Where(c => tbSiteByCont .Count(sbc => sbc.FKSiteID == 13 && c.PKConID == sbc.FKConID) == 0)