Web API and OData V4 Queries, Functions and Attribute Routing Part 2

This post continues on from Getting started with Web API and OData V4.

Part 1 Getting started with Web API and OData V4 Part 1.
Part 2 Web API and OData V4 Queries, Functions and Attribute Routing Part 2
Part 3 Web API and OData V4 CRUD and Actions Part 3
Part 4 Web API OData V4 Using enum with Functions and Entities Part 4
Part 5 Web API OData V4 Using Unity IoC, SQLite with EF6 and OData Model Aliasing Part 5
Part 6 Web API OData V4 Using Contained Models Part 6
Part 7 Web API OData V4 Using a Singleton Part 7
Part 8 Web API OData V4 Using an OData T4 generated client Part 8
Part 9 Web API OData V4 Caching Part 9
Part 10 Web API OData V4 Batching Part 10
Part 11 Web API OData V4 Keys, Composite Keys and Functions Part 11

Breaking changes:
Web API 2.2 OData V4 no longer supports DateTime! DateTimeOffset is supported instead of this. $inlinecount has also been replaced with $count.

Code: https://github.com/damienbod/WebAPIODataV4

Queries
With Web API OData V4, it is possible to create complex queries which can use functions etc to page, fill data tables or filter your data as required. In this post, some of the basic or standard queries are demonstrated.

Example of $expand

http://localhost:51902/odata/Person?$expand=EmailAddress
http://localhost:51902/odata/Person(7)?$expand=EmailAddress
http://localhost:51902/odata/Person?$expand=PersonPhone
http://localhost:51902/odata/Person(7)?$expand=PersonPhone($expand=PhoneNumberType)

Example of $skip, $top

http://localhost:51902/odata/Person?$top=7&$skip=0
http://localhost:51902/odata/Person?$top=7&$skip=7

Example of $orderby

http://localhost:51902/odata/ContactType?$orderby=Name
http://localhost:51902/odata/Person?$orderby=LastName
http://localhost:51902/odata/Person?$orderby=LastName&$top=7&$skip=7

Example of $select

http://localhost:51902/odata/Person?$select=FirstName,MiddleName,LastName

Example of $filter

For this to work, the filter option has to be activated:

[EnableQuery(PageSize = 20, AllowedQueryOptions= AllowedQueryOptions.Filter  )]
public IHttpActionResult Get()
{  
  return Ok(_db.Person.AsQueryable());
}
http://localhost:51902/odata/Person?$filter=FirstName eq 'Ken'

http://localhost:51902/odata/Person?$filter=EmailAddress/any(q: q/EmailAddress1 eq 'kevin0@adventure-works.com')

http://localhost:51902/odata/Person?$expand=EmailAddress&$filter=EmailAddress/any(q: q/EmailAddress1 eq 'kevin0@adventure-works.com')

http://localhost:51902/odata/PersonPhone?$filter=startswith(PhoneNumber,'6')

or urlencoded

http://localhost:51902/odata/Person?$filter=FirstName%20eq%20%27Ken%27

http://localhost:51902/odata/Person?$filter=EmailAddress/any%28q:%20q/EmailAddress1%20eq%20%27kevin0@adventure-works.com%27%29

http://localhost:51902/odata/Person?$expand=EmailAddress&$filter=EmailAddress/any%28q:%20q/EmailAddress1%20eq%20%27kevin0@adventure-works.com%27%29

http://localhost:51902/odata/PersonPhone?$filter=startswith%28PhoneNumber%20,%20%276%27%29

The cast function is also supported with $filter

http://localhost:51902/odata/PersonPhone?$filter=startswith(PhoneNumber, cast('42', Edm.String))

http://localhost:51902/odata/PersonPhone?$filter=startswith%28PhoneNumber%20,%20cast%2842,%20%27Edm.String%27%29%29

Example of $count

This replaces $inlinecount from V3.

Count also needs to be enabled.

[EnableQuery(PageSize = 20, AllowedQueryOptions= AllowedQueryOptions.Count)]
public IHttpActionResult Get()
{  
  return Ok(_db.Person.AsQueryable());
}
http://localhost:51902/odata/Person?$count=true

This returns the total count of possible records. The odata.count for this example is shown below.

odatav4WebApi_04

Example of $metadata

http://localhost:51902/odata/Person?$format=application/json;odata.metadata=full

ODATA V4 Attribute Routing

The PersonController uses OData attribute routing. This is similar to Web API attribute routing. You can add an ODataRoutePrefix attribute to the controller or ODataRoute attributes to the different actions. As with Web API attribute routing, various parameters can be added to the URL.

[ODataRoutePrefix("Person")]
public class PersonController : ODataController
{
  readonly DomainModel _db = new DomainModel();

  [ODataRoute]
  [EnableQuery(PageSize = 20, AllowedQueryOptions= AllowedQueryOptions.All  )]
  public IHttpActionResult Get()
  {  
    return Ok(_db.Person.AsQueryable());
  }

  [ODataRoute("({key})")]
  [EnableQuery(PageSize = 20, AllowedQueryOptions = AllowedQueryOptions.All)]
  public IHttpActionResult Get([FromODataUri] int key)
  {
    return Ok(_db.Person.Find(key));
  }

OData Attribute Routing can be activated in the Web API config method.

 config.MapODataServiceRoute("odata", "odata", model: GetModel()); 

OData V4 Functions

OData V4 Functions allows for specific or complex selects to be called with a simple function call.
The Web API config needs to be updated from the last post. It is possible to define a function for the whole service, for a collection of specific entities or for an entity itself. In this example, the function is defined for a collection of Person entities.

The Function is defined as follows:

FunctionConfiguration myFirstFunction = persons
  .EntityType
  .Collection
  .Function("MyFirstFunction");
myFirstFunction.ReturnsCollectionFromEntitySet<Person>("Person");

Now that the function is defined, it needs to be added to the Person Controller:

[EnableQuery(PageSize = 20, AllowedQueryOptions = AllowedQueryOptions.All)]
[ODataRoute("Default.MyFirstFunction")]
[HttpGet]
public IHttpActionResult MyFirstFunction()
{
 return Ok(_db.Person.Where(t => t.FirstName.StartsWith("K")));
}

This can then be tested in fiddler:
http://localhost:51902/odata/Person/Default.MyFirstFunction()
odatav4WebApi_03

Note:
The functions or actions require a namespace in the Web API implementation of OData V4. If none is defined for the OData model builder in the configuration, a “Default” namespace is used. This is different to the old WCF Data services which supported V3.

Links:

http://blogs.msdn.com/b/webdev/archive/2014/03/13/getting-started-with-asp-net-web-api-2-2-for-odata-v4-0.aspx

http://msdn.microsoft.com/en-us/library/ff478141.aspx

http://www.odata.org/documentation/odata-version-2-0/uri-conventions/

SAMPLES: https://aspnet.codeplex.com/SourceControl/latest#Samples/WebApi/OData/v4/

https://aspnetwebstack.codeplex.com/SourceControl/latest

http://aspnetwebstack.codeplex.com/

http://www.asp.net/web-api/overview/releases/whats-new-in-aspnet-web-api-22

http://www.asp.net/web-api/overview/odata-support-in-aspnet-web-api/odata-routing-conventions

http://stackoverflow.com/questions/18233059/apicontroller-vs-odatacontroller-when-exposing-dtos

http://meyerweb.com/eric/tools/dencoder/

http://techbrij.com/jquery-datatables-asp-net-web-api-odata-service

http://www.asp.net/web-api/overview/odata-support-in-aspnet-web-api/supporting-odata-query-options

http://www.dotnetcurry.com/showarticle.aspx?ID=1008

http://msdn.microsoft.com/en-us/library/gg312156.aspx

http://sudoman1012.wordpress.com/2014/03/01/networld-getting-started-with-odata-v4-in-asp-net-web-api/

12 comments

  1. Dinesh, first–great blog and article! My concern is that each blog I see on ODATA v4 seems to skirt the big breaking changes issues–namely, the DATETIME vs. DATETIMEOFFSET changes. Many are not in a position to modify the database schema or sometimes even the supporting code. So when attempting to upgrade from V1-3 to V4, we run into this roadblock… what are some practical, thread-safe solutions out there that address this concern?

    1. Hi
      Thanks for your comment.
      I agree with you. I have provided a possible solution for mapping DateTimeOffset to DateTime database fields in Entity Framework so you don’t need to change your schema. This is a breaking change which makes updates difficult.
      https://damienbod.wordpress.com/2014/06/16/web-api-and-odata-v4-crud-and-actions-part-3/

      greetings Damien

  2. Operations on ContactType.ModifiedDate not supported.
    For instance, http://localhost:51902/odata/ContactType?$orderby=ModifiedDate.

  3. […] Web API and OData V4 Queries, Functions and Attribute … – 13/6/2014 · This post continues on from Getting started with Web API and OData V4. Part 1 Getting started with Web API and OData V4 Part 1. Part 2 Web API and …… […]

  4. The path template ‘Default.MyFirstFunction’ on the action ‘MyFirstFunction’ in controller ‘Person’ is not a valid OData path template. Resource not found for the segment ‘Default.MyFirstFunction’.

    I keep getting error! Any hints.

    1. Hi
      This is defined in the OData model

      https://github.com/damienbod/WebAPIODataV4/blob/master/WebAppODataV4/App_Start/WebApiConfig.cs

      The Default is the standard namespace if none is defined.

      greetings Damien

    2. Hi KH,

      You had probably figured out the solution by now, but maybe this could help others that will be facing this issue.

      If your running the App on IIS rather than IIS Express you have to Add this to your web.config

      as seen here http://www.asp.net/web-api/overview/odata-support-in-aspnet-web-api/odata-v4/odata-actions-and-functions.

      1. Cheers
        Thanks for this
        Greetings Damien

  5. hexonx · · Reply

    What is a good strategy for applying authorization to $filter? For example, Customers with Orders: customers can only view their own orders, but Adminstrators can view any customer’s orders. Is there a way to apply authorization to something like “$filter=Customer.CustomerNumber eq 12345”?

  6. I know this is old, but I found this so others will too. In .Net services I generally handle this in the server side logic. For each query made within the endpoints of a controller, I pass the IQueryable statement to a method that applies the relevant security logic for that entity.

    So we have a case statement that evaluates the group permission of the currently logged in user (just use standard HTTP security mechanisms) and for certain groups adds filters to the query, before we apply user’s odata query to the IQueryable.

    The first basic filter is for multi-tenant environments to force that the data is scoped to the current tenant, but unless the tenant identifier is passed in as a headeryou will either need to evaluate it from SAML tokens from the authentication provider or from some custom data store for your solution. Note that for each entity the path back to the tenant descriminator field in a normalised DB could be different, so it is invariably something you have to manage in code manually, but the pattern itself is easy to template out and describe either through inheritance or interfaces.

    1. Get the basic Linq query for the data in the endpoint
    2. Apply the generic user policy for that type of query
    3. Apply the user’s OData parameters to your Linq query
    4. Return the data.

    Note that if your add the EnableQuery attribute to your methods then step 3 happens automatically when you return your query as IQueryable

    1. Cheers thanks Chris
      Greetings Damien

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: