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.

Answer 1

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...

Answer 2

To answer the question asked by @NickG et al: in .Net Core, you do something similar:

private static IEdmModel GetEdmModel()
{
    var builder = new ODataConventionModelBuilder();
    var products = builder.EntitySet<Product>("Products");
    products.EntityType.Count().Filter().OrderBy().Expand().Select();
    return builder.GetEdmModel();
}