EF Core Global Query Filter Complex expression

November 12, 2021

I’m trying to implement a global query filter for tenancy within my application. I’ve got AssessmentModels that can have multiple owners, the owners coming from a 3rd party that I don’t have complete control over, but can adjust as needed. (And can manipulate before I save to my database) Right now, the Owners are stored as a semicolon delimited string (.e.g, team1;team2).

I’ve come up with the following which works for selecting data, but does not work for a global query filter:

private Expression<Func<AssessmentModel, bool>> GetAssessmentFilter()
{
    // The lambda parameter.
    var assessmentParameter = Expression.Parameter(typeof(AssessmentModel), "a");
    // Build the individual conditions to check against.
    var orConditions = _adminTeamNames
        .Select(keyword => (Expression<Func<AssessmentModel, bool>>)(a => EF.Functions.Like(a.Owners, $"%{keyword}%")))
        .Select(lambda => (Expression)Expression.Invoke(lambda, assessmentParameter))
        .ToList();

    // Combine the individual conditions to an expression tree of nested ORs.
    var orExpressionTree = orConditions
        .Skip(1)
        .Aggregate(
            orConditions.First(),
            (current, expression) => Expression.OrElse(expression, current));


    // Build the final predicate (a lambda expression), so we can use it inside of `.Where()`.
    var predicateExpression = (Expression<Func<AssessmentModel, bool>>)Expression.Lambda(
        orExpressionTree,
        assessmentParameter);

    return predicateExpression;
}

So,
var result = db.Assessments.Where(predicateExpression).ToList(); works, but modelBuilder.Entity<AssessmentModel>().HasQueryFilter(predicateExpression); gives the error:

System.InvalidOperationException : The LINQ expression 'DbSet<AssessmentModel>()
.Where(a => Invoke(a => value(DbFunctions).Like(a.Owners, "%Los%"), a)
 || Invoke(a => value(DbFunctions).Like(a.Owners, "%Atl%"), a)
)' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

>Solution :

AFAIK EF Core is not very good at translating invoke expressions. Try manually replacing parameter of build lambda with your custom parameter expression (i.e. assessmentParameter) :

var orConditions = _adminTeamNames
    .Select(keyword => (Expression<Func<AssessmentModel, bool>>)(a => EF.Functions.Like(a.Owners, $"%{keyword}%")))
    .Select(lambda => new ReplacingExpressionVisitor(lambda.Parameters, new []{assessmentParameter}).Visit(lambda.Body))
    .ToList();