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.

1
286
3/6/2015 8:25:00 PM

Accepted Answer

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

283
5/26/2011 5:41:48 AM

You want the Except operator.

var answer = list1.Except(list2);

Better explanation here: http://blogs.msdn.com/charlie/archive/2008/07/12/the-linq-set-operators.aspx

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


Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon