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.

Answer 1

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);            } } 

Answer 2

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:

6 12 18 24 30 31 36 42 48 54 60 62 66 72 78 84 90 93 96