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

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.

Solution 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.

Solution 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

Solution 3:

For people who start with a group of in-memory objects and are querying against a database, I've found this to be the best way to go:

var itemIds = inMemoryList.Select(x => x.Id).ToArray();
var otherObjects = context.ItemList.Where(x => !itemIds.Contains(x.Id));

This produces a nice WHERE ... IN (...) clause in SQL.