Background
I have a method that returns a Linq expression with the following signature:
public static Expression<Func<string, bool>> GetStringFilterExpression(IEnumerable<string> fields)
Which I then place into a local variable:
var filterby = GetStringFilterExpression(new[] {"Copy*Z"});
Given a Linq2Sql data context db and a table DbItems, this allows me to do this:
var names = db.DbItems.Select(d=>d.Name).Where(filterby);
var result = db.DbItems.Where(d=>names.Contains(d.Name);
Or I can use this helper method
public static IQueryable<T> MatchesFilter<T>(IQueryable<T> query, Expression<Func<T, string>> selector, Expression<Func<string, bool>> filter)
{
LambdaExpression lambda = (LambdaExpression)selector;
var predicate = Expression.Invoke(filter, selector.Body);
var lambdaPredicate = Expression.Lambda<Func<T, bool>>(predicate, lambda.Parameters);
return query.Where(lambdaPredicate);
}
And perform a slightly more concise query:
var result = MatchesPattern<DbItems>(db.DbItems, d=>d.Name, filterby);
Both methods work, returning the same results, and running the expression as a correctly filtered SQL query (no local collection).
Goal
What I would like to do is use the function inside the normal .Where() clause, so it can be combined with other logic or invoked on more than one field, for example:
var result = db.DbItems.Where(d => filterby(d.Name) && d.Active);
But to do that, I have to use a Func rather than an Expression. I tried unwrapping the expression like so:
public static Func<string, bool> CreateStringFilter(Expression<Func<string, bool>> filterby)
{
return p => filterby.Invoke(p);
}
var inlinefilter = CreateStringFilter(filterby);
Which does allow this to work:
var result = db.DbItems.ToList().Where(d => inlinefilter(d.Name));
However, that requires pulling the whole table down into a local collection. If I remove .ToList(), I get a "no supported translation to SQL" error.
Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL.
Even though it's the exact same code that runs as SQL when it's wrapped in the Expression.
Is there any way to get this to work as an inline string function in the .Where (or .Count, etc.) clause against the live data context instead of a local collection?
Follow-up/Workaround
Marked as duplicate, but I didn't feel the linked question answered this question, which is how to use an existing string,bool Expression against an arbitrary property (vs. creating custom property-aware expression trees in the accepted answer to the other question). Here's the workaround I came up with:
Add another helper method like this:
public static Expression<Func<T,bool>> PropFilter<T,T2>(Expression<Func<T, T2>> selector, Expression<Func<T2, bool>> filter)
{
LambdaExpression lambda = (LambdaExpression)selector;
var predicate = Expression.Invoke(filter, selector.Body);
return Expression.Lambda<Func<T, bool>>(predicate, lambda.Parameters);
}
Then I can create property-specific filters based on the original string filter, and chain them like this:
var filtername = PropFilter<DbItems, string>(d => d.Name, filterby);
var filterdesc = PropFilter<DbItems, string>(d => d.Description, filterby);
var propfilter = filtername.Or(filterdesc);
Finally, I am able to invoke that filter in Linq and combine with inline property filters like so:
var result = db.DbItems.Where(propfilter.And(d => d.Active));
I gather that's as close as I'm going to get to the original desired syntax.
