Filtering dates between x & y using ODATA

votes

I'm looking into the new ASP.NET Web API as a reporting tool.

In SQL I would do this:

WHERE order_date 
    BETWEEN to_date ('2003/01/01', 'yyyy/mm/dd') 
    AND to_date ('2003/12/31', 'yyyy/mm/dd');

...how do these type of command translate in the ODATA protocol URL?

votes

With the Visual Studio 2012.2 update, OData support is back and the following url works for filtering dates:

http://host/api/controller?$filter=order_date+gt+datetime'2003-01-01'+and+order_date+lt+datetime'2003-12-31'

If you want to include a time, you should specify the dates in ISO 8601 format.

votes

Using the WCF Data Services client API, you would be able to express the date constraints using a LINQ clause, e.g. context.Orders.Where(o => o.OrderDate < DateTime.Now && o.OrderDate > DateTime.Now.AddDays(-7)). In the URL that is generated from that LINQ query, you'll see query parameters such as the $filter option, e.g. http://services.odata.org/(S(readwrite))/OData/OData.svc/Products()?$filter=(ReleaseDate lt datetime'2012-06-19T08:16:01.4283521-07:00') and (ReleaseDate gt datetime'2012-06-26T08:16:01.4293524-07:00'). LINQPad is a great way to see what those URLs would look like if you're familiar with LINQ

votes

The OData support in ASP.NET Web API is gone and QueryableAttribute will not be there for RTM (at least it seems that way at the moment).

They also added this line as a commit message:

We plan to provide much better OData support as a separate feature based on the OData library.

Not a complete answer to your question but hope it helps.