C# Linq: Combine multiple .Where() with an OR clause

Question

I have been searching a lot about my current problem but I could not find a real answer to solve that issue.

I am trying to build a LINQ Query that produces the following SQL:

SELECT * FROM TABLE WHERE (Field1 = X, Field2 = Y ... ) or (Field3 = Z)

In a normal situation I would just do this:

Object.Where(c => (c.Field1 == X && c.Field2 == Y) || (c.Field3 == Z))

I cannot use this approach because the query is build by using multiple .Where() calls.

Having an example:

// This is a short example, the real world situation has 20 fields to check and they are all connected with an AND.
if (model.Field1.HasValue) 
{
    Query = Query.Where(c => c.Field1 == X)
}

if (model.Field2.HasValue) 
{
    Query = Query.Where(c => c.Field2 == X)
}

[...] like 20 more of these .Where() calls.

and that is how it gets complicated for me. All these .Where() calls are building a Linq Query that is connected with AND, which is fine.

How do I let them execute with Parenthese and add a simple OR now using the API?

Is there a way to save the predicate in some variables so I can make something like:

Query = Query.Where(c => previousPredicates || c.Field3 == X)

or how to solve that problem?

I think there must be a good solution for that particual problem and I am not the only one who needs it, but I am absolute unsure how to achieve it.

P.S: I can't really remove the multiple .Where() calls and writing direct SQL is neither an option.

EDIT StackOverflow wants me to say why my question is different from others. Well, the thing is about Parentheses. I do not want to connect all .Where() with a single OR clause, I want to leave them with AND and add another OR clause while all the AND queries are being parenthesied.

Possible duplicate of [How can I build Linq query with dynamic OR statements?](https://stackoverflow.com/questions/7180305/how-can-i-build-linq-query-with-dynamic-or-statements) — GSerg, May 18 '19 at 08:35
No it is not a duplicate. While the other link is helpful it does not really explain how to achieve Parentheses, since it only explains how to replace all ` AND ` connections with ` OR `. — thelostcode, May 18 '19 at 08:44
Yes, it is a duplicate. In the [Jon Skeet's code](https://stackoverflow.com/a/7180331/11683), the `someOtherCondition` is the expression that may contain several `AND`s. These `AND` blocks are then gradually `OR`ed together. — GSerg, May 18 '19 at 08:47
The other answer tells you how to combine predicates. Just replace the `||` with `&&`. Then you can do exactly what you want `Query = Query.Where(c => previousPredicates || c.Field3 == X)` — adjan, May 18 '19 at 08:52
Assuming the Fields are nullable, the example can be shortened to : `Query = Query.Where(c => c.Field1 ?? X == X && c.Field2 ?? X == X)`. If it is entity framework query, add tag because some cases are not applicable — Slai, May 18 '19 at 08:52
@Adrian The other answers shows how to combine predicates with `OR` specifically. Combining them with `AND` is much easier and is shown in this very question. — GSerg, May 18 '19 at 08:54
@Slai I don't see how that would help when building the predicate gradually with several calls to `where`. I also don't think it translates to SQL. — GSerg, May 18 '19 at 08:56
@GSerg Combining `Where` does not result in a predicate, but an `IEnumerable` which you cant combine with another predicate... — adjan, May 18 '19 at 08:57
@Adrian Combining several `where` here results in an `IQueryable` that has correct complete predicate *in it* which will be then translated to SQL. It is [no different](https://stackoverflow.com/q/4098343/11683) to building that predicate separately. — GSerg, May 18 '19 at 09:13
Try searching for "predicatebuilder", e.g. [LINQKit PredicateBuilder](http://www.albahari.com/nutshell/linqkit.aspx), [Universal PredicateBuilder](https://petemontgomery.wordpress.com/2011/02/10/a-universal-predicatebuilder/) etc. — Ivan Stoev, May 18 '19 at 11:26

score 18 · Accepted Answer · answered May 18 '19 at 14:12

If you want to build your query programmatically and have it execute on your SQL server instead of fetching all records and querying in memory, you need to use the set of static methods on the Expression class and build your query using those. In your example:

public class Query // this will contain your 20 fields you want to check against
{
    public int? Field1; public int? Field2; public int? Field3; public int Field4;
}

public class QueriedObject // this is the object representing the database table you're querying
{
    public int QueriedField;
}

public class Program
{
    public static void Main()
    {
        var queryable = new List<QueriedObject>().AsQueryable();
        var query = new Query { Field2 = 1, Field3 = 4, Field4 = 2 };

        // this represents the argument to your lambda expression
        var parameter = Expression.Parameter(typeof(QueriedObject), "qo");

        // this is the "qo.QueriedField" part of the resulting expression - we'll use it several times later
        var memberAccess = Expression.Field(parameter, "QueriedField");

        // start with a 1 == 1 comparison for easier building - 
        // you can just add further &&s to it without checking if it's the first in the chain
        var expr = Expression.Equal(Expression.Constant(1), Expression.Constant(1));

        // doesn't trigger, so you still have 1 == 1
        if (query.Field1.HasValue)
        {
            expr = Expression.AndAlso(expr, Expression.Equal(memberAccess, Expression.Constant(query.Field1.Value)));
        }
        // 1 == 1 && qo.QueriedField == 1
        if (query.Field2.HasValue)
        {
            expr = Expression.AndAlso(expr, Expression.Equal(memberAccess, Expression.Constant(query.Field2.Value)));
        }
        // 1 == 1 && qo.QueriedField == 1 && qo.QueriedField == 4
        if (query.Field3.HasValue)
        {
            expr = Expression.AndAlso(expr, Expression.Equal(memberAccess, Expression.Constant(query.Field3.Value)));
        }

        // (1 == 1 && qo.QueriedField == 1 && qo.QueriedField == 4) || qo.QueriedField == 2
        expr = Expression.OrElse(expr, Expression.Equal(memberAccess, Expression.Constant(query.Field4)));

        // now, we combine the lambda body with the parameter to create a lambda expression, which can be cast to Expression<Func<X, bool>>
        var lambda = (Expression<Func<QueriedObject, bool>>) Expression.Lambda(expr, parameter);

        // you can now do this, and the Where will be translated to an SQL query just as if you've written the expression manually
        var result = queryable.Where(lambda);       
    }
}

Yep, that is the only answer out of presented so far that will work, even though it is pretty inconvenient to use. — GSerg, May 18 '19 at 14:32

adjan · Answer 2 · 2019-05-18T14:35:07.250

10

First, create some helper extension methods to easier combine two Func<T,bool> predicates:

 public static Func<T, bool> And<T>(this Func<T, bool> left, Func<T, bool> right) 
     => a => left(a) && right(a);

 public static Func<T, bool> Or<T>(this Func<T, bool> left, Func<T, bool> right)
     => a => left(a) || right(a);

Then you can use them to chain predicates:

var list = Enumerable.Range(1, 100);

Func<int, bool> predicate = v => true; // start with true since we chain ANDs first

predicate = predicate.And(v => v % 2 == 0); // numbers dividable by 2
predicate = predicate.And(v => v % 3 == 0); // numbers dividable by 3
predicate = predicate.Or(v => v % 31 == 0); // numbers dividable by 31

var result = list.Where(predicate);

foreach (var i in result)
    Console.WriteLine(i);

Output:

edited May 18 '19 at 14:35

answered May 18 '19 at 09:17

adjan

13,371
2
31
48

7

`IQueryable.Where` accepts `Expression>`, that is the only thing that can be translated to SQL. `IEnumerable.Where` accepts `Func<>`, so passing a `Func<>` predicate to a EF / Linq2Sql query will result in the entire table fetched on the client and filtered with the predicate. The OP specifically wanted something that translates to SQL. – GSerg May 18 '19 at 09:27
It works with `IQueryable`as well. see my edit. @GSerg – adjan May 18 '19 at 09:40
2

See [LINQKIT](https://github.com/scottksmith95/LINQKit) for a generic way of generatig predicates - among other things – Peter Smith May 18 '19 at 09:51
1

This of course *doesn't* work with `IQueryable<>`. Take a look carefully of the **type** of the `var result` in your "working" example - it's `IEnumerable<>`, not `IQueryable<>`. Also even you make the final predicate expression, using delegates inside makes it non translatable. – Ivan Stoev May 18 '19 at 11:31
@IvanStoev The type is `IQueryable`, yes. Great comment! See running example here: https://onlinegdb.com/SkEJeOa24 – adjan May 18 '19 at 11:40
3

`IQueryable result = list.AsQueryable().Where(predicate);` is a compile error, "Cannot implicitly convert type '`System.Collections.Generic.IEnumerable`' to '`System.Linq.IQueryable`'. An explicit conversion exists (are you missing a cast?)". Which is correct. The code you have on GDB is different, `.Where(v => predicate(v))`. That does compile (because it is technically an `Expression`, built for you by the compiler because of the `=>`), but it will not work in a data context, giving "Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL." – GSerg May 18 '19 at 12:14
@GSerg If that doesnt work, why did you point to John Skeets answer as a duplicate? That would not work either. – adjan May 18 '19 at 12:23
1

@Adrian Correct, and Jon Skeet [has confirmed](https://stackoverflow.com/questions/7180305/how-can-i-build-linq-query-with-dynamic-or-statements#comment99019065_7180331) that it would not work. I pointed to it before I saw he was using `Func<>` instead of `Expression>`. – GSerg May 18 '19 at 12:24

alireza yazdandoost · Answer 3 · 2019-05-18T13:04:29.050

3

You can use Expression to create in one step like this:

Expression<Func<Model, bool>> exp = (model => 
                                    ((model.Field1.HasValue && c.Field1 == X) &&
                                    (model.Field2.HasValue && c.Field2 == X)) ||
                                     model.Field3 == X
                                    )

Once you have your predicates defined, it's very easy to use them in a query.

var result = Query.AsQueryable().Where(exp)

Check the code in this gist: my gist url

UPDATE 1: If You have to use steps to create your expression you can use this:

Expression<Func<Model, bool>> exp = c => true;
if (model.Field1.HasValue) 
{
    var prefix = exp.Compile();
    exp = c => prefix(c) && c.Field1 == X;
}

if (model.Field2.HasValue) 
{
    var prefix = exp.Compile();
    exp = c => prefix(c) && c.Field2 == X;
}

[...] like 20 more of these .Where() calls.

edited May 18 '19 at 13:04

answered May 18 '19 at 09:27

alireza yazdandoost

634
7
11

You cannot build the predicate gradually with this approach. – GSerg May 18 '19 at 09:28
@GSerg I will post the gist here – alireza yazdandoost May 18 '19 at 09:48
@GSerg here is my gist: https://gist.github.com/azzerilla/c31172c8a2972cc5db6e273d881a677f – alireza yazdandoost May 18 '19 at 09:51
@GSerg Is It True? – alireza yazdandoost May 18 '19 at 11:09
I guess we'll never know – Denny May 18 '19 at 12:41
I don't know, I'm not very familiar with this – Denny May 18 '19 at 12:50
@Denny I Tested It, it works, But I don't know why I get 'not useful' feedback – alireza yazdandoost May 18 '19 at 12:52
Like I said in the [very first comment](https://stackoverflow.com/questions/56197261/c-sharp-linq-combine-multiple-where-with-an-or-clause#comment99018844_56197655), there is only one predicate built in one go. The problem is that it has to be built in steps, like it is shown in the question. – GSerg May 18 '19 at 12:53
@GSerg ok, I Updated my answer, Please check that :) – alireza yazdandoost May 18 '19 at 13:05
"*Method `System.Object DynamicInvoke(System.Object[])` has no supported translation to SQL.*" – GSerg May 18 '19 at 13:36

score 0 · Answer 4 · edited May 19 '19 at 18:14

Ok you have had your own share of answer about linq.

Let me introduce a different approach using Dynamic.linq

// You could build a Where string that can be converted to linq.
// and do if sats and append your where sats string. as the example below
var query = "c => (c.Field1 == \" a \" && c.Field2 == Y) || (c.Field3 == \" b \")";
var indicator = query.Split('.').First(); // the indicator eg c
   // assume TABLE is the name of the class
var p = Expression.Parameter(typeof(TABLE), indicator);
var e = DynamicExpression.ParseLambda(new[] { p }, null, query);

// and simple execute the expression 
var items = Object.Where(e);

C# Linq: Combine multiple .Where() with an OR clause

4 Answers4

Linked

Related

C# Linq: Combine multiple .Where() with an *OR* clause

4 Answers4

Linked

Related

C# Linq: Combine multiple .Where() with an OR clause