How to create SQL Nested ANDs inside of multiple ORs using LINQ

Question

I'm trying to create the equivelant LINQ query from the below SQL example:

SELECT *
FROM FOO
WHERE 
    ((a == <val1>) AND (b == <val2>) AND (c == <val3>))
OR
    ((a == <val4>) AND (b == <val5>) AND (c == <val6>))

There will always be an a, b, and c that will be AND'd together surrounded by an OR. This pattern can occur n amount of times.

The only solution I have found that work is using LINQ Union but the SQL generated isn't what I would like.

Answer 1

Try implementing the PredicateBuilder class.

Then perhaps you can use something like this:

var pred = PredicateBuilder.False<Foo>();

foreach(var criteria in myCriteriaSet)
{            
    pred = pred.Or(x => x.ID== criteria.ID && 
                        x.Name== criteria.Name &&
                        x.Created == criteria.SomeDate);
}

var matching = db.Foos.Where(pred);

This then assumes your criteria is enumerable. Modify as you need to accommodate your needs.