Community
Showing results for 
Search instead for 
Do you mean 
Reply

BUG in Web API with OData filtering.

Accepted Solution Solved
Copper Contributor
Posts: 113
Country: Netherlands
Accepted Solution

BUG in Web API with OData filtering.

I'm reposting this because my previous message suddenly was only visible at this link when I was logged in:

 

https://community.act.com/t5/Act-Premium-Web-API/BUG-in-OData-filter/m-p/346646#M515

 

Hi,

 

I think we've found a bug in the Web API regarding OData filtering.

We are working on an automated connection between Act! and ExactOnline, and in that process we want do get the latest created or edited companies, which are not edited by the sync user itself.

 

This works fine:

GET https://demo.actweb.nl/Act.Web.API/api/Companies/?$filter=((edited gt 2018-02-06T12:52:18Z or created gt 2018-02-06T12:52:18Z) and editedBy ne 'Chris Huffman')

SO we have two AND's, namely the first being the edited/created date, the second being the editedBy.

 

But as soon as we enter a third AND like this (comes from configurable sync criteria in our app):

 

we get a 500 error:

 

exceptionMessage": "criteria[0].LogicalOperator is End but is not the last criteria",
"exceptionType": "Act.Framework.Lookups.CriteriaValidationException",
"stackTrace": " at Act.Framework.Lookups.LookupManager.ValidateCriteria(Criteria[] criteria, Boolean failOnMissingColumns)\r\n at Act.Framework.Lookups.LookupManager.ValidateCriteria(Criteria[] criteria)\r\n at Act.Framework.Lookups.LookupManager.LookupCompaniesNarrow(Criteria[] criteria, CompanyLookup lookupToNarrow)\r\n at act.web.api.Services.CompaniesService.LookupCompany(QueryNode node, IActFrameworkWrapper framework, IDictionary`2 mappers) in c:\\gitroot\\act.web.api\\act.web.api\\Services\\CompaniesService.cs:line 282\r\n at act.web.api.Services.CompaniesService.Get(ActFramework framework, IDictionary`2 mappers, ODataQueryOptions`1 options) in c:\\gitroot\\act.web.api\\act.web.api\\Services\\CompaniesService.cs:line 134\r\n at act.web.api.Controllers.CompaniesController.Get(ODataQueryOptions`1 options) in c:\\gitroot\\act.web.api\\act.web.api\\Controllers\\CompaniesController.cs:line 128\r\n at lambda_method(Closure , Object , Object[] )\r\n at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters)\r\n at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments)\r\n at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.ValidateEnd(Task task)\r\n at act.web.api.Security.SecureMessageAttribute.<ExecuteActionFilterAsync>d__5.MoveNext() in c:\\gitroot\\act.web.api\\act.web.api\\Security\\SecureMessageAttribute.cs:line 160\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Web.Http.Filters.ActionFilterAttribute.<CallOnActionExecutedAsync>d__5.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Filters.ActionFilterAttribute.<ExecuteActionFilterAsyncCore>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Controllers.AuthenticationFilterResult.<ExecuteAsync>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__1.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__1.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.Cors.CorsMessageHandler.<SendAsync>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at Microsoft.AspNet.WebApi.Extensions.Compression.Server.BaseServerCompressionHandler.<SendAsync>d__21.MoveNext() in C:\\Repository\\git\\Microsoft.AspNet.WebApi.MessageHandlers.Compression\\src\\Server\\BaseServerCompressionHandler.cs:line 218\r\n--- End of stack trace from previous location where exception was thrown ---\r\n at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n at System.Web.Http.HttpServer.<SendAsync>d__0.MoveNext()"
}

Greetings,

ACT Certified Consultants
TendenZ
the Netherlands

Accepted Solutions
Solution
Accepted by topic author TendenZ
2 weeks ago
Copper Contributor
Posts: 113
Country: Netherlands

Re: BUG in Web API with OData filtering.

[ Edited ]

Hi Stephen,

 

thanks for your answer.

Reading back my original post, I can't believe I posted it wrong. The actual failing query is exactly like your colored example:

 

https://demo.actweb.nl/Act.Web.API/api/Companies/?$filter=(edited gt 2010-02-06T12:52:18Z or created gt 2010-02-06T12:52:18Z) and (editedBy ne 'Chris Huffman') and (idStatus eq 'Customer')

 

So if I understand this correctly, it doesn't accept more than 3 ands in a row (within one 'group') ? So A and B and C is incorrect, but A and (B and C) is correct?

 

This seems to work indeed.

What is the logic behind this? In the ExactOnline API for instance, I can simply do A and B and C:

 

https://start.exactonline.nl/api/v1/1353385/crm/Accounts?$filter=(Modified+gt+DateTime'2018-01-23T14:25:31' or Created+gt+DateTime'2018-01-23T14:25:31') and Modifier+ne+guid'27c69b6e-307e-4835-ad03-e73186755480' and IsSupplier eq true

 

Then I started thinking: "But hey, I let my customers define multiple sync criteria (which gets built to an OData query). That's the C part of the query. Then - if you had two criteria, you'd get A and (B and C and D) which seems incorrect."

 

But strangely enough, this behaviour is not consistent, because although we now again have three and's in a row, it now does not give me no 500 error response).

I have to put it to more testing, though to see if it actually gives the expected results.

Greetings,

ACT Certified Consultants
TendenZ
the Netherlands

View solution in original post


All Replies
Employee
Posts: 56
Country: USA

Re: BUG in Web API with OData filtering.

I am getting the same issue, it appears the second set of parentheses is sending an End statement before that last parameter is read.  I will create a defect on this.  You can rewrite this query to get around this bug as long as you don't have another parameter grouping:

 

?$filter=(idStatus eq 'Customer' and editedBy ne 'Chris Huffman') and edited eq 2015-02-02T00:30:15Z or created eq 2011-04-19T21:51:31Z

Copper Contributor
Posts: 113
Country: Netherlands

Re: BUG in Web API with OData filtering.

Hi Simon, but in the rewritten query you posted, it returns ALL records with either 

 

created eq 2011-04-19T21:51:31Z

 

or

 

(idStatus eq 'Customer' and editedBy ne 'Chris Huffman') and edited eq 2015-02-02T00:30:15Z

 

That's not what I want, the created/edited must be combined with an OR, and parentheses around it so it will be one AND. They are one AND together.

Is there another workaround?

If not, what do you estimate when this is fixed?

Greetings,

ACT Certified Consultants
TendenZ
the Netherlands
Employee
Posts: 56
Country: USA

Re: BUG in Web API with OData filtering.

I really though that would work, but it looks like it ignores all parentheses groupings.  I will see if I can squeeze a fix to this bug into my workload today.

Employee
Posts: 56
Country: USA

Re: BUG in Web API with OData filtering.

[ Edited ]

Taking a second look at the query, I can see the problem now.  You have three groups, but because the second grouping (blue) is not encapsulated within a parent group it terminates the query and does not include the idStatus parameter (red).

 

https://demo.actweb.nl/Act.Web.API/api/Companies?$filter=(edited gt 2010-02-06T12:52:18Z or edited gt 2010-02-06T12:52:18Z) and (editedBy ne 'Chris Huffman') and (idStatus eq 'Customer')

 

If you create a parent grouping, it will prevent the termination and include the third grouping.

 

https://demo.actweb.nl/Act.Web.API/api/Companies/?$filter=(edited gt 2010-02-06T12:52:18Z or edited gt 2010-02-06T12:52:18Z) and ((editedBy ne 'Chris Huffman') and (idStatus eq 'Customer'))

 

This should work as well:

 

?$filter=(created gt 2010-02-06T12:52:18Z or edited gt 2010-02-06T12:52:18Z) and ((editedBy ne 'Chris Huffman') and (idStatus eq 'Customer'))

 

or

 

?$filter=(created gt 2010-02-06T12:52:18Z or edited gt 2010-02-06T12:52:18Z) and (editedBy ne 'Chris Huffman' and idStatus eq 'Customer')

 

 

Sorry, I didn't catch this earlier.

Solution
Accepted by topic author TendenZ
2 weeks ago
Copper Contributor
Posts: 113
Country: Netherlands

Re: BUG in Web API with OData filtering.

[ Edited ]

Hi Stephen,

 

thanks for your answer.

Reading back my original post, I can't believe I posted it wrong. The actual failing query is exactly like your colored example:

 

https://demo.actweb.nl/Act.Web.API/api/Companies/?$filter=(edited gt 2010-02-06T12:52:18Z or created gt 2010-02-06T12:52:18Z) and (editedBy ne 'Chris Huffman') and (idStatus eq 'Customer')

 

So if I understand this correctly, it doesn't accept more than 3 ands in a row (within one 'group') ? So A and B and C is incorrect, but A and (B and C) is correct?

 

This seems to work indeed.

What is the logic behind this? In the ExactOnline API for instance, I can simply do A and B and C:

 

https://start.exactonline.nl/api/v1/1353385/crm/Accounts?$filter=(Modified+gt+DateTime'2018-01-23T14:25:31' or Created+gt+DateTime'2018-01-23T14:25:31') and Modifier+ne+guid'27c69b6e-307e-4835-ad03-e73186755480' and IsSupplier eq true

 

Then I started thinking: "But hey, I let my customers define multiple sync criteria (which gets built to an OData query). That's the C part of the query. Then - if you had two criteria, you'd get A and (B and C and D) which seems incorrect."

 

But strangely enough, this behaviour is not consistent, because although we now again have three and's in a row, it now does not give me no 500 error response).

I have to put it to more testing, though to see if it actually gives the expected results.

Greetings,

ACT Certified Consultants
TendenZ
the Netherlands
Copper Contributor
Posts: 113
Country: Netherlands

Re: BUG in Web API with OData filtering.

Stephen, a second question arises:

 

Is there also $filter support for getting records not containing string X ?

 

Goal is to specify criteria like for instance:

 

idStatus contains 'Customer' (so 'Customer type 1' and 'Customer type 2' or both will apply)

idStatus contains not 'Testcontact' (so if 'Testcontact' is checked in the ID/Status list the company will not sync)

 

not contains or contains(field, 'value') eq false don't work.

Greetings,

ACT Certified Consultants
TendenZ
the Netherlands
Employee
Posts: 56
Country: USA

Re: BUG in Web API with OData filtering.

You can have as many 'And' or 'Or' in a row as needed (limited only by the URL), but when mixing 'And' and 'Or' you need to group them.

 

    this does not work because the last ) after the F terminates the query so the G fails when building the filter:

    filter=(A and B and C and D) and (E or F) and G

    filter=(A and B and C and D) and (E or F) and (G)   -- this does not work because the F) as already terminated the string so (G) fails as well

 

    this works because it see the last ) after the G.

    filter=(A and B and C and D) and ((E or F) and G)  -- this works because the ((E tells that the F) is not the last param, but G) is, so it is included.

    filter=(A and B and C and D) and (E or F or G)  -- this works because (E starts and reads to the G), only because its an or and not an and'.

 

I know, this is not consistent with the OData specification and it really doesn't have anything to do with OData, but more about the Act! SDK and the way the API parses OData and converts it to Act! filters.  I am planning on re-engineering this in the future.

 

 

 

 

 

 

Employee
Posts: 56
Country: USA

Re: BUG in Web API with OData filtering.

Unfortunately, the API does not support not contains yet.  I will add that to my list of things to do. 

Copper Contributor
Posts: 113
Country: Netherlands

Re: BUG in Web API with OData filtering.

Thank Stephen, one more question. To get records which have a Yes/No value checked.

 

mustsync eq true --> Value does not match the column's datatype

mustsync eq True --> Object reference not set to an instance of an object.

 

Strange thing is if I leave out the filter, it represents as a string, not a JSON bool:

 

"customFields": {
    ...
    "mustsync": "False", // note the quotes?!?
    ...
},

 

So if I then compare to "True" (with quotes), it also does not work: 

mustsync eq 'True' --> Value does not match the column's datatype

 

Greetings,

ACT Certified Consultants
TendenZ
the Netherlands