NOT IN clause in LINQ to Entities

Question

Is there anyway I can create a not in clause like I would have in SQL Server in Linq to Entities

User · Answer

I created it in a more similar way to the SQL  I think it is easier to understand  var list    from a in listA AsEnumerable               join b in listB AsEnumerable   on a id equals b id into ab             from c in ab DefaultIfEmpty               where c    null             select new   id   c id  name   c nome    ToList

User · Answer

I have the following extension methods       public static bool IsIn lt T gt  this T keyObject  params T   collection                return collection Contains keyObject              public static bool IsIn lt T gt  this T keyObject  IEnumerable lt T gt  collection                return collection Contains keyObject              public static bool IsNotIn lt T gt  this T keyObject  params T   collection                return keyObject IsIn collection     false             public static bool IsNotIn lt T gt  this T keyObject  IEnumerable lt T gt  collection                return keyObject IsIn collection     false          Usage   var inclusionList   new List lt string gt     inclusion1    inclusion2     var query   myEntities MyEntity                       Select e   gt  e Name                        Where e   gt  e IsIn inclusionList     var exceptionList   new List lt string gt     exception1    exception2     var query   myEntities MyEntity                       Select e   gt  e Name                        Where e   gt  e IsNotIn exceptionList      Very useful as well when passing values directly   var query   myEntities MyEntity                       Select e   gt  e Name                        Where e   gt  e IsIn  inclusion1    inclusion2      var query   myEntities MyEntity                       Select e   gt  e Name                        Where e   gt  e IsNotIn  exception1    exception2

User · Answer

If you are using an in-memory collection as your filter  it s probably best to use the negation of  Contains     Note that this can fail if the list is too long  in which case you will need to choose another strategy  see below for using a strategy for a fully DB-oriented query       var exceptionList   new List lt string gt     exception1    exception2         var query   myEntities MyEntity                           Select e   gt  e Name                            Where e   gt   exceptionList Contains e Name      If you re excluding based on another database query using Except might be a better choice   Here is a link to the supported Set extensions in LINQ to Entities      var exceptionList   myEntities MyOtherEntity                                   Select e   gt  e Name       var query   myEntities MyEntity                           Select e   gt  e Name                            Except exceptionList     This assumes a complex entity in which you are excluding certain ones depending some property of another table and want the names of the entities that are not excluded   If you wanted the entire entity  then you d need to construct the exceptions as instances of the entity class such that they would satisfy the default equality operator  see docs

User · Answer

I have the following extension methods       public static bool IsIn lt T gt  this T keyObject  params T   collection                return collection Contains keyObject              public static bool IsIn lt T gt  this T keyObject  IEnumerable lt T gt  collection                return collection Contains keyObject              public static bool IsNotIn lt T gt  this T keyObject  params T   collection                return keyObject IsIn collection     false             public static bool IsNotIn lt T gt  this T keyObject  IEnumerable lt T gt  collection                return keyObject IsIn collection     false          Usage   var inclusionList   new List lt string gt     inclusion1    inclusion2     var query   myEntities MyEntity                       Select e   gt  e Name                        Where e   gt  e IsIn inclusionList     var exceptionList   new List lt string gt     exception1    exception2     var query   myEntities MyEntity                       Select e   gt  e Name                        Where e   gt  e IsNotIn exceptionList      Very useful as well when passing values directly   var query   myEntities MyEntity                       Select e   gt  e Name                        Where e   gt  e IsIn  inclusion1    inclusion2      var query   myEntities MyEntity                       Select e   gt  e Name                        Where e   gt  e IsNotIn  exception1    exception2

User · Answer

I created it in a more similar way to the SQL  I think it is easier to understand  var list    from a in listA AsEnumerable               join b in listB AsEnumerable   on a id equals b id into ab             from c in ab DefaultIfEmpty               where c    null             select new   id   c id  name   c nome    ToList

User · Answer

I took a list and used    MyList Contains table columb tostring      Note  Make sure to use List and not Ilist

User · Answer

Try   from p in db Products where  theBadCategories Contains p Category  select p    What s the SQL query you want to translate into a Linq query

User · Answer

Try   from p in db Products where  theBadCategories Contains p Category  select p    What s the SQL query you want to translate into a Linq query

User · Answer

Try   from p in db Products where  theBadCategories Contains p Category  select p    What s the SQL query you want to translate into a Linq query

User · Answer

If you are using an in-memory collection as your filter  it s probably best to use the negation of  Contains     Note that this can fail if the list is too long  in which case you will need to choose another strategy  see below for using a strategy for a fully DB-oriented query       var exceptionList   new List lt string gt     exception1    exception2         var query   myEntities MyEntity                           Select e   gt  e Name                            Where e   gt   exceptionList Contains e Name      If you re excluding based on another database query using Except might be a better choice   Here is a link to the supported Set extensions in LINQ to Entities      var exceptionList   myEntities MyOtherEntity                                   Select e   gt  e Name       var query   myEntities MyEntity                           Select e   gt  e Name                            Except exceptionList     This assumes a complex entity in which you are excluding certain ones depending some property of another table and want the names of the entities that are not excluded   If you wanted the entire entity  then you d need to construct the exceptions as instances of the entity class such that they would satisfy the default equality operator  see docs

User · Answer

Try   from p in db Products where  theBadCategories Contains p Category  select p    What s the SQL query you want to translate into a Linq query

User · Answer

If you are using an in-memory collection as your filter  it s probably best to use the negation of  Contains     Note that this can fail if the list is too long  in which case you will need to choose another strategy  see below for using a strategy for a fully DB-oriented query       var exceptionList   new List lt string gt     exception1    exception2         var query   myEntities MyEntity                           Select e   gt  e Name                            Where e   gt   exceptionList Contains e Name      If you re excluding based on another database query using Except might be a better choice   Here is a link to the supported Set extensions in LINQ to Entities      var exceptionList   myEntities MyOtherEntity                                   Select e   gt  e Name       var query   myEntities MyEntity                           Select e   gt  e Name                            Except exceptionList     This assumes a complex entity in which you are excluding certain ones depending some property of another table and want the names of the entities that are not excluded   If you wanted the entire entity  then you d need to construct the exceptions as instances of the entity class such that they would satisfy the default equality operator  see docs

User · Answer

If you are using an in-memory collection as your filter  it s probably best to use the negation of  Contains     Note that this can fail if the list is too long  in which case you will need to choose another strategy  see below for using a strategy for a fully DB-oriented query       var exceptionList   new List lt string gt     exception1    exception2         var query   myEntities MyEntity                           Select e   gt  e Name                            Where e   gt   exceptionList Contains e Name      If you re excluding based on another database query using Except might be a better choice   Here is a link to the supported Set extensions in LINQ to Entities      var exceptionList   myEntities MyOtherEntity                                   Select e   gt  e Name       var query   myEntities MyEntity                           Select e   gt  e Name                            Except exceptionList     This assumes a complex entity in which you are excluding certain ones depending some property of another table and want the names of the entities that are not excluded   If you wanted the entire entity  then you d need to construct the exceptions as instances of the entity class such that they would satisfy the default equality operator  see docs

User · Answer

I took a list and used    MyList Contains table columb tostring      Note  Make sure to use List and not Ilist

[c#] "NOT IN" clause in LINQ to Entities

Examples related to c#

Examples related to linq-to-entities