EF- How to do a 'Not In' using Linq to Entities

Question

Hi- I'm using the Entity framework 3.5 and am having trouble running this query.

I have the following entities: Applicant, application, and applicationstatusHistory (tracking job applicants)

I'm looking for matches in Application where there are no matching applicationids in applicationstatusHistory with an id of -insert param here-. The 2 entities are joined by applicationid.

Here's my current statement:

 applications = context.Application.Include("Applicant").Include("ApplicationStatusHistory")
                        .Where(LinqExtension.BuildContainsExpression<Application, int>(a => a.Id, applicationIds))
                        .ToList();

Answer 1

Not sure what's going ion in the BuildContainsExpression, but here's a WhereNotIn method that I found somewhere that I've been using:

public static IQueryable<T> WhereNotIn<T, TValue>(
    this IQueryable<T> query,
    Expression<Func<T, TValue>> propertySelector,
    IEnumerable<TValue> values)
{
    if (propertySelector == null)
    {
        throw new ArgumentNullException("propertySelector");
    }

    if (values == null || !values.Any())
    {
        return query.Where(i => true);
    }

    var param = propertySelector.Parameters.Single();
    var unequals = values.Select(value => (Expression)Expression.NotEqual(propertySelector.Body, Expression.Constant(value, typeof(TValue))));
    var body = unequals.Aggregate<Expression>((accumulate, unequal) => Expression.And(accumulate, unequal));
    var lambda = Expression.Lambda<Func<T, bool>>(body, param);

    return query.Where(lambda);
}