Dynamic OR in LINQ without the PredicateBuilder

Question

I am building a method that takes one or more criteria for querying a database with LINQ. I made this:

public ICollection<MyClass> FindAllBy(params Expression<Func<MyClass, bool>>[] criteria)
    {
        using (var ctx = new MyContext())
        {
            IQueryable<MyClass> result = ctx.MyClasses.AsNoTracking();

            if (criteria != null && criteria.Length > 0)
            {
                foreach (var item in criteria)
                {
                    result = result.Where(item);
                }
            }

            return result.ToList();
        }
    }

This has the effect that if I look for a object with Id 1 and one with Id 2 I get nothing, as no row has both an Id of 1 and 2. So I need an OR clause. I found this page:

http://www.albahari.com/nutshell/predicatebuilder.aspx

Which has a PredicateBuilder class, which I used to make this:

    public ICollection<PhotoFile> FindAllBy(params Expression<Func<PhotoFile, bool>>[] criteria)
    {
        using (var ctx = new CotanContext())
        {
            var predicate = PredicateBuilder.False<PhotoFile>();

            if (criteria != null && criteria.Length > 0)
            {
                foreach (var item in criteria)
                {
                    predicate = predicate.Or(item);
                }
            }

            return ctx.PhotoFiles.Where(predicate).ToList();
        }
    }

My code differs slightly from the page in that I pass in an expression into the method, which I then pass into the Predicate.Or method.

The above method gives a The LINQ expression node type 'Invoke' is not supported in LINQ to Entities. error. Which makes sense, as Entity Framework does not know how to translate this code into a valid query.

The solution on the linked site is to download their Nuget package or source code, which makes this code work. However, I don't really feel comfortable putting in several hundreds of lines of unknown and seemingly untested code for a single function, that in my opinion should have been built into LINQ ages ago by Microsoft. And the lead developer on my project has also in the past strongly advised against using unknown packages that aren't directly from Microsoft. I am working with sensitive information, so I would rather be safe than sorry.

So, my question: is there any way to get an OR function in LINQ without having to use an external Nuget package?

Do you really need to pass the clauses into your function? Why not return an IQueryable<> and let the caller work out what to do? — Neil, Jun 29 '16 at 08:03
In the first method at least, that's what I'm doing. I only turn the IQueryable into an IEnumerable when I call ToList(). I use the Where() overload that accepts Expressions and returns another IQueryable. And the Or method in the second example only returns another Expression, so no database calls there either. — yesman, Jun 29 '16 at 08:07
Use [this predicate builder](https://petemontgomery.wordpress.com/2011/02/10/a-universal-predicatebuilder/) instead — Ivan Stoev, Jun 29 '16 at 08:23
Thanks Ivan, that is simple enough for me to write my own tests for it. It's also short enough that it can be reviewed by others as well. — yesman, Jun 29 '16 at 08:29
@ohyeah No, what I mean is, return IQueryable from your repository and allow the service to apply its own filters. What you are basically doing here is preventing the fluent API from doing it's work. — Neil, Jun 29 '16 at 08:51

score 4 · Accepted Answer · edited May 23 '17 at 12:16

As I mentioned in the comments, you can use the Universal PredicateBulder or the class from my answer to Establish a link between two lists in linq to entities where clause.

However you can greatly simplify the methods like the one from your example by using this simple extension method:

public static class QueryableExtensions
{
    public static IQueryable<T> WhereAny<T>(this IQueryable<T> source, IEnumerable<Expression<Func<T, bool>>> predicates)
    {
        if (predicates == null || !predicates.Any()) return source;
        var predicate = predicates.Aggregate((a, b) => Expression.Lambda<Func<T, bool>>(
            Expression.OrElse(a.Body, b.Body.ReplaceParameter(b.Parameters[0], a.Parameters[0])),
            a.Parameters[0]));
        return source.Where(predicate);
    }
}

which in turn uses this helper:

public static class ExpressionUtils
{
    public static Expression ReplaceParameter(this Expression expression, ParameterExpression source, Expression target)
    {
        return new ParameterReplacer { Source = source, Target = target }.Visit(expression);
    }

    class ParameterReplacer : ExpressionVisitor
    {
        public ParameterExpression Source;
        public Expression Target;
        protected override Expression VisitParameter(ParameterExpression node)
        {
            return node == Source ? Target : base.VisitParameter(node);
        }
    }
}

Now the sample method could be simple as that:

public ICollection<PhotoFile> FindAllBy(params Expression<Func<PhotoFile, bool>>[] criteria)
{
    using (var ctx = new CotanContext())
        return ctx.PhotoFiles.WhereAny(criteria).ToList();
}

score 0 · Answer 2 · answered Jun 29 '16 at 08:24

0

Short answer: yes. If you look at the PredicateBuilder code, it uses Expressions to construct your query. So you could try to recreate that behavior, but as it has been done before, why not try to fix your code?

I use PredicateBuilder a lot in my repository classes and I remember having the same issue. I resolved the issue by adding AsExpandable() (in the LinqKit namespace) in every call to Entity Framework:

return ctx.PhotoFiles.AsExpandable().Where(predicate).ToList();

answered Jun 29 '16 at 08:24

hbulens

1,872
3
24
45

That was my first idea as well (great minds think alike :-)). However, the AsExpandable() extension also has several other dependencies, which in turn have dependencies, which forced me to copy a lot of the source code if I really wanted to use it. – yesman Jun 29 '16 at 08:30
What are the dependencies that you worry about? Based on the LinqKit Nuget package, the dependencies are fairly common assemblies. – hbulens Jun 29 '16 at 08:36

Dynamic OR in LINQ without the PredicateBuilder

2 Answers2

Linked