C# Predicate Builder with "NOT IN" functionality

Question

With PredicateBuilder how do I get functionality similar to the SQL IN or NOT IN query?

For example I have a list of IDs and I want to select all of the People whose IDs either Match or do not match the IDs.

The people match functionality is fairly straightforward (although there may be a better way to do it)

var predicate = PredicateBuilder.False<Person>()
foreach (int i in personIDs)
{
  int temp = i;
  predicate = predicate.Or(e=>e.PersonID == temp);
}
return persons.Where(predicate);

So how do I get the opposite? I want all persons whose IDs are not in the personIDs list.

Willumsen

As a workaround I was able to get the list of PersonIDs prefiltered so I can just use the code above. It's not ideal, but it will work. – Willumsen

Answer 1

Ask De Morgan:

NOT (P OR Q) = (NOT P) AND (NOT Q)

To have your code generate the equivalent of a NOT IN condition, rewrite as

var predicate = PredicateBuilder.True<Person>()

and

predicate = predicate.And(e=>e.PersonID != temp);

Substructure

That is what I tried the first time, however I am getting an empty result set from SQL. When I inspect the SQL from Profiler I get something to the effect of SELECT CAST(NULL as varchar(1)) as [C1], . . . FROM (SELECT 1 as X) AS [SingleRowTable1] WHERE 1=0 – Willumsen

Answer 2

Do you use Entity Framework?

Then you can build the query without PredicateBuilder:

var personIds = new List<int>() { 8,9,10 };
var query = persons.Where(it => !personIds.Contains(it.PersonId));

From this LINQ statement a SQL NOT IN query is created.

Vicarage

Answer 3

Is this what you want?

var predicate = PredicateBuilder.True<Person>()
foreach (int i in personIDs)
{
    int temp = i;
    predicate = predicate.And(e => e.PersonID != temp);
}
return persons.Where(predicate);

Nationalism

Answer 4

Without looking at the api....

var predicate = PredicateBuilder.True<Person>()
foreach (int i in personIDs)
{
    int temp = i;
    predicate = predicate.And(e=>e.PersonID != temp);
}
return persons.Where(predicate);

Paprika