Append Ef Core IQueryable< T> as Or Query

Question

I have main EF core query that append some AND search Criteria to it via IQueryable.Where(). No issue.

And have some OR search Criteria apply to it, via Expression<Func<T, bool>> orFilter. And it's concat with Or via: orFilter = orFilter.Or(p=>p.Prop == foo); And finally it will apply to main query via mainQuery.Where(orFilter) as AND search criteria; No issue.

But one of the OR search Criteria is for another table, need to join on other table and it returns IQueryable< T>. I want to apply it to the orFilter via orFilter = orFilter.Or(joinQuery.Expression as Expression<Func<Product, bool>>); but it didn't work.

Or is there any equivalent way to implement it?

IQueryable<Product> records = DBContext.Product
        .Include(p => p.Item1)
        ...;


if (filterANDCriteria1.HasValue)
{
   records= records.Where(p.xxx == filterANDCriteria1);
}

Expression<Func<Product, bool>> orFilter= null;
if(filterOrCriteriaP1.HasValue)
{
 orFilter= orFilter.Or(p=>p.yyy==filterOrCriteriaP1 );
}

if(filterOrCriteriaP2.HasValue)
{
//join another table e.g. Category pseudo code 
  IQueryable<Product> joinQuery = 
records.Join(Category.EntityId == Product.Id and Category.EntityType == EntityType.Product and Category.FieldFoo == filterOrCriteriaP2), 
(product,category)=> product;

  //Want to append the query to orFilter here but it not work and return null
  orFilter = orFilter.Or(joinQuery.Expression as Expression<Func<Product, bool>>); //return null

//It only works for AND query, following code will return the products that match join result and other filter (like filterANDCriteria1)
// records = joinQuery;
}
if(orFilter!=null)
{
   //Apply whole orFilter as AND filter to main query
   records = records.Where(orFilter);
}
return records.ToList();

Answer 1

Here is this code may help you.

public static class PredicateBuilder
{
    public static IQueryable<T> WhereAny<T>(
        this IQueryable<T> source,
        params Expression<Func<T, bool>>[] predicates)
    {
        if (source == null) throw new ArgumentNullException("source");
        var exp = GetPredicates(predicates);
        return source.Where(exp);
    }
    private static Expression<Func<T, bool>> GetPredicates<T>(Expression<Func<T, bool>>[] predicates)
    {
        if (predicates == null) throw new ArgumentNullException("predicates");
        if (predicates.Length == 0) return x => false;
        if (predicates.Length == 1) return predicates[0];

        var exp = predicates[0];
        for (var i = 1; i < predicates.Length; i++)
        {
            exp = exp.OrElse(predicates[i]);
        }
        return exp;
    }
    public static Expression<Func<T, bool>> OrElse<TA, T>(this IEnumerable<TA> args, Func<TA, Expression<Func<T, bool>>> expression)
    {
        return args.Select(expression).ToList().OrElse();
    }
    public static Expression<Func<T, bool>> OrElse<T>(this List<Expression<Func<T, bool>>> expressions)
    {
        if (expressions.Count <= 0) return null;
        var expression = expressions[0];
        for (var i = 1; i < expressions.Count; i++)
        {
            expression = expression.OrElse(expressions[i]);
        }
        return expression;
    }
    public static Expression<Func<T, bool>> OrElse<T>(
        this Expression<Func<T, bool>> expr1,
        Expression<Func<T, bool>> expr2)
    {
        var parameter = Expression.Parameter(typeof(T), expr1.Parameters[0].Name);
        var leftVisitor = new ReplaceExpressionVisitor(expr1.Parameters[0], parameter);
        var left = leftVisitor.Visit(expr1.Body);
        var rightVisitor = new ReplaceExpressionVisitor(expr2.Parameters[0], parameter);
        var right = rightVisitor.Visit(expr2.Body);
        return Expression.Lambda<Func<T, bool>>(
            Expression.OrElse(left, right), parameter);
    }
    public static Expression<Func<T, bool>> AndAlso<T>(
       this Expression<Func<T, bool>> expr1,
       Expression<Func<T, bool>> expr2)
    {
        var parameter = Expression.Parameter(typeof(T), expr1.Parameters[0].Name);
        var leftVisitor = new ReplaceExpressionVisitor(expr1.Parameters[0], parameter);
        var left = leftVisitor.Visit(expr1.Body);
        var rightVisitor = new ReplaceExpressionVisitor(expr2.Parameters[0], parameter);
        var right = rightVisitor.Visit(expr2.Body);
        return Expression.Lambda<Func<T, bool>>(
            Expression.AndAlso(left, right), parameter);
    }
    private class ReplaceExpressionVisitor : ExpressionVisitor
    {
        private readonly Expression _oldValue;
        private readonly Expression _newValue;

        public ReplaceExpressionVisitor(Expression oldValue, Expression newValue)
        {
            _oldValue = oldValue;
            _newValue = newValue;
        }

        public override Expression Visit(Expression node)
        {
            if (node == _oldValue)
                return _newValue;
            return base.Visit(node);
        }
    }
}

Demo of use:

var users = ctx.Users.WhereAny(ids.Select(id => (Expression<Func<T, bool>>)(g => g.Id == id)).ToArray()).ToList();

And you can have new extensions method,then it used easy more.

    public static IQueryable<User> WhereAnyId(
        this IQueryable<User> source,
        params Guid[] ids)
    {
        if (source == null) throw new ArgumentNullException(nameof(source));
        return source.WhereAny(ids.Select(id => (Expression<Func<User, bool>>)(g => g.Id == id)).ToArray());
    }

    var users = ctx.Users.WhereAnyId(ids).ToList();

Answer 2

Finally I got a work around. As it's one to one mapping, I added a new column into Product table e.g. named CategoryId. When create product category fill in the CategoryId back to Product table.

And include the property which type is Category and add data annotation [ForeignKey] to point to CategoryId. So it's could be use .Include(p=>p.Category) and use orFilter = orFilter.Or(p=>p.Category.xxx == filterOrCriteriaP2.Value);

For other entity e.g. Order, it won't impacted.