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

User · Answer

You can take both the collections in two different lists  say list1 and list2   Then just write  list1 RemoveAll Item   gt  list2 Contains Item      This will work

User · Answer

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 lt T gt  in your type  or override Equals and GetHashCode in your type  or pass in an instance of a type implementing IEqualityComparer lt T gt  for your type

User · 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

User · Answer

You can use a combination of Where and Any for finding not in   var NotInRecord  list1 Where p   gt   list2 Any p2   gt  p2 Email     p Email

User · Answer

One could also use All    var notInList   list1 Where p   gt  list2 All p2   gt  p2 Email    p Email

User · Answer

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 · Answer

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   gt  x Id  ToArray    var otherObjects   context ItemList Where x   gt   itemIds Contains x Id      This produces a nice WHERE     IN       clause in SQL

User · Answer

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 lt T gt  in your type  or override Equals and GetHashCode in your type  or pass in an instance of a type implementing IEqualityComparer lt T gt  for your type

User · Answer

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   gt  x Id  ToArray    var otherObjects   context ItemList Where x   gt   itemIds Contains x Id      This produces a nice WHERE     IN       clause in SQL

User · Answer

Example using List of int for simplicity   List lt int gt  list1   new List lt int gt        fill data List lt int gt  list2   new List lt int gt        fill data  var results   from i in list1               where  list2 Contains i                select i   foreach  var result in results      Console WriteLine result ToString

User · Answer

For anyone who also wants to use a SQL-alike IN operator in C   download this package        Mshwf NiceLinq   It has In and NotIn methods   var result   list1 In x   gt  x Email  list2 Select z   gt  z Email      Even you can use it this way  var result   list1 In x   gt  x Email   a b com    b c com    c d com

User · Answer

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     In response to Andy s comment  yes  one can have two from s in a LINQ query  Here s a complete working example  using lists  Each class  Foo and Bar  has an Id  Foo has a  foreign key  reference to Bar via Foo BarId  The program selects all Foo s not linked to a corresponding Bar   class Program       static void Main string   args                   Creates some foos         List lt Foo gt  fooList   new List lt Foo gt             fooList Add new Foo   Id   1  BarId   11             fooList Add new Foo   Id   2  BarId   12             fooList Add new Foo   Id   3  BarId   13             fooList Add new Foo   Id   4  BarId   14             fooList Add new Foo   Id   5  BarId   -1             fooList Add new Foo   Id   6  BarId   -1             fooList Add new Foo   Id   7  BarId   -1                 Create some bars         List lt Bar gt  barList   new List lt Bar gt             barList Add new Bar   Id   11             barList Add new Bar   Id   12             barList Add new Bar   Id   13             barList Add new Bar   Id   14             barList Add new Bar   Id   15             barList Add new Bar   Id   16             barList Add new Bar   Id   17              var linked   from foo in fooList                      from bar in barList                      where foo BarId    bar Id                      select foo          var notLinked   fooList Except linked           foreach  Foo item in notLinked                        Console WriteLine                  String Format                   Foo Id   0    Bar Id   1                    item Id  item BarId                      Console WriteLine  Any key to continue               Console ReadKey             class Foo       public int Id   get  set        public int BarId   get  set       class Bar       public int Id   get  set

User · Answer

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 lt T gt  in your type  or override Equals and GetHashCode in your type  or pass in an instance of a type implementing IEqualityComparer lt T gt  for your type

User · Answer

You can take both the collections in two different lists  say list1 and list2   Then just write  list1 RemoveAll Item   gt  list2 Contains Item      This will work

User · Answer

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 · Answer

Example using List of int for simplicity   List lt int gt  list1   new List lt int gt        fill data List lt int gt  list2   new List lt int gt        fill data  var results   from i in list1               where  list2 Contains i                select i   foreach  var result in results      Console WriteLine result ToString

User · 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

User · Answer

items in the first list where the Email does not exist in the second list     from item1 in List1 where   list2 Any item2   gt  item2 Email    item1 Email   select item1

User · Answer

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   amp  amp  dt sub IsLanguage    false                               Order By Cause                             orderby dt sub Subject Name                              select dt sub                              AsEnumerable                                                       SelectList multiSelect   new SelectList data   quot Id quot    quot Subject Name quot   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   amp  amp  dt sub IsLanguage    false                                Order By Cause                             orderby dt sub Subject Name                              select dt sub                               AsEnumerable

User · Answer

items in the first list where the Email does not exist in the second list     from item1 in List1 where   list2 Any item2   gt  item2 Email    item1 Email   select item1

User · Answer

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

User · Answer

Example using List of int for simplicity   List lt int gt  list1   new List lt int gt        fill data List lt int gt  list2   new List lt int gt        fill data  var results   from i in list1               where  list2 Contains i                select i   foreach  var result in results      Console WriteLine result ToString

User · Answer

items in the first list where the Email does not exist in the second list     from item1 in List1 where   list2 Any item2   gt  item2 Email    item1 Email   select item1

User · Answer

Couldn t you do an outer join  only selecting the items from the first list if the group is empty  Something like   Dim result    From a In list1               Group Join b In list2                    On a Value Equals b Value                    Into grp   Group               Where Not grp Any               Select a    I m unsure whether this would work in any sort of efficient way with the Entity framework

User · Answer

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

User · Answer

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

User · Answer

Example using List of int for simplicity   List lt int gt  list1   new List lt int gt        fill data List lt int gt  list2   new List lt int gt        fill data  var results   from i in list1               where  list2 Contains i                select i   foreach  var result in results      Console WriteLine result ToString

User · Answer

I did not test this with LINQ nbsp to nbsp Entities   NorthwindDataContext dc   new NorthwindDataContext        dc Log   Console Out   var query           from c in dc Customers      where  dc Orders Any o   gt  o CustomerID    c CustomerID         select c    Alternatively   NorthwindDataContext dc   new NorthwindDataContext        dc Log   Console Out   var query           from c in dc Customers      where dc Orders All o   gt  o CustomerID    c CustomerID         select c   foreach  var c in query       Console WriteLine  c

User · Answer

I did not test this with LINQ nbsp to nbsp Entities   NorthwindDataContext dc   new NorthwindDataContext        dc Log   Console Out   var query           from c in dc Customers      where  dc Orders Any o   gt  o CustomerID    c CustomerID         select c    Alternatively   NorthwindDataContext dc   new NorthwindDataContext        dc Log   Console Out   var query           from c in dc Customers      where dc Orders All o   gt  o CustomerID    c CustomerID         select c   foreach  var c in query       Console WriteLine  c

User · Answer

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 · Answer

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     In response to Andy s comment  yes  one can have two from s in a LINQ query  Here s a complete working example  using lists  Each class  Foo and Bar  has an Id  Foo has a  foreign key  reference to Bar via Foo BarId  The program selects all Foo s not linked to a corresponding Bar   class Program       static void Main string   args                   Creates some foos         List lt Foo gt  fooList   new List lt Foo gt             fooList Add new Foo   Id   1  BarId   11             fooList Add new Foo   Id   2  BarId   12             fooList Add new Foo   Id   3  BarId   13             fooList Add new Foo   Id   4  BarId   14             fooList Add new Foo   Id   5  BarId   -1             fooList Add new Foo   Id   6  BarId   -1             fooList Add new Foo   Id   7  BarId   -1                 Create some bars         List lt Bar gt  barList   new List lt Bar gt             barList Add new Bar   Id   11             barList Add new Bar   Id   12             barList Add new Bar   Id   13             barList Add new Bar   Id   14             barList Add new Bar   Id   15             barList Add new Bar   Id   16             barList Add new Bar   Id   17              var linked   from foo in fooList                      from bar in barList                      where foo BarId    bar Id                      select foo          var notLinked   fooList Except linked           foreach  Foo item in notLinked                        Console WriteLine                  String Format                   Foo Id   0    Bar Id   1                    item Id  item BarId                      Console WriteLine  Any key to continue               Console ReadKey             class Foo       public int Id   get  set        public int BarId   get  set       class Bar       public int Id   get  set

User · Answer

Couldn t you do an outer join  only selecting the items from the first list if the group is empty  Something like   Dim result    From a In list1               Group Join b In list2                    On a Value Equals b Value                    Into grp   Group               Where Not grp Any               Select a    I m unsure whether this would work in any sort of efficient way with the Entity framework

User · Answer

One could also use All    var notInList   list1 Where p   gt  list2 All p2   gt  p2 Email    p Email

User · Answer

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 · Answer

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

User · Answer

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 lt T gt  in your type  or override Equals and GetHashCode in your type  or pass in an instance of a type implementing IEqualityComparer lt T gt  for your type

User · Answer

You can use a combination of Where and Any for finding not in   var NotInRecord  list1 Where p   gt   list2 Any p2   gt  p2 Email     p Email

User · Answer

For anyone who also wants to use a SQL-alike IN operator in C   download this package        Mshwf NiceLinq   It has In and NotIn methods   var result   list1 In x   gt  x Email  list2 Select z   gt  z Email      Even you can use it this way  var result   list1 In x   gt  x Email   a b com    b c com    c d com

User · 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

User · Answer

Alternatively you can do like this   var result   list1 Where p   gt  list2 All x   gt  x Id    p Id

User · Answer

Alternatively you can do like this   var result   list1 Where p   gt  list2 All x   gt  x Id    p Id

User · Answer

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   amp  amp  dt sub IsLanguage    false                               Order By Cause                             orderby dt sub Subject Name                              select dt sub                              AsEnumerable                                                       SelectList multiSelect   new SelectList data   quot Id quot    quot Subject Name quot   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   amp  amp  dt sub IsLanguage    false                                Order By Cause                             orderby dt sub Subject Name                              select dt sub                               AsEnumerable

User · 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

User · Answer

items in the first list where the Email does not exist in the second list     from item1 in List1 where   list2 Any item2   gt  item2 Email    item1 Email   select item1

[c#] How would you do a "not in" query with LINQ?

Examples related to c#

Examples related to linq