Entity Framework Filter "Expression<Func<T, bool>>"

Question

I'm trying to create a filter method for Entity framework List and understand better the

Expression<Func<...

I have a Test Function like this.

public IQueryable<T> Filter<T>(IEnumerable<T> src, Expression<Func<T, bool>> pred)
{
    return src.AsQueryable().Where(pred);
}

and if I do this:

context.Table.Filter(e => e.ID < 500);

or this:

context.Table.Filter(e => e.SubTable.Where(et => et.ID < 500).Count() > 0 && e.ID < 500);

it all works well.

But if I do this:

context.Table.Filter(e => e.SubTable.Filter(et => et.ID < 500).Count() > 0 && e.ID < 500);

or this:

context.Table.Where(e => e.SubTable.Filter(et => et.ID < 500).Count() > 0 && e.ID < 500);

I receive one error. LINQ to Entities does not recognise the method ...Filter...

Why does it work in one case and not in the other? What should I change in the filter to make it work with related tables? I prefer to stay away from other external libraries as what I want is to learn how it works and be able to use it in any scenario in future.

In the first two cases, the filter runs in the database correctly.

Answer 1

Jon and Tim already explained why it doesn't work.

Assuming that the filter code inside Filter is not trivial, you could change Filter so that it returns an expression EF can translate.

Let's assume you have this code:

context.Table.Where(x => x.Name.Length > 500); 

You can now create a method the returns this expression:

Expression<Func<YourEntity, bool>> FilterByNameLength(int length) {     return x => x.Name.Length > length; } 

Usage would be like this:

context.Table.Where(FilterByNameLength(500)); 

The expression you build inside FilterByNameLength can be arbitrarily complex as long as you could pass it directly to Where.

Answer 2

It's useful to understand the difference between Expression<Func<>> and Func<>.

An Expression e => e.ID < 500 stores the info about that expression: that there's a T e, that you're accessing the property ID, calling the < operator with the int value 500. When EF looks at that, it might turn it into something like [SomeTable].[ID] < 500.

A Func e => e.ID < 500 is a method equivalent to:

static bool MyMethod(T e) { return e.ID < 500; } 

It is compiled as IL code that does this; it's not designed to be 'reconstituted' into a SQL query or anything else, only run.

When EF takes your Expression, it must understand every piece of it, because it uses that to build a SQL query. It is programmed to know what the existing Where method means. It does not know what your Filter method means, even though it's a trivial method, so it just gives up.

Answer 3

Why it works in one case and not in the adder?

Because EF doesn't really "know" about your Filter method. It has no understanding of what it's meant to do, so it doesn't know how to translate it into SQL. Compare that with Where etc, which it does understand.

The version where you call it directly on the initial table works because that way you don't end up with an expression tree containing a call to Filter - it just calls Filter directly, which in turn does build up a query... but one which EF understands.

I'd be very surprised if you could work out a way of getting your Filter method to work within an EF query... but you've already said that using Where works anyway, so why use Filter at all? I'd use the Where version - or better yet, use the Any overload which takes a predicate:

context.Table.Filter(e => e.SubTable.Any(et => et.ID < 500) && e.ID < 500);