NOT IN clause in LINQ to Entities

Question

In any case, can I create a sentence not in the form that I would use in SQL Server in Linq for Entities?

Answer 1

If you use a collection in memory as a filter, it is best to use the negation of Contains (). Please note that this may fail if the list is too long, in which case you will need to choose a different strategy (see below to use the strategy for a fully database-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 exclude another database query using Except , this might be a better choice. (Below 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 object in which you exclude certain of them, depending on some property of another table, and want the names of the objects not to be excluded. If you need the whole object, then you will need to create exceptions as instances of the entity class so that they satisfy the default equality operator (see docs ).

Answer 2

Try:

 from p in db.Products where !theBadCategories.Contains(p.Category) select p; 

What SQL query do you want to translate into a Linq query?

Answer 3

I have the following extension methods:

  public static bool IsIn<T>(this T keyObject, params T[] collection) { return collection.Contains(keyObject); } public static bool IsIn<T>(this T keyObject, IEnumerable<T> collection) { return collection.Contains(keyObject); } public static bool IsNotIn<T>(this T keyObject, params T[] collection) { return keyObject.IsIn(collection) == false; } public static bool IsNotIn<T>(this T keyObject, IEnumerable<T> collection) { return keyObject.IsIn(collection) == false; } 

Using:

 var inclusionList = new List<string> { "inclusion1", "inclusion2" }; var query = myEntities.MyEntity .Select(e => e.Name) .Where(e => e.IsIn(inclusionList)); var exceptionList = new List<string> { "exception1", "exception2" }; var query = myEntities.MyEntity .Select(e => e.Name) .Where(e => e.IsNotIn(exceptionList)); 

It is also very useful with direct transfer of values:

 var query = myEntities.MyEntity .Select(e => e.Name) .Where(e => e.IsIn("inclusion1", "inclusion2")); var query = myEntities.MyEntity .Select(e => e.Name) .Where(e => e.IsNotIn("exception1", "exception2")); 

Answer 4

I took the list and used

 !MyList.Contains(table.columb.tostring()) 

Note. Be sure to use List, not Ilist

Answer 5

I created it in a more similar way to 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();