WebApi OData filtering

In the previous post, we discussed the OData support in MicroLite.Extensions.WebApi 3.0.

When it comes to using $filter, we may want to restrict our results based upon properties which are data types other than a string.

OData dates and guids are specified as strings (quoted with single quotes) but allows the data type to prefix the value:

Dates should be specified in the yyyy-mm-dd format and optional include time by adding Thh:mm after dd. Dates in the Round Trip Date Format (“o” in C#) are also supported if you need to include higher precision and/or timezone information.

For a Date: $filter=Joined gt datetime'2010-01-01'
For a DateTime: $filter=Joined gt datetime'2013-10-23T10:00'
For a Guid: $filter=Identifier eq guid'D30648F9-6CAB-47AF-B010-53C8CD1CCB44'

Boolean values (true or false) are supported as unquoted literals:

$filter=Enabled eq true
$filter=Enabled eq false

To specify a decimal, use the M character after the value

$filter=Total gt 125.00M

To specify a double, use the D character after the value

$filter=Area gt 1512.1245D

To specify an integer, just specify a whole integer value

$filter=ItemCount eq 12

To specify a single, use the F character after the value

$filter=Distance gt 1243.224F

In MicroLite WebApi 3.0, the following operators are supported

eq – equal
gt – greater than
ge – greater than or equal
lt – less than
le – less than or equal
ne – not equal

The following string functions are also supported:

startswith – startswith(Name, 'Hayes') eq true would translate to WHERE Name LIKE ‘Hayes%’
endswith – endswith(Name, 'Hayes') eq true would translate to WHERE Name LIKE ‘%Hayes’
substringof – substringof('Hayes', Name) eq true would translate to WHERE Name LIKE ‘%Hayes%’

There are some other OData functions which are defined in OData 3.0 which MicroLite doesn’t currently support. Some of them may be added in future releases but the support in 3.0 allows for the most common types of query.

Advertisements

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