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.

p.campbell · Accepted Answer · 2011-05-05 22:32:50Z

2

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.

answered May 5, 2011 at 22:32

p.campbell

101k70 gold badges264 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Scott F Over a year ago

I needed to download LINQKit because I needed AsExpandable() because I'm using LINQ to Entities but it worked like a charm.

Mantra Over a year ago

PredicateBuilder saved my day. Thanks @p.campbell for this straightforward solution. Actually this is why Stackoverflow is so famous among developer community :).

Dustin Laine · Accepted Answer · 2011-05-05 22:27:11Z

1

You can make a long conditional statement:

var foo = from f in db.Foos.Where((f => f.A == val1 && f.b == val2 && f.c == val3) ||
                                  (f => f.A == val4 && f.b == val5 && f.c == val6))

Or, which is preferable IMO:

var predicate = PredicateBuilder.False<Foo>();
predicate = predicate.Or(f => f.A == val1 && f.A == val2 && f.A == val3);
predicate = predicate.Or(f => f.A == val4 && f.A == val5 && f.A == val6);
var foo = db.Foos.Where(predicate);

These can be conditional also:

var predicate = PredicateBuilder.False<Foo>();
predicate = predicate.Or(f => f.A == val1 && f.A == val2 && f.A == val3);
if (condition)
    predicate = predicate.Or(f => f.A == val4 && f.A == val5 && f.A == val6);
var foo = db.Foos.Where(predicate);

edited May 5, 2011 at 22:27

answered May 5, 2011 at 22:20

Dustin Laine

38.7k10 gold badges90 silver badges125 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related