System Query Option $filter in OData v4 (canonical functions)

Question

According to this documentation: http://docs.oasis-open.org/odata/odata/v4.0/errata02/os/complete/part2-url-conventions/odata-v4.0-errata02-os-part2-url-conventions-complete.html#_Toc406398094 I should be able to pass with $filter parameter queries like "$filter=contains(PropertyName, 'SomeValue')".

When I pass logical operators (for example: $filter=PropertyName eq 'SomeValue') it works fine. But functions like 'contains', 'substring', 'endswith', 'startswith' etc. never works -- query results from applying ODataQueryOptions to DbSet are always an empty set.

Any ideas why?

Answer 1

So you mean $filter=contains(PropertyName, 'SomeValue') not worked with your service? The feature itself can work well, try with http://services.odata.org/TripPinWebApiService/People?$filter=contains(FirstName, 'Angel'). It will help others solve your problems if you show more details of your code. You can go https://github.com/OData/ODataSamples/tree/master/Scenarios/TripPin to see the implementation of an OData V4 sample service or go to http://aspnet.codeplex.com/SourceControl/latest#Samples/WebApi/OData/v4/ODataQueryableSample/ to understand how Queryable and $filter work.

Since you didn't post your code, I'm not sure what's the problem, I just have a quick implmentation and the $filter can work well. Hope that can help.

Model class of Person

public class Person
    {
        [Key]
        public String ID { get; set; }

        [Required]
        public String FirstName { get; set; }

        [Required]
        public String LastName { get; set; }

        [Required]
        public int Age { get; set; }

        public String Description { get; set; }
    }

PeopleController.cs

[EnableQuery]
public class PeopleController : ODataController
{
    public IHttpActionResult Get()
    {
        return Ok(DemoDataSources.Instance.People.AsQueryable());
    }
}

WebApiConfig.cs

   public static class WebApiConfig
    {

        public static void Register(HttpConfiguration config)
        {
            config.MapODataServiceRoute("odata", null, GetEdmModel(), new DefaultODataBatchHandler(GlobalConfiguration.DefaultServer));
            config.EnsureInitialized();
        }

        private static IEdmModel GetEdmModel()
        {
            ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
            builder.Namespace = "Demos";
            builder.ContainerName = "DefaultContainer";

            builder.EntitySet<Person>("People");

            var edmModel = builder.GetEdmModel();

            return edmModel;
        }
    }

And then the request can work http://localhost:21830/People?$filter=contains(Description,'Lorem') correctly

Answer 2

I finally found out what is probably wrong, but I don't know how to fix it.

If I'm trying to pass parameter queries like that:

http://(...)/People?$filter=SomeProperty eq 'Foo'

In my WebAPI controller method after applying OData Query Options to my query, that is:

IQueryable<People> queryResults = (IQueryable<People>)queryOptions.ApplyTo(query);

In queryResults there is a fragment like that:

WHERE (`Project1`.`SomeProperty` = @p__linq__0) 

And everything works fine. But when I pass query like that:

http://(...)/People?$filter=contains(SomeProperty, 'Foo')

In queryResults I can see that:

WHERE (`Project1`.`SomeProperty` LIKE '%p__linq__0%')

And there is always no results. I don't know if I understand it correctly, but it seems like it is looking for SomeProperty values containing text 'p__linq__0' instead of looking for values that contains the value of p__linq__0 (which is 'Foo').

---

Thank you for your response, @QianLi. My controller looked like that:

public class PeopleController : ApiController
{
    readonly PeopleContext _context = new PeopleContext();

    public PageResult<People> Get(ODataQueryOptions<People> queryOptions)
    {
        var query = _context.People.OrderBy(x => x.SomeProperty1);
        var queryResults = (IQueryable<People>)queryOptions.ApplyTo(query);
        long cnt = 0;
        if (queryOptions.Count != null)
            cnt = long.Parse(Request.Properties["System.Web.OData.TotalCount"].ToString());

        return new PageResult<People>(queryResults, null, cnt);
    }
}

(This weird workaround with 'cnt' is explained there: Items count in OData v4 WebAPI response) But now I give up on using Count and I changed controller implementation to something like described by you:

[EnableQuery]
public class PeopleController : ODataController
{
    public IHttpActionResult Get()
    {
        var query = _context.People.OrderBy(x => x.SomeProperty1);
        return Ok(query);
    }
}

And it works the same as before - filter queries with eq, lt etc. works fine, contains doesn't work at all.

Edit: I know! The problem can be a result of using this fix: http://www.nuget.org/packages/Patches.System.Web.OData/5.3.0-datetimefixes instead of official OData library. But I need this datetimefixes very much...