25

I've a WebAPI 2.2 service with OData support.

My controller has an action which returns an IQuerable<Entity>, but I'm unable to use $filter=substringof function even if I allow all functions.

[Authorize]
public class MyController : ODataController
{
    [EnableQuery(AllowedFunctions=AllowedFunctions.All)]
    public IQueryable<Entity> GetEntities()
    {
      return GetMyQueryable();
    }
}

When I hit a URL like http://localhost:49844/Entities/?$filter=substringof('Queen',Name)

I get an error saying substringof is not allowed.

{
"error": {
    "code": "",
    "message": "The query specified in the URI is not valid. An unknown function with name 'substringof' was found. This may also be a function import or a key lookup on a navigation property, which is not allowed.",
    "innererror": {
        "message": "An unknown function with name 'substringof' was found. This may also be a function import or a key lookup on a navigation property, which is not allowed.",
        "type": "Microsoft.OData.Core.ODataException",

Any idea why I might be seeing this error?

JDB
  • 25,172
  • 5
  • 72
  • 123
BuddhiP
  • 6,231
  • 5
  • 36
  • 56

1 Answers1

58

substringof() is a V3 function while contains() is a V4 function.

Try contains:

$filter=contains(Name,'Queen')
Jerther
  • 5,558
  • 8
  • 40
  • 59
Tan Jinfu
  • 3,327
  • 1
  • 19
  • 20
  • 2
    substringof() is a V3 function while contains is a V4 function. If contains not work, can you share the error message? – QianLi Jul 29 '14 at 03:34
  • I was wrong to say 'contain'gave the same error. When using contain it says "The query specified in the URI is not valid. Function 'startswith' is not allowed. To allow it, set the 'AllowedFunctions' property on EnableQueryAttribute or QueryValidationSettings.", But I think I'm doing this part. However, AllowedFunctions enum has not updated to reflect changes in functions for OData V4 – BuddhiP Jul 30 '14 at 05:50
  • @QianLi Thank you for pointing out the V3/V4 difference. I did not notice it. And since AllowedFunctions enum lists SubstringOf and no Contain there, I thought SubstringOf is still the way to go – BuddhiP Jul 30 '14 at 05:52
  • This is not a direct answer, but this pointed me in the right direction, I still do not know what was the exact problem, but I created a new WebAPI and project, and it just supported contains function. Then I removed all the unnecessary NuGet packages in my original WebAPI and that seemed to fix the issue. No need to use AllowedFunctions enumeration, everything enabled by default. – BuddhiP Jul 30 '14 at 12:47
  • 1
    This seemed to fix my issue, I was reading the wrong docs and google lead me here, thanks. – James Peruggia Oct 07 '15 at 19:45
  • How do I use startsWith? Microsoft.OData.ODataException: An unknown function with name 'startsWith' was found. – Arrow_Raider Jul 21 '21 at 16:50