Simplest approach to a dynamic where clause

Question

I'm hitting information overload when it comes to generating dynamic where clauses in EF. Some of the solutions look to be very dated (I'm targeting .NET 4.5 with EF 5).

Here's what I have:

public enum PersonTypes
{
    Lazy = 1,
    Awesome = 2,
    SuperHero = 3
}

public bool IncludeLazyPeople { get; set; }
public bool IncludeAwesomePeople { get; set; }
public bool IncludeSuperHeroPeople { get; set; }

Using EF I need to query for the people types that match the supplied bools.

I looked into this: http://www.albahari.com/nutshell/predicatebuilder.aspx

And this is what I came up with:

// create an expression that would include none of the person types
var personTypeExpression = PredicateBuilder.False<DAL.Models.Person>();

if (IncludeLazyPeople)
    personTypeExpression = personTypeExpression.Or(person => person.PersonType == (int)PersonTypes.Lazy);

if (IncludeAwesomePeople)
    personTypeExpression = personTypeExpression.Or(person => person.PersonType == (int)PersonTypes.Awesome);

if (IncludeSuperHeroPeople)
    personTypeExpression = personTypeExpression.Or(person => person.PersonType == (int)PersonTypes.SuperHero);

// filter the people for all included types
var filteredPeople = ctx.People.Where(personTypeExpression);

That failed to work because I didn't include the AsExpandable extension. Before I try that, I'm wondering if there is an easier approach?

score 0 · Answer 1 · 2013-04-06T22:57:28.327

0

var query = context.People.AsQueryable();

if (IncludeLazyPeople)
    query = query.Where(person => person.PersonType == (int)PersonTypes.Lazy);

if (IncludeAwesomePeople)
    query = query.Where(person => person.PersonType == (int)PersonTypes.Awesome);

if (IncludeSuperHeroPeople)
    query = query.Where(person => person.PersonType == (int)PersonTypes.SuperHero);

If you just need AND logic then you can call additional Where() methods on your query. Otherwise if you need a more powerful option then go with predicate builder or System.Linq.Expressions.

edited Apr 06 '13 at 22:57

answered Apr 06 '13 at 22:52

Woops, my example should have used OR instead of AND. I've updated the question. I'm looking into System.Linq.Expressions – mbursill Apr 06 '13 at 23:00

TDaver · Accepted Answer · 2013-04-07T08:18:13.540

0

ParameterExpression p = Expression.Parameter(typeof(Person));
Expression personType = Expression.MakeMemberAccess(p, typeof(Person).GetProperty("PersonType"));
Expression personTypeExpression = Expression.Constant(false);
if (IncludeLazyPeople)
    personTypeExpression = Expression.OrElse(personTypeExpression, Expression.Equal(personType, Expression.Constant((int)PersonTypes.Lazy)));

//... same as above only with different Constant values

// filter the people for all included types
var filteredPeople = ctx.People.Where(Expression.Lambda<Func<Person,bool>>(personTypeExpression,p));

This should do roughly what you'd want. Didn't check in compiler, hope not too many typos.

edited Apr 07 '13 at 08:18

answered Apr 06 '13 at 23:05

TDaver

7,164
5
47
94

Just a missing closing parenthesis on the OrElse, otherwise it works great. :-) – mbursill Apr 07 '13 at 00:04
@mbursill: also, I just noticed, you're targeting EF5, .net 4.5. Do you know you can have Enums instead of ints on your model then? in which case this code stays EXACTLY the same, just drop the `(int)`... – TDaver Apr 07 '13 at 08:19

Simplest approach to a dynamic where clause

2 Answers2