"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?

1
6
2/24/2016 6:34:14 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();
19
11/12/2015 9:44:10 AM

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



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow