How to incorporate an Expression<> into an Entity Framework query?

Question

I'm trying to write a class that helps create a LINQ query dynamically.

protected Func<T, TColumn> GetColumn;

public MyClass(Func<T, TColumn> getColumn)
{
    GetColumn = getColumn;
}

public virtual IQueryable<T> ApplyFilter(IQueryable<T> query)
{
    if (FilterMode == FilterModeMatchAny)
        return query.Where(x => FilterIds.Contains(GetColumn(x)));
    return query;
}

This class is called like this:

MyClass<Location, string> myClass = new MyClass<Location, string>(l => l.State);

var locations = myClass.ApplyFilter(DbContext.Locations);

However, the code above fails:

The LINQ expression 'DbSet
.Where(l => Invoke(__GetJoiningTables_0, l[Location])
.Any(xx => __FilterIds_1
.Contains(Invoke(__GetColumn_2, xx)
)))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

The trouble appears to be the way I'm using GetColumn. So I changed the declaration of GetColumn so that it is now an expression.

protected Expression<Func<T, TColumn>> GetColumn;

The same argument being passed to my constructor can easily be converted to this type. I Only need to change the argument type.

But now how can I use this new GetColumn in my ApplyFilter() method?

Update:

Eventually, I also need to do the same thing to the following two expressions.

// Expressions to incorporate
protected Expression<Func<T, ICollection<TJoinTable>>> GetJoiningTables;
protected new Expression<Func<TJoinTable, TColumn>> GetColumn;

// Match any query
return query.Where(x => GetJoiningTables(x).Any(xx => FilterIds.Contains(GetColumn(xx))));

// Match all query
return query.Where(x => GetJoiningTables(x).Count(xx => FilterIds.Contains(GetColumn(xx))) >= FilterIds.Count());

Answer 1

Firstly, you need to keep the column as an expression, or c# will compile the lambda function and EF will be unable to extract which column it is;

protected Expression<Func<T, TColumn>> GetColumn;

public MyClass(Expression<Func<T, TColumn>> getColumn)
{
    GetColumn = getColumn;
}

Now, you could use the static methods of Expression to build an entire filter expression by hand. But in your case there's a shortcut. Since you can reuse the parameter and body of the input expression (eg x => x.Column), and wrap it in your Contains call (eg x => FilterIds.Contains(x.Column)).

EDIT:

Since you've now revealed that FilterIds is an IEnumerable<T>, then FilterIds.Contains() is actually the static extension method Enumerable.Contains(). The simplest way to find a matching generic static method is to create a matching delegate.

public virtual IQueryable<T> ApplyFilter(IQueryable<T> query)
{
    if (FilterMode == FilterModeMatchAny)
        return query.Where(
            Expression.Lambda<Func<T, bool>>(
                Expression.Call(
                    null,
                    new Func<IEnumerable<TColumn>,TColumn,bool>(Enumerable.Contains).Method,
                    Expression.Constant(FilterIds),
                    GetColumn.Body),
                GetColumn.Parameters)
        );
    return query;
}

EDIT:

.Where(x => GetJoiningTables(x).Any(...

Ok that's a can of worms.... I assume that what you're trying to do here is take a collection of navigations to other tables & columns, and apply a filter to that?

I find it helps to build an example Expression that you're trying to achieve. I assume you're trying to build expressions like;

Expression<Func<T,bool>> filter = t => 
    Enumerable.Any(t.Child1, c => FilterIds.Contains(c.ChildCol))
    || Enumerable.Any(t.Child2, c => FilterIds.Contains(c.OtherChildCol))
    ... ;

Expression<Func<T,bool>> filter = t => 
    Enumerable.Count(t.Child1, c => FilterIds.Contains(c.ChildCol))
    + Enumerable.Count(t.Child2, c => FilterIds.Contains(c.OtherChildCol))
    ... ;

I suggest that you write this code, let the c# compiler turn them into Expression graphs, and use the debugger to see what those graphs look like.

If possible, I'd recommend trying to find a way to "give to cesar what is caesar's". And assemble fragments of Expressions by inlining or transforming some template Expression.