PredicateBuilder nests in OR sentences, causing too deep depths for large predicates

Question

Summary: I use PredicateBuilder to Or() multiple expressions together, and then sending this combined expression to OrmLite Select() . However, the generated SQL has a WHERE with so many parentheses that SQL Server is causing the error. What can I do to get around this?

Details: I have a Foo table with two columns, Bar and Baz . If I have a set of Bar / Baz values ​​and I want to find all the matching rows, then I could (for example) issue the following SQL:

 SELECT * FROM Foo WHERE (Bar=1 AND Baz=1) OR (Bar=2 AND Baz=3) OR ... 

Since I use OrmLite , I use PredicateBuilder to create a where clause for me:

 var predicate = PredicateBuilder.False<Foo>(); foreach (var nextFoo in fooList) predicate = predicate.Or(foo => nextFoo.Bar == foo.Bar && nextFoo.Baz == foo.Baz); Db.Select(predicate); 

If I do this with 3 Foos on my list , the generated SQL looks like this (cleared for brevity, but intentionally stays on one line to make a point):

 SELECT Bar, Baz FROM Foo WHERE ((((1=0) OR ((1=Bar) AND (1=Baz))) OR ((2=Bar) AND (3=Baz))) OR ((2=Bar) AND (7=Baz))) 

Pay attention to the main brackets? PredicateBuilder constantly brackets an existing expression before adding the next, so x → (x) or y → ((x) or y) or z , etc.

My problem: When I have dozens or hundreds of items to search, the generated SQL has dozens or hundreds of nested parentheses, and SQL Server returns it using SqlException :

Some part of your SQL statement is nested too deep. Rewrite the query or split it into smaller queries.

So what can I do about this? I need the SQL WHERE generated to be flattened (like the example of my example above) if I want to avoid the nesting exception. I know that I can generate my own SQL dynamically and send it to the OrmLite SqlList method, but I have to do this to defeat half the value of OrmLite.

Answer 1

Since SQL does not have OR s short circuits, you can transform an expression tree that looks like this:

 OR \ OR \ OR \ OR 

into an expression tree that looks like this:

  OR / \ / \ / \ OR OR / \ / \ OR OR OR OR 

This is only work: ideally, the structure should be able to cope with such situations.

One way to build such a tree is to recursively split the list in half, construct an " OR -tree" from each half recursively, and then combine the two " OR -tree" with another OR :

 Predicate ToOrTree(List<Foo> fooList) { if (fooList.Count > 2) { var firstHalf = fooList.Count / 2; var lhs = ToOrTree(fooList.Take(firstHalf).ToList()); var rhs = ToOrTree(fooList.Skip(firstHalf).ToList()); return lhs.Or(rhs); } Predicate res = PredicateBuilder.Create<Foo>( foo => fooList[0].Bar == foo.Bar && fooList[0].Baz == foo.Baz ); if (fooList.Count == 2) { res = res.Or( foo => fooList[1].Bar == foo.Bar && fooList[1].Baz == foo.Baz ); } return res; }