How would you do a "not in" query with LINQ?

Question

I have two collections which have property Email in both collections. I need to get a list of the items in the first list where Email does not exist in the second list. With SQL I would just use "not in", but I do not know the equivalent in LINQ. How is that done?

So far I have a join, like...

var matches = from item1 in list1 join item2 in list2 on item1.Email equals item2.Email select new { Email = list1.Email }; 

But I cannot join since I need the difference and the join would fail. I need some way of using Contains or Exists I believe. I just have not found an example to do that yet.

Answer 1

You want the Except operator.

var answer = list1.Except(list2); 

Better explanation here: https://docs.microsoft.com/archive/blogs/charlie/linq-farm-more-on-set-operators

NOTE: This technique works best for primitive types only, since you have to implement an IEqualityComparer to use the Except method with complex types.

Answer 2

I don't know if this will help you but..

NorthwindDataContext dc = new NorthwindDataContext();     dc.Log = Console.Out;  var query =         from c in dc.Customers         where !(from o in dc.Orders                 select o.CustomerID)                .Contains(c.CustomerID)         select c;  foreach (var c in query) Console.WriteLine( c ); 

from The NOT IN clause in LINQ to SQL by Marco Russo