1

Resolved

Linq2Caml generation fails with multiple boolean operators

description

Filters with more than one && or || fails because the caml query is generated wrongly. With four or more boolean operators it fails extra hard and an exception is thrown when GetListItems(query) is called in SPGenRepositoryManager::FindDataItems. The problem seems to be the placement of the expressions inside nested <And/> or <Or/> elements.
 
Steps to reproduce
Any long query on an entity, like
 
var neverReturns = SPGENEntityManager<MyEntity>.Instance.GetEntities(MyList, e=>e.Field1 == 1 && (e.Field2 == 2 || e.Field2 == 3) && e.Field3 == 3 && e.Field4 < 4).ToArray()
 
I tried modifying the current logic in AddComparisonOperandNode and AddBooleanOperandNode without success. I honestly don't understand how it works. It is either very brilliant or just plain wrong.
 
Instead I wrote a fix that uses a stack that represents a path to the most current boolean operator (and its boolean operator parents). The solution is included as an attachment. It works on my machine(tm), but I have not tested it under all circumstances (things like having a not-operator in front of one or more boolean ops is not tested).
 
Another small issue:
negation of boolean fields, like (entity=>!entity.IsHidden) doesn't seem to work. But there is any easy workaround, where one can write (entity => entity.IsHidden == false) instead.

file attachments

comments

tore7506 wrote Dec 21, 2011 at 8:52 AM

There is a new version on its way. I'll have a look into this problem and see if it can be fixed. I agree that the logic behind is hard to understand and could need some refactoring..therefore I appriciate your contribution!

/Tony

tore7506 wrote Jan 12, 2012 at 6:56 PM

I could not reproduce this in the new version 1.3.1 which was released today. Can you please test it again?

julmas wrote Jan 16, 2012 at 3:59 PM

Thanks for the new version!

But the query bug is still there I'm afraid.
the expression:
(p => p.AntalKolli == 1 || p.AntalKolli == 2 || p.AntalKolli == 3 || p.AntalKolli == 4 || p.AntalKolli == 5 || p.AntalKolli == 6 || p.AntalKolli == 7 || p.AntalKolli == 8 || p.AntalKolli == 9)

is translated to:

<Where><Or><Or><Or><Or><Or><Or><Or><Or><Eq><FieldRef Name="AntalKolli" /><Value Type="Number">1</Value></Eq><Eq><FieldRef Name="AntalKolli" /><Value Type="Number">2</Value></Eq><Eq><FieldRef Name="AntalKolli" /><Value Type="Number">4</Value></Eq><Eq><FieldRef Name="AntalKolli" /><Value Type="Number">5</Value></Eq><Eq><FieldRef Name="AntalKolli" /><Value Type="Number">7</Value></Eq><Eq><FieldRef Name="AntalKolli" /><Value Type="Number">8</Value></Eq></Or><Eq><FieldRef Name="AntalKolli" /><Value Type="Number">3</Value></Eq><Eq><FieldRef Name="AntalKolli" /><Value Type="Number">6</Value></Eq><Eq><FieldRef Name="AntalKolli" /><Value Type="Number">9</Value></Eq></Or></Or></Or></Or></Or></Or></Or></Where>

Sorry about the xml spam. Copy paste the block into an xml editor.

The CAML above was captured by tracing query.Query in SPGENLinqQueryProvider::ExecuteQuery.

And yes. I know this is not the most optimal way to check if AntalKolli is between 1 and 9 :). The long query was written to trigger the bug.

tore7506 wrote Jan 18, 2012 at 8:08 PM

I have made some minor changes in the tree visitor class. Could you test the latest version which is 1.3.3 and see if it still has issues with your CAML rendering?

julmas wrote Jan 19, 2012 at 7:55 AM

Thanks Tony!

Now (in 1.3.3) the query in my previous comment is translated correctly.

But here's another query that doesn't work in 1.3.3.

(p => (p.AntalKolli == 1 || p.AntalKolli == 2) || (p.AntalKolli == 3 || p.AntalKolli == 4 || p.AntalKolli == 5))

With expected tree structure ( 1 2 ) ( ( 3 4 ) 5) ) or similar

Is translated to
<Where>
<Or>
<Or>
<Or>
<Or>
 <Eq><FieldRef Name="AntalKolli" /><Value Type="Number">3</Value></Eq>
 <Eq><FieldRef Name="AntalKolli" /><Value Type="Number">4</Value></Eq>
</Or>
<Eq><FieldRef Name="AntalKolli" /><Value Type="Number">5</Value></Eq>
</Or>
<Eq><FieldRef Name="AntalKolli" /><Value Type="Number">1</Value></Eq>
<Eq><FieldRef Name="AntalKolli" /><Value Type="Number">2</Value></Eq>
</Or>
</Or>
</Where>

with tree structure ( ( ( 3 4 ) 5 ) 1 2 )

The first or has only 1 child, and the second has 3.

tore7506 wrote Jan 19, 2012 at 11:17 AM

Ok, happy to hear that your first query works correctly. I thought a quick fix without rewriting the whole visitor class would be to check if there are maximum of 2 child nodes inside a comparison node (Eq, Gt, Lt). I will check it out again.

julmas wrote Jan 20, 2012 at 5:17 PM

Thanks again for all the work you are putting in to this, Tony. You are really close now. If you add the while-loop you have in AddComparisonOpNode to the else clause in AddBooleanOpNode I believe the algorithm will work for all combinations of And, Or and parentheses.

Then we have the issue of a unary Not sitting between two binary ops. The user (developer) can avoid the Not operator by "pushing" all negations to the leaf nodes, using the DeMorgan laws in the linq expression. But they are unlikely to know they need to do that.

tore7506 wrote Jan 22, 2012 at 9:59 PM

I have made some changes in the visitor regardinng this issue. It looks like it handles the boolean comparisons better and negations also. Could you test the new version (v1.3.4) to see if this issue was corrected?

wrote Jan 22, 2012 at 10:03 PM

julmas wrote Jan 23, 2012 at 9:18 AM

One last bug for the And, Or and Parentheses.

AddBooleanOperandNode walks down the where nodes children following the First Child of every node, but then it adds itself as a last child (current.AppendChild). This corrupts queries with parentheses. The quick fix is to use InsertBefore here, like everywhere else.

Then there are problems with negations. Double negations don't work. These occur in queries like (e=> !(e.a ==5 && !(e.a > 0 || e.a < 9)))

The first step is is probably to toggle _isNotOperand in Visitunary(Not) instead of always setting it to true.

tore7506 wrote Jan 23, 2012 at 1:53 PM

I agree, I'll have a look on this right away. Thanks for your input!

/Tony

wrote Jan 23, 2012 at 8:06 PM

tore7506 wrote Jan 23, 2012 at 8:09 PM

I have made some new changes and uploaded a new version 1.3.5

Please test it again and leave feedback. Thanks again!

/Tony

julmas wrote Jan 25, 2012 at 2:18 PM

1.3.5. correctly translates all queries we use in our Application. And also all test queries containing Not, And Or and parentheses I used trying to provoke an error. Two thumbs up!

This issue can now be closed.

And I just now realised that there is no <Not/> - operator in CAML. That explains why the translation is done the way it is done. The developer that hacked together the CAML Query specification probably didn't think that thousands of developers would still use his language 10 years later. :)

tore7506 wrote Jan 25, 2012 at 8:11 PM

Happy to hear that it all works ok for you now.

Yes, I agree! there isn't even a way to do Not-Contains or Not-BeginsWith in CAML which makes a LINQ to CAML parser very clumpsy because you never know what it supports or not in advance. Only experience will assist you in what you can and can not do in CAML.

Thanks again for all feedback.
/Tony

wrote Jan 25, 2012 at 8:11 PM

wrote Feb 14, 2013 at 2:13 AM

wrote May 16, 2013 at 7:49 AM

wrote May 16, 2013 at 7:49 AM

wrote Jun 14, 2013 at 6:55 AM