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

Question

I have 2 collections which have an Email property in both collections. I need to get a list of the items in the first list where the 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

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 http://programminglinq.com/blogs/marcorusso/archive/2008/01/14/the-not-in-clause-in-linq-to-sql.aspx

Answer 2

var secondEmails = (from item in list2
                    select new { Email = item.Email }
                   ).ToList();

var matches = from item in list1
              where !secondEmails.Contains(item.Email)
              select new {Email = item.Email};

Answer 3

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

Answer 4

Example using List of int for simplicity.

List<int> list1 = new List<int>();
// fill data
List<int> list2 = new List<int>();
// fill data

var results = from i in list1
              where !list2.Contains(i)
              select i;

foreach (var result in results)
    Console.WriteLine(result.ToString());

Answer 5

items in the first list where the Email does not exist in the second list.

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

Answer 6

While Except is part of the answer, it's not the whole answer. By default, Except (like several of the LINQ operators) does a reference comparison on reference types. To compare by values in the objects, you'll have to

• implement IEquatable<T> in your type, or
• override Equals and GetHashCode in your type, or
• pass in an instance of a type implementing IEqualityComparer<T> for your type

Answer 7

In the case where one is using the ADO.Net entity framework, EchoStorm's solution also works perfectly. But it took me a few minutes to wrap my head around it. Assuming you have a database context, dc, and want to find rows in table x not linked in table y, the complete answer answer looks like:

var linked = 
  from x in dc.X
  from y in dc.Y
  where x.MyProperty == y.MyProperty
  select x;
var notLinked = 
  dc.X.Except(linked);

Answer 8

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 (.NET 4.0 only):

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.

Answer 9

Thank u Brett. Your suggestion helped me too. I had a list of Objects , and wanted to filter that using another list of objects. Thanks again....

If anyone needs, Please have a look at my code sample 'First, Get all the items present in the local branch db Dim _AllItems As List(Of LocalItem) = getAllItemsAtBranch(BranchId, RecordState.All)

        'Then get the Item Mappings Present for the branch
        Dim _adpt As New gItem_BranchesTableAdapter
        Dim dt As New ds_CA_HO.gItem_BranchesDataTable
        _adpt.FillBranchMappings(dt, BranchId)

        Dim _MappedItems As List(Of LocalItem) = (From _item As LocalItem In _AllItems Join _
                                       dr As ds_CA_HO.gItem_BranchesRow In dt _
                                       On _item.Id Equals dr.numItemID _
                                     Select _item).ToList

        _AllItems = _AllItems.Except(_MappedItems.AsEnumerable).ToList


        Return _AllItems