“NOT IN” clause in LINQ to Entities

by

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<string> { "exception1", "exception2" };

   var query = myEntities.MyEntity
                         .Select(e => e.Name)
                         .Where(e => !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 => e.Name);

   var query = myEntities.MyEntity
                         .Select(e => 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).

More Related Contents:

  1. Problem with converting int to string in Linq to entities
  2. Dynamic where clause (OR) in Linq to Entities
  3. Linq-to-Entities Join vs GroupJoin
  4. ‘Contains()’ workaround using Linq to Entities?
  5. LINQ to Entities case sensitive comparison
  6. How to use DbContext.Database.SqlQuery(sql, params) with stored procedure? EF Code First CTP5
  7. The cast to value type ‘Int32’ failed because the materialized value is null
  8. Method cannot be translated into a store expression
  9. Bulk-deleting in LINQ to Entities
  10. The type of one of the expressions in the join clause is incorrect in Entity Framework
  11. Entity framework left join
  12. Linq to Entities, random order
  13. C# – code to order by a property using the property name as a string [duplicate]
  14. LEFT JOIN in LINQ to entities?
  15. How to compare only date components from DateTime in EF?
  16. How to do a Bulk Insert — Linq to Entities
  17. “The LINQ expression node type ‘Invoke’ is not supported in LINQ to Entities” – stumped!
  18. Simple Automapper Example
  19. The entity or complex type ‘ ‘ cannot be constructed in a LINQ to Entities query [duplicate]
  20. How to tell if an IEnumerable is subject to deferred execution?
  21. Linq to Entity Join table with multiple OR conditions
  22. ‘Date’ is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported
  23. linq to entities vs linq to objects – are they the same?
  24. How to use SQL ‘LIKE’ with LINQ to Entities? [duplicate]
  25. big issue in converting string to datetime using linq-to-entities
  26. The LINQ expression node type ‘ArrayIndex’ is not supported in LINQ to Entities
  27. How do you construct a LINQ to Entities query to load child objects directly, instead of calling a Reference property or Load()
  28. Only parameterless constructors and initializers are supported in LINQ to Entities message
  29. Linq to Entities : using ToLower() on NText fields
  30. Is Injection Possible through Dynamic LINQ?

Leave a Comment