Francis Ducharme Francis Ducharme - 2 months ago 42
C# Question

Obsure OData error message

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

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 this change brings.

So, you can, and maybe should, enable OData Model Bound Attributes 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!).