LINQ expressions can not be translated to Sql when combining specifications in Specification Pattern

Question

I am using the Specification pattern to perform db filtering, and avoid doing it on memory (I roughly followed this article). My base specification class is something like this:

    public abstract class Specification<T> : ISpecification<T>{

    public abstract Expression<Func<T, bool>> FilterExpr();

    public bool IsSatisfied(T entity)
    {
        Func<T, bool> func = this.FilterExpr().Compile();
        return func(entity);
    }

    public Specification<T> And(Specification<T> otherSpec)
    {
        return new CombinedSpecification<T>(this, otherSpec);
    }
}

From this base Specification class, multiple Strongly-typed specifications are derived, which work well on their own. However, the problem arises when trying to combine such specifications and evaluating the CombinedSpecification in my Repository class:

System.InvalidOperationException: The LINQ expression 'DbSet() .Where(c => c.ClientCode == __client_0 && c.Status == "Efective")' 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.

Here is a look at the CombinedSpecification class:

    internal class CombinedSpecification<T> : Specification<T>
    {
        private Specification<T> leftSpec;
        private Specification<T> rightSpec;

        public CombinedSpecification(Specification<T> aSpec, Specification<T> otherSpec)
        {
            this.leftSpec = aSpec;
            this.rightSpec = otherSpec;
        }

        public override Expression<Func<T, bool>> FilterExpr()
        {
            Expression<Func<T, bool>> firstExpr = this.leftSpec.FilterExpr();
            Expression<Func<T, bool>> secondExpr = this.rightSpec.FilterExpr();

            BinaryExpression combined = Expression.AndAlso(firstExpr.Body, secondExpr.Body);

            return Expression.Lambda<Func<T, bool>>(combined, firstExpr.Parameters.Single());
        }
    }

Just to be clear, filtering by a single specification works just fine, yet the LinQ expression seems impossible to translate when combining them (which seems weird to me since I'm not using any methods that are unsupported by SQL AFAIK). I avoided showing the Repository class to reduce this question's volume, but here is the relevant lines of my Find() method anyways:

    public IEnumerable<TEntity> Find(Specification<TEntity> spec)
        {
            IQueryable<TEntity> result = this.dbSet;

            if (spec != null)
            {
                result = result.Where(spec.FilterExpr());
            }

            return result.ToList();
        }

Thanks in advance for the help, I hope my first question was clearly stated!

Answer 1

The problem is that in your lambda expression combining implementation you use a body which combines the bodies of the two lambda expressions, but only the parameter of the first lambda expression (firstExpr.Parameters.Single()).

This way the second body still references a different parameter (even it visually might look the same, the parameters in lambda expressions are identifying by reference, not by name as you can think when creating them at compile time).

In other words, what you see in the error message

c => c.ClientCode == __client_0 && c.Status == "Efective"

is actually something like this

p0 => p0.ClientCode == __client_0 && p1.Status == "Efective"

which of course is invalid and cannot be translated (or compiled - try calling Compile() and you'll get runtime exception).

What you need is to make sure both operands as well as the resulting lambda expression use one and the same parameter instance.

You could still reuse the parameter of one of the operand, but you have to rebind the body of the other operand to the same parameter. Which usually is achieved with small custom ExpressionVisitor which finds all occurrences of a parameter inside the expression tree and replaces them with another parameter (or expression) - pretty much like string.Replace, but for expressions:

public static partial class ExpressionExtensions
{
    public static Expression ReplaceParameter(this Expression target, ParameterExpression parameter, Expression value)
        => new ParameterReplacer { Parameter = parameter, Value = value }.Visit(target);

    class ParameterReplacer : ExpressionVisitor
    {
        public ParameterExpression Parameter;
        public Expression Value;
        protected override Expression VisitParameter(ParameterExpression node)
            => node == Parameter ? Value : node;
    }
}

and modify the combining implementation with something like this

public override Expression<Func<T, bool>> FilterExpr()
{
    var firstExpr = this.leftSpec.FilterExpr();
    var secondExpr = this.rightSpec.FilterExpr();

    var parameter = firstExpr.Parameters[0]; // <--
    var combined = Expression.AndAlso(
        firstExpr.Body, 
        secondExpr.Body.ReplaceParameter(secondExpr.Parameters[0], parameter) // <--
    );

    return Expression.Lambda<Func<T, bool>>(combined, parameter); 
}