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.

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. — Craig G., Aug 23 '11 at 15:24

score 3 · Answer 1 · answered Jul 23 '12 at 08:48

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.

score 3 · Answer 2 · answered Aug 23 '11 at 05:25

3

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

answered Aug 23 '11 at 05:25

devio

36,858
7
80
143

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 – Craig G. Aug 23 '11 at 13:21

score 0 · Answer 3 · answered Aug 23 '11 at 05:26

0

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

answered Aug 23 '11 at 05:26

Florian Greinacher

14,478
1
35
53

score 0 · Answer 4 · answered Aug 23 '11 at 05:30

0

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

answered Aug 23 '11 at 05:30

Alistair

1,064
8
17

C# Predicate Builder with "NOT IN" functionality

4 Answers4