Chaining predicates to use inside .Where clause

Question

I want to create EFCore query that will return all entities that meet some conditions for their related entities.

For example entities look like this (that's pretty simplified example):

public class MyEntity
{
   public int Id { get; set; }
   public List<MyOtherEntity> OtherEntities { get; set; }
}

public class MyOtherEntity
{
   public int Id { get; set; }
   public int SomeProperty1 { get; set; }
   public int SomeProperty2 { get; set; }
}

And I have a method that takes array of simplified MyOtherEntity objects:

public class MySimpleOtherEntity
{
   public int SomeProperty1 { get; set; }
   public int SomeProperty2 { get; set; }
}

Now I have some method that takes IEnumerable of these simplified objects, and I want to return all of the MyEntity objects that have in their relations MyOtherEntities that match all of the required conditions:

public IEnumerable<MyEntity> GetMyEntitiesByMyOtherEntities(IEnumerable<MySimpleOtherEntity> entities)
{
   // example with some static values
   // we want to find all MyEntities that have MyOtherEntity with value 1,2 AND MyOtherEntity with value 2,2
   _dataContext
      .Where(x => x.OtherEntities.Any(y => y.SomeProperty1 == 1 && y.SomeProperty2 == 2)
                  &&
                  x.OtherEntities.Any(y => y.SomeProperty1 == 2 && y.SomeProperty2 == 2)
                  &&
                  .
                  . // and so on
                  .)
      .ToList();

The query above is translated correctly to SQL. I already created a solution with glueing some raw SQL parts that gives correct results, because it's just attaching AND EXISTS parts with proper subqueries.

That being said I would (if possible) rather like to have it as some dynamic LINQ Where expression. SQL parser creates pretty much as good SQL as I would do for this example, but with raw SQL queries I lose some of the control that EFCore gives me.

I created some list of predicates that I would like to chain together and inject into .Where:

public IEnumerable<MyEntity> GetMyEntitiesByMyOtherEntities(IEnumerable<MySimpleOtherEntity> entities)
{
    var predicates = new List<Expression<Func<MyEntity, bool>>>();

    foreach(var entity in entities)
    {
       predicates.Add(x => x.OtherEntities.Any(y => y.SomeProperty1 == entity.SomeProperty1 
                                                    && y.SomeProperty2 == entity.SomeProperty2);
    }

}

Unfortunately I don't know how to chain them properly. I tried to use

var combinedPredicate = predicates.Aggregate((l, r) => Expression.AndAlso(l, r));

But it has some casting issues (probably related to AndAlso returning BinaryExpression?) that won't allow me to do it in such simple way.

How can I achieve that so it's not overly complicated?

Answer 1

Since it's a "And" that should be applied between each condition why you do not use "Where" multiple time ?

var predicates = ...
var myElements = ...
foreach(var predicate in predicate) 
{
   myElements = myElements.Where(predicate);
}

The aggregatation you tried to do with expression could work but will be a bit more complicated.

EDIT here is how you can do it by aggregating expressions :

        var param = predicates.First().Parameters.First();
        var body = predicates.Select(s => s.Body).Aggregate(Expression.AndAlso);
        var lambda = (Expression<Func<Temp, bool>>)Expression.Lambda(body, param);

So the first part of the code is not so difficult. Let's say you have two predicates :

t => t.Value < 10;
t => t.Value > 5;

The first parameter will be kept (t, I'll explain why later). Then we extract the body of the expression so we get :

t.Value < 10;
t.Value > 5;

Then we aggregate them with an "And" :

t.Value < 10 && t.Value > 5

Then we create a lambda again :

t => t.Value < 10 && t.Value > 5

So everything seems fine but if you try to compile it you will get an error. Why? Everything seems OK visually. It's because the "t" at the beginning and the "t" in the second condition are not the same... They have the same name but they come from different expressions (so different objects were created and the name is not enough to same they are the same...).

In order to solve that you need to check every time the parameter is used to replace it by the same value

You need to implement a "visitor" (from the Visitor pattern) that will inspect the whole expression to replace usage of the parameter :

public static class ExpressionHelper
{

    public static Expression<Func<T, bool>> ReplaceParameters<T>(this Expression<Func<T, bool>> expression, ParameterExpression param)
    {
        return (Expression<Func<T, bool>>)new ReplaceVisitor<T>(param).Modify(expression);
    }

    private class ReplaceVisitor<T> : ExpressionVisitor
    {
        private readonly ParameterExpression _param;

        public ReplaceVisitor(ParameterExpression param)
        {
            _param = param;
        }

        public Expression Modify(Expression expression)
        {
            return Visit(expression);
        }

        protected override Expression VisitParameter(ParameterExpression node)
        {
            return node.Type == typeof(T) ? _param : node;
        }
    }
}

This implementation is naive and have surely a lot of flaws but in basic cases like this it will be enough I think.

Then you can use it by adding this line to the first block of code :

lambda = lambda.ReplaceParameters(param);

And you can now use it with EF... Or even for an in memory object:

var result = lambda.Compile()(new Temp() {Value = 5});