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

Brennan_10366 0
Viewed 372436

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.

linq c#
16 Answers
Echostorm_128620

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

Scyllinice_251290

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;
user_81550 
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};
user_129500

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
user_54860

Alternatively you can do like this:

var result = list1.Where(p => list2.All(x => x.Id != p.Id));
user_31930300

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());
JAtkinson_29820 
 DynamicWebsiteEntities db = new DynamicWebsiteEntities();
    var data = (from dt_sub in db.Subjects_Details
                                //Sub Query - 1
                            let sub_s_g = (from sg in db.Subjects_In_Group
                                           where sg.GroupId == groupId
                                           select sg.SubjectId)
                            //Where Cause
                            where !sub_s_g.Contains(dt_sub.Id) && dt_sub.IsLanguage == false
                            //Order By Cause
                            orderby dt_sub.Subject_Name

                            select dt_sub)
                           .AsEnumerable();
                  
                                SelectList multiSelect = new SelectList(data, "Id", "Subject_Name", selectedValue);

    //======================================OR===========================================

    var data = (from dt_sub in db.Subjects_Details

                               
                            //Where Cause
                            where !(from sg in db.Subjects_In_Group
                                           where sg.GroupId == groupId
                                           select sg.SubjectId).Contains(dt_sub.Id) && dt_sub.IsLanguage == false

                            //Order By Cause
                            orderby dt_sub.Subject_Name

                            select dt_sub)

                           .AsEnumerable();
user_103667550
Related