OData Error: The query specified in the URI is not valid. The property cannot be used in the query option

Question

I'm trying to get an OData endpoint up and working and I'm having this error that even Google doesn't have much to say about.

I have created an Entity Framework EDMX context (database first), had the designer generate 2 models from it.

Everything is working fine, except $filter queries fail.

I can do this fine:

http://localhost:27164/Projects(6587660)

Which retrieves the Project with a primary ID of 6587660.

But any $filter requests as such:

http://localhost:27164/Projects?$filter=ProjectID eq 6587660

Will fail with the following error:

The query specified in the URI is not valid. The property 'ProjectID' cannot be used in the $filter query option.

I've also tried querying other properties, string properties too. Same error.

I've checked that the model generated by EF doesn't have any attributes on the properties, they don't.

Here's my Register method in WebApiConfig.cs module:

using System.Web.OData.Builder;
using System.Web.OData.Extensions;

public static void Register(HttpConfiguration config)
{
    // Web API configuration and services
    // Configure Web API to use only bearer token authentication.
    config.SuppressDefaultHostAuthentication();
    config.Filters.Add(new HostAuthenticationFilter(OAuthDefaults.AuthenticationType));


    ODataModelBuilder builder = new ODataConventionModelBuilder();
    builder.EntitySet<DB.Project>("Projects");
    config.MapODataServiceRoute(
        routeName: "ODataRoute",
        routePrefix: null,
        model: builder.GetEdmModel()
    );           

}

Here's the Projects controller (GetProjects is the called method when doing a $filter query):

public class ProjectsController : ODataController
{
    private AppContext db = new AppContext();

    //I've tried decorating with that: [EnableQuery(AllowedQueryOptions = System.Web.OData.Query.AllowedQueryOptions.All, AllowedArithmeticOperators = System.Web.OData.Query.AllowedArithmeticOperators.All)] and no go
    [EnableQuery]
    public IQueryable<Project> GetProjects()
    {
        return db.Projects;
    }

    // GET: odata/Projects(5)
    [EnableQuery]
    public SingleResult<Project> GetProject([FromODataUri] int key)
    {
        return SingleResult.Create(db.Projects.Where(project => project.ProjectID == key));
    }

    /*
    // PUT: odata/Projects(5)
    public IHttpActionResult Put([FromODataUri] int key, Delta<Project> patch)
    {
        Validate(patch.GetEntity());

        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }

        Project project = db.Projects.Find(key);
        if (project == null)
        {
            return NotFound();
        }

        patch.Put(project);

        try
        {
            db.SaveChanges();
        }
        catch (DbUpdateConcurrencyException)
        {
            if (!ProjectExists(key))
            {
                return NotFound();
            }
            else
            {
                throw;
            }
        }

        return Updated(project);
    }

    // POST: odata/Projects
    public IHttpActionResult Post(Project project)
    {
        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }

        db.Projects.Add(project);
        db.SaveChanges();

        return Created(project);
    }

    // PATCH: odata/Projects(5)
    [AcceptVerbs("PATCH", "MERGE")]
    public IHttpActionResult Patch([FromODataUri] int key, Delta<Project> patch)
    {
        Validate(patch.GetEntity());

        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }

        Project project = db.Projects.Find(key);
        if (project == null)
        {
            return NotFound();
        }

        patch.Patch(project);

        try
        {
            db.SaveChanges();
        }
        catch (DbUpdateConcurrencyException)
        {
            if (!ProjectExists(key))
            {
                return NotFound();
            }
            else
            {
                throw;
            }
        }

        return Updated(project);
    }

    // DELETE: odata/Projects(5)
    public IHttpActionResult Delete([FromODataUri] int key)
    {
        Project project = db.Projects.Find(key);
        if (project == null)
        {
            return NotFound();
        }

        db.Projects.Remove(project);
        db.SaveChanges();

        return StatusCode(HttpStatusCode.NoContent);
    }
    */

    protected override void Dispose(bool disposing)
    {
        if (disposing)
        {
            db.Dispose();
        }
        base.Dispose(disposing);
    }

    private bool ProjectExists(int key)
    {
        return db.Projects.Count(e => e.ProjectID == key) > 0;
    }
}

This is the first time I'm using OData with Database First so I'm not sure what's causing this.

I'm using the latest runtimes from Nuget on .NET 4.5.2.

Solution

From the docs 13.1 Model Bound Attributes:

Now the default setting for WebAPI OData is : client can’t apply $count, $orderby, $select, $top, $expand, $filter in the query, query like localhost\odata\Customers?$orderby=Name will failed as BadRequest, because all properties are not sort-able by default, this is a breaking change in 6.0.0

So, we now need to enable OData Model Bound Attributes which you can do globally with the middle line in the following block (the other two are your code):

ODataModelBuilder builder = new ODataConventionModelBuilder();
config.Count().Filter().OrderBy().Expand().Select().MaxTop(null); //new line
builder.EntitySet<DB.Project>("Projects");

But that is a catch-all and kind of works around the better security/performance this change brings.

So, you can, and maybe should, enable OData Model Bound Attributes using fluent API calls per entity like this:

builder.EntitySet<DB.Project>("Projects"); //your line of code
builder.EntityType<DB.Project>().Filter("ProjectID");

This answer should solve the problem you posted about but, I expect, you will need to take a look at those docs to enable you to work up a comprehensive solution for the rest of your project (unless, of course, you just deploy the one-line catch all!).

---

As the name "Model Bound Attribute" suggests, you can also achieve what you need via attributes on your models, which is covered in (in fact, is the main focus of) the docs too.

---

Edit February 2017:

There appears to be a bug in the per-entity fluent API. Calls to $expand entity-sets intermittently return a 400 Bad Request with the error in the original question despite the entity sets being set up with fluent API. I don't know whether this bug only exists on $expand or with other query params. I also don't know whether it is my code that is causing the problem or an MS bug and therefore something others are encountering. I will investigate this further soon and update this answer. For now I am using the one-line catch all; that works just fine.

Further edit:

I have just reread some of the docs (to try and get this update as understandable as possible) and they seem to imply that the way I now have things set up (with the Global Config one-line-catch-all plus fluent API), the per-entity fluent API will still be respected because:

"Query settings can be placed in many places, with the following precedence from lowest to highest: System Default(not query-able by default), Global Configuration, Model Bound Attribute, Fluent API."

Therefore, maybe this is what you have to do: add the one-line-catch-all and then fine-tune with model-bound-attributes, fluent API or both. I need to test this and will report back soon...

Comments

[Igor]

Could it be the case of the property? Have you tried $filter=projectId eq 6587660 or $filter=projectID eq 6587660 ?

[Francis Ducharme]

@Igor I've tried that. Any other casing will return an error saying the property doesn't exist, so I got the casing right.

[Igor]

Have you tried $filter with another property on the model like a string property?

[Igor]

Do you have any of these options enabled (or not set) that it would prevent you from using $filter? At the moment this is a lot of guessing and question asking, you should provide some code like the configuration AND odata controller you are using to give more insight.

[Ravi Desai]

I am having the exact same problem. I actually cannot use any of the query syntax parameters, $count, $filter, $select, etc. They all fail.

[Ravi Desai]

public class CustomersController : ODataController { ProductsContext db = new ProductsContext(); [EnableQuery(AllowedQueryOptions = System.Web.OData.Query.AllowedQueryOptions.All)] public IQueryable<Customer> Get() { var got = db.Customers.Include( c => c.Orders ); return got.AsQueryable(); } }

[lukkea]

@Igor if the property name was wrong, as you are wondering in your first comment, the error message would report that: the wording of this error message is such that we know the property can be found, it just can't be used in a filter. See my answer for how to allow that property to be filtered. The options you point to in your other comment are for an older version of OData and won't help solve this problem AFAIK. HTH.

[lukkea]

@RaviDesai Take a look at my answer; I hope it solves your problem too; which I expect it will since it is exactly the same ;-).

[Francis Ducharme]

Breaking change alright. It kind of broke my interest in using MS's implementation of OData. Thanks a lot anyways.

[lukkea]

@FrancisDucharme don't be too down on the change; it's a good security/performance enhancement & there is the catch-all-fix if you don't need the granular control. Glad to help. :-)

[Ravi Desai]

Thanks for the pointer to the docs. I'm a little surprised I wasn't able to stumble across with all the google searching I did.

[lukkea]

@RaviDesai - it is difficult to find the breaking change documentation by searching for this error; I'm not sure why but, hopefully, this q&a will fill that gap...

[Ravi Desai]

@lukkea I'm sure the SO issue will help, but if any on the OData team are listening, a better error message would have went a long way towards pointing me in the right direction as well.

[Moshi]

Your answer works fine for options you mentioned, but i didn't find `callback' option.

[lukkea]

@Moshii - not too sure what you are referring to, but if you add a question on SO with the context you'll probably get the answer you need.

[Shaiju T]

Thanks, and for config.Count() to work add using System.Web.OData.Extensions; hope helps someone.

[NickG]

How do you get the "config" object? Where is this from? I'm using .NET core and can't seem to see the equivalent function.

[lukkea]

@NickG take a look at the question - that's where I'm getting the config object from.