With LINQ, how would you handle a “not in” query?

Problem

I have two collections, both of which have the property Email. I need a list of the things in the first list that do not have an email address in the second list. I’d just use “not in” in SQL, but I’m not sure what the LINQ equivalent is. How does one go about doing that?

So far, I’ve had a join…

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

However, I am unable to join since I require the difference, and the join would fail. I believe I’ll need to use Contains or Exists in some way. I just haven’t come across an example of how to accomplish that yet.

Asked by Brennan

Solution #1

The Except operator is what you’re looking for.

var answer = list1.Except(list2);

More information can be found at https://docs.microsoft.com/archive/blogs/charlie/linq-farm-more-on-set-operator

NOTE: This technique is best for primitive types because using the Except method with complicated types requires implementing an IEqualityComparer.

Answered by Echostorm

Solution #2

I’m not sure if this will help, 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 );

Marco Russo’s The NOT IN clause in LINQ to SQL

Answered by Robert Rouse

Solution #3

I’ve discovered that starting with a collection of in-memory objects and querying against a database is the best way to go:

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

In SQL, this creates a lovely WHERE… IN (…) clause.

Answered by StriplingWarrior

Solution #4

from item1 in List1
where !(list2.Any(item2 => item2.Email == item1.Email))
select item1;

Answered by Amy B

Solution #5

You can use a combination of Where and Any for finding not in:

var NotInRecord =list1.Where(p => !list2.Any(p2 => p2.Email  == p.Email));

Answered by DevT

Post is based on https://stackoverflow.com/questions/183791/how-would-you-do-a-not-in-query-with-linq