How to chain OR clauses, with LINQ?

Question

I've got a scenario where I need to build a SQL query, using LINQ, that includes a variable amount of OR clauses. I'm writing a function that will build the query based on some input. The function definition looks something similar to ...

function BuildQuery(ICollection<ColumnsThatNeedToBeTrue> columns)
{
  ...
}

So, I'm given a collection of columns that I need to check for true, and the check needs to use OR clauses.

If the columns array contains A and B, I'd need the query to check if column A is true OR column B is true.

If the columns array contains A, B and C, I'd need to query and check if A OR B OR C are true.

I don't know how to do this all within a single Where, because I don't know of a way to progressively tack on additional || clauses. I'm not sure how I'd include additional OR checks in the below, based on the input array.

var query = entities.Where(m => m.A == true || m.B == true ...)

I cannot chain Where functions, each for their own column check, because it builds that query using AND clauses and I need OR.

Is there a way to build out a query like this, using LINQ?

`In` clause? http://stackoverflow.com/questions/959752/where-in-clause-in-linq — George Stocker, Mar 23 '16 at 18:35
I don't think it'll work because in the SQL these things are all individual columns. On my EF entity class, these columns are represented as `bool` properties for each column. As an input to my function I'm given an array of `enum` that says which column to check for true. So there's no real array to compare - just individual bool columns/properties. — Ryan, Mar 23 '16 at 18:37
Duplicate of [this question](http://stackoverflow.com/questions/2101540/linq-or-equivalent-of-where) ? — Matthew, Mar 23 '16 at 18:56
Is Linq part of the problem or part of the solution for this...... — Ian Ringrose, Mar 23 '16 at 19:09
Possible duplicate of ["Or" equivalent in Linq Where() lambda expression](https://stackoverflow.com/questions/2101540/or-equivalent-in-linq-where-lambda-expression) — GSerg, Apr 06 '20 at 14:30

Magnus · Accepted Answer · 2016-03-23T19:07:15.867

9

You can use the PredicateBuilder to chain or conditions.

var predicate = PredicateBuilder.False<SomeEntity>();

predicate = predicate.Or (p => p.A == true);
if(something)
   predicate = predicate.Or (p => p.B == true);

var query = entities.AsExpandable().Where (predicate); //AsExpandable() for EF

edited Mar 23 '16 at 19:07

answered Mar 23 '16 at 18:46

Magnus

45,362
8
80
118

Is this C# 5+ only? I'm still on 4.5, unfortunately. – Ryan Mar 23 '16 at 18:48
@Ryan No you just add the PredicateBuilder class yourself. It is in the page I linked under "PredicateBuilder Source Code". – Magnus Mar 23 '16 at 18:49
@Magnus The problem though is that he'll need the whole LinqKit because he seems to be targeting EF. – Ivan Stoev Mar 23 '16 at 18:51
There is a nuget package for LinqKit here: https://www.nuget.org/packages/LinqKit/ which includes the `PredicateBuilder` – Magnus Mar 23 '16 at 18:52
@Ryan Use LinqKit and for Entity framework change the last line to: `var query = entities.AsExpandable().Where (predicate);` – Magnus Mar 23 '16 at 18:55
Seems to be compatible with .NET 4.5. It's generating SQL, but the SQL does nothing. By that I mean, here's what's being generated https://gist.github.com/ryancole/87ee8b1f7b6d488e487b and here is the LINQ code https://gist.github.com/ryancole/39f2386aaad8f24283c3 – Ryan Mar 23 '16 at 19:04
@Ryan Check your conditions. EF generates such query when the `Where` condition evaluates **constantly** to `false` – Ivan Stoev Mar 23 '16 at 19:09
Ah that does it. I guess I needed to use the `True` function. Didn't realize what those functions were doing. Thanks guys, this builds the right query! – Ryan Mar 23 '16 at 19:14
Great it worked @Ryan. Yes the `PredicateBuilder.False/True` is the starting point of the condition. – Magnus Mar 23 '16 at 19:16

score 0 · Answer 2 · answered Mar 02 '22 at 20:42

I'm currently using Linq2DB, and needed to get many records in a single query, filtered like this:

WHERE [...some other filters...] AND (
    (TipoConfiguracionId = $1 AND ConfiguracionId = $2) OR
    (TipoConfiguracionId = $3 AND ConfiguracionId = $4) OR
    (TipoConfiguracionId = $5 AND ConfiguracionId = $6))

This query should be reading a Configuration table that is not so big, so ended up building my own Expression. This function takes a list of records to be matched:

public async static Task<List<Data.Configuraciones>> GetMany(DataConnection db, List<Data.Configuraciones> list)
{
    // Main query
    var q = from p in db.Configuraciones
        where [...some other normal filters...]
        select p;
    
    Expression completeCondition = null;
    Expression itemCondition = null;
    
    var entityParam = Expression.Parameter(typeof(Data.Configuraciones), "e");
    var propTipoConfiguracionId = Expression.PropertyOrField(entityParam, "TipoConfiguracionId");
    var propConfiguracionId = Expression.PropertyOrField(entityParam, "ConfiguracionId");

    // Iterates the list
    foreach (var config in list)
    {
        // Assembles the inner condition: (TipoConfiguracionId = $1 AND ConfiguracionId = $2)
        itemCondition = Expression.Equal(propTipoConfiguracionId, Expression.Constant(config.TipoConfiguracionId));
        itemCondition = Expression.And(itemCondition, Expression.Equal(propConfiguracionId, Expression.Constant(config.ConfiguracionId)));

        // Adds the inner condition to the complete condition, concatenating it with an OR operator
        if (completeCondition == null)
            completeCondition = itemCondition;
        else
            completeCondition = Expression.Or(completeCondition, itemCondition);
    }

    // Appends the complete condition to the main query
    var lambda = Expression.Lambda<Func<Data.Configuraciones, bool>>(completeCondition, entityParam);
    q = q.Where(lambda);

    return await q.ToListAsync();
}

How to chain OR clauses, with LINQ?

2 Answers2

Linked

Related