How to filter IEnumerable based on an entity input parameter

Question

I'm using now Entity framework- but it's a problem "shared" between all ORM's and even IEnumerable.

Let's say I have a method in MVC looks like this:

[HttpPost]
public ActionResult Foo(FooModel model)
{
    var context = new Context(); -- The EF session
    var data = context.Foo.Where(???).ToList();
    return View(data);
}

I want to query the context based on the input parameter like:

var data = context.Foo.Where(x => x.Date == model.Date &&
                             x.Name == model.Name &&
                             x.ItemCode = model.ItemCode).ToList();

But it's more complicated than that, because if one of the parameters above(Date\ Name\ ItemCode) is null I don't want to include it inside the query.
If I hard code it can looks similar to this:

var query =  context.Foo;

if (model.Date != null)
    query =query.Where(x => x.Date == model.Date);

if (model.ItemCode != null)
    query =query.Where(x => x.ItemCode == model.ItemCode);
...

There must be a simpler way than this.
I need a way to generate an expression of the type Expression<T, bool> to be used in the Where method.

[HttpPost]
public ActionResult Foo(FooModel model)
{
    var context = new Context(); -- The EF session
    var data = context.Foo.Where(THE_EXPRESSION).ToList();
    return View(data);
}

Is there a built-in way to build that expression? Is there a package in nuget that does it?

Update: There could be more than 30 properites in the model-entity; writing 30 times the Where for each query can be a pain in the neck:

.Where(model.Date != null, x => x.Date == model.Date)
.Where(model.Name != null, x => x.Name == model.Name)
.Where(model.ItemCode != null, x => x.ItemCode == model.ItemCode)
...
...
...
.ToList();

score 5 · Answer 1 · answered Sep 13 '12 at 10:45

5

Your hard coded method is the best method generally.

However you can try to make your life a little easier by writing an appropriate extension method to help keep the code clean.

Try this for example:

public static class QueryableEx
{
    public static IQueryable<T> Where<T>(
        this IQueryable<T> @this,
        bool condition,
        Expression<Func<T, bool>> @where)
    {
        return condition ? @this.Where(@where) : @this;
    }
}

Now you could write this code:

[HttpPost]
public ActionResult Foo(FooModel model)
{
    using (var context = new Context())
    {
        var data = context.Foo
            .Where(model.Date != null, x => x.Date == model.Date)
            .Where(model.Name != null, x => x.Name == model.Name)
            .Where(model.ItemCode != null, x => x.ItemCode == model.ItemCode)
            .ToList();
        return View(data);
    }
}

(Please don't forget to dispose of your context or use using to do it for you.)

answered Sep 13 '12 at 10:45

Enigmativity

113,464
11
89
172

+1 This gets my vote - screens which allow the user to search a large table by N! different permutations make it a nightmare to index at db level. By making each filter visible to the dev, thre is at least some visibility into the resulting query, and may cause some rethinking as to which fields (or combinations thereof) may be exposed to the user. – StuartLC Sep 13 '12 at 11:00
Thanks for your input and idea. But if my model-entity have more than **30** properites it write 30 times Where(...) can be a pain. – gdoron Sep 13 '12 at 11:19
@gdoron - Yes, but you must write them somewhere. If you try to do this with reflection and building expressions then I'm afraid you'd now have two problems. – Enigmativity Sep 13 '12 at 11:25
Why do you think it two problems now? @Amiram answer looks good to me. – gdoron Sep 13 '12 at 11:38
@gdoron - I like his answer too, but it only works with reference types, it is specific to the `mode.P != null` & `x.P == model.P` pattern, it is difficult to reason with and nearly impossible to debug. It is more maintainable to explicitly list out your predicates. – Enigmativity Sep 13 '12 at 12:02
@Enigmativity, you right about the reference thing, but this kind of comparison is written in the question too. Anyway, there is a way to do that for value type also, here: http://stackoverflow.com/a/353073/1495902 – Amiram Korach Sep 13 '12 at 12:10
@AmiramKorach. The case with value types is not important to me because we can use `Nullable` meaning `int? \ long? DateTime? ...` Thanks you both. – gdoron Sep 13 '12 at 14:55

Amiram Korach · Accepted Answer · 2012-09-13T11:40:41.400

Try that. This is using reflection and expressions to build the query dynamically. I tested it only with objects.

static IQueryable<T> Filter<T>(IQueryable<T> col, T filter)
{
    foreach (var pi in typeof(T).GetProperties())
    {
        if (pi.GetValue(filter) != null)
        {
            var param = Expression.Parameter(typeof(T), "t");
            var body = Expression.Equal(
                Expression.PropertyOrField(param, pi.Name),
                Expression.PropertyOrField(Expression.Constant(filter), pi.Name));
            var lambda = Expression.Lambda<Func<T, bool>>(body, param);
            col = col.Where(lambda);
        }
    }

    return col;
}

An example that shows how this method is used will be very helpful. — devinbost, May 14 '14 at 23:28

score 1 · Answer 3 · edited May 23 '17 at 12:12

1

I think you should encapsulate your logic into your Foo entity, e.g.

   public Foo
   {
     public bool isMatch(Model model)
     {
       // check your rules and return result
     }
   }

and use it in linq. Or look at Specification pattern

edited May 23 '17 at 12:12

Community

1
1

answered Sep 13 '12 at 10:52

syned

2,201
19
22

1

That only works with LINQ to Objects, not with the Entity Framework. – Daniel Hilgarth Sep 13 '12 at 11:02
You can return public Expression> IsSatisfiedBy() – syned Sep 13 '12 at 11:18
Which completely polutes your entity. – Daniel Hilgarth Sep 13 '12 at 11:26
Indeed, that could be feasable as it would encapsulate just exactly that part. – Daniel Hilgarth Sep 13 '12 at 11:48

How to filter IEnumerable based on an entity input parameter

3 Answers3

Linked