Build a linq query based on conditional values

Question

I have a large record set of people and each of those people are in a country.

I cam retrieve all people in Australia using Entity Framework with the following:

var people = db.People.Where(x=>x.Country == "Australia")

What I'm not sure how to do is to retrieve people that are in Country X or Country Y based on a set of Boolean values.

Ie:

bool USA = true;
bool Australia = false;
bool UK = true;
bool China = false;

How do I build a linq query that in this case would give me:

var people = db.People.Where(x=>x.Country == "USA" || x.Country == "UK")

Thanks

`PredicateBuilder` and a bunch of `if` statements is what you're looking for. — MarcinJuraszek, Apr 12 '15 at 05:41
The problem is that there are 10 countries so quite a few permutations. -) Was hoping for a fancy way using a Dict or something — Evonet, Apr 12 '15 at 05:42
permutations? See my answer. There are no permutations involved. — MarcinJuraszek, Apr 12 '15 at 05:46
Hmm, getting [System.NotSupportedException] --- {"The LINQ expression node type 'Invoke' is not supported in LINQ to Entities."} trying to use PredicateBuilder. Any ideas? — Evonet, Apr 12 '15 at 06:14
All good, solved with http://stackoverflow.com/questions/22406952/keep-getting-the-linq-expression-node-type-invoke-is-not-supported-in-linq-to — Evonet, Apr 12 '15 at 06:16

score 5 · Accepted Answer · answered Apr 12 '15 at 05:46

5

You should use PredicateBuilder:

var predicate = PredicateBuilder.False<People>();

if (USA)
    predicate = predicate.Or(p => p.Country == "USA");
if (Australia)
    predicate = predicate.Or(p => p.Country == "Australia");

// ...

var people = dp.People.Where(predicate);

answered Apr 12 '15 at 05:46

MarcinJuraszek

124,003
15
196
263

I think it's likely the link to Joe's code would remain valid for a long time to come. However, the implementation is very short and IMHO would be appropriate to quote here in its entirety, or at least the `Or()` method part. That would ensure that this answer remains useful as long as the question exists. – Peter Duniho Apr 12 '15 at 06:09

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

1

PredicateBuilder is the right answer. As an alternative, you could do something like:

var countries = new List<string>();

if(USA) countries.Add("USA");
if(Australia) countries.Add("Australia");
if(UK) countries.Add("UK");

// ...

var people = dp.People.Where(x => countries.Contains(x.Country));

This would be translated to a WHERE IN SQL clause

Update

As the comments point out, in a Linq-To-Entities (or Linq-To-SQL) scenario, it doesn't matter, but if you plan to use this for Linq-To-Objects, it'd be wiser to use a HashSet<string> instead of a List<string> for performance reasons

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 12 '15 at 05:54

Jcl

27,696
5
61
92

@PeterDuniho It will be translated into SQL anyway, so type of collection doesn't matter - `Contains` will be translated into `IN` clause and never actually called. – MarcinJuraszek Apr 12 '15 at 06:08
@PeterDuniho you are right, I'll modify the answer, I was thinking of Entity Framework, not Linq-To-Objects – Jcl Apr 12 '15 at 06:28

score 0 · Answer 3 · answered Apr 12 '15 at 07:01

Try this:

//You can add, remove or change filters
var tempDictionary = new Dictionary<string, bool>
{
    {"USA", true},
    {"Australia", false},
    {"UK", true},
    {"China", false},
};
//Get relevant filters
var tempFilter = tempDictionary
                 .Where(item => item.Value)
                 .Select(item => item.Key)
                 .ToArray();
var tempPeople = db
                 .People
                 .Where(x => tempFilter.Contains(x.Country));

Build a linq query based on conditional values

3 Answers3

Update