"Not In" in Entity Framework

Question

"Not In" in Entity Framework

c# entity-framework linq

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?

Mohamed Badr

Popular Answer

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" }).

Mark Shevchenko

View more on Stack Overflow

Cristian Lupascu · Accepted Answer · 11/12/2015 9:44:10 AM

Accepted Answer

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();

Cristian Lupascu

"Not In" in Entity Framework

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related