EF Core Global Query Filter Complex expression-CodePudding

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.

CodePudding user response：

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();