"Not In" in Entity Framework

Question

I have the following Entity

public class Person
{
    public int PersonId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

and have a list List<Person> badGuys

what I want to do is select from all persons except those in badGuys List

My Code

context.Persons
    .where(p => !badGuys.Contain(p))
    .ToList()

but I get an error

Only primitive types or enumeration types are supported in this context.

How to fix this?

Answer 1

You could make an array containing the ids of the bad guys and filter out those ids (which are of a primitive type, so it should work):

var badGuyIds = badGuys.Select(x => x.PersonId).ToArray();

context.Persons
    .Where(p => !badGuyIds.Contain(p.PersonId))
    .ToList();

Answer 2

You can use the clause ALL with the distinct(!=)

var badBoys= from P in context.Persons
         where badGuys.All(a => a.PersonId!= P.PersonId)
         select P;

Answer 3

You can implement your own method to make necessary expression tree, like here:

    public static IQueryable<TEntity> WhereNotIn<TEntity, TValue>(
        this IQueryable<TEntity> queryable,
        Expression<Func<TEntity, TValue>> valueSelector,
        IEnumerable<TValue> values)
        where TEntity : class
    {
        if (queryable == null)
            throw new ArgumentNullException("queryable");

        if (valueSelector == null)
            throw new ArgumentNullException("valueSelector");

        if (values == null)
            throw new ArgumentNullException("values");

        if (!values.Any())
            return queryable.Where(e => true);

        var parameterExpression = valueSelector.Parameters.Single();

        var equals = from value in values
                     select Expression.NotEqual(valueSelector.Body, Expression.Constant(value, typeof (TValue)));

        var body = equals.Aggregate(Expression.And);

        return queryable.Where(Expression.Lambda<Func<TEntity, bool>>(body, parameterExpression));
    }
}

And now you can call this extension method

var badGuys = new int[] { 100, 200, 300 };
context.Persons.WhereNotIn(p => p.PersionId, badGuys);

This method makes the same thing as:

context.Persons.Where(p => p.PersonId != badGuys[0]
                        && p.PersonId != badGuys[1]
                        . . .
                        && p.PersonId != badGuys[N]);

for each element of badGuys enumerable object.

An additional advantage of the method is its universality, cause you can call it for any property of any entity, f.e. context.Persons.WhereNotIn(p => p.LastName, new[] { "Smith", "Brown", "Jones" }).

Answer 4

LINQ to ENTITIES: NOT CONTAINS METHOD:

The method should be .Contains() not .Contain. This requires a primitive type enumeration, so just enumerate your key field of your badGuys collection and call .Contains() on it in your Where clause when you query your context.

//get the list of bad guys
List<Person> badGuys = ...
//get simple primitive type enumeration
var badGuysIDs = badGuys.Select(t => t.PersonId).ToList();

using(Context c = new Context())
{
   var badPersons = c.Persons.Where(t => !badGuysIDs.Contains(t.PersonId)).ToList();
}

LINQ TO OBJECTS EXCEPT() METHOD:

Another option, if you already have your List<Person> of badguys is to use the .Except() method on your collection. This will have to be done after you enumerate your collection to objects.

//get the list of bad guys
List<Person> badGuys = ...

using(Context c = new Context())
{
   //enumerate Persons, then exclude badPersons
   var goodPersons = c.Persons.ToList().Except(badPersons);
}

LINQ to ENTITIES EXCEPT() METHOD:

The above method will not be ideal if you have a very large number of rows in your Persons table, because it will enumerate the entire collection. If you have a large number of rows, you will want to perform the exclusion on the database side before enumerating. To do this, you simply have to tell the database how to compare two objects for equality as in This excellent SO answer.

public class PersonComparer: IEqualityComparer<Persons> 
{
     public bool Equals(Person x, Person y) {return x.Id == y.Id;}
     public int GetHashCode(Person person) {return person.PersonId.GetHashCode();}
}


using(Context c = new Context())
{
    //get the list of bad guys
    List<Person> badGuys = ...

   var goodPersons = c.Persons.Except(badPersons, PersonComparer()).ToList();
}