Advanced Filtering Data with OData

Modified on Tue, 31 Oct, 2023 at 12:03 PM

There are many ways to filter your data via Odata here are some examples.

TABLE OF CONTENTS

Filtering
Sorting
Selecting fields
Paging
Counting the Number of Objects
- Retrieving a Count of Objects
- (Inline) Count

Filtering

Filters are applied by appending a $filter=... parameter to the request.

For example: {{url}}/dataaccess/customer?$filter=Name eq 'Company'

Comparison Operators

We support the following comparison operators:

Operator	Meaning	Example
eq	equals	/Customer?$filter=Name eq 'John'
ne	does not equal	/Customer?$filter=Name ne 'John'
gt	greater than	/Customer?$filter=Limit gt 1500
lt	less than	/Customer?$filter=Limit lt 1500
ge	greater than or equal to	/Customer?$filter=Limit ge 1500
le	less than or equal to	/Customer?$filter=Limit le 1500

Arithmetic Operators

Operator	Meaning	Example	Returns
add	add	/Products?$filter=Prices add 2 eq 10	All products with price 8
sub	minus	/Products?$filter=Prices sub 2 eq 10	All products with price 12
mul	multiplied by	/Products?$filter=Prices mul 2 eq 10	All products with price 5
div	divided by	/Products?$filter=Prices div 2 eq 10	All products with price 20
mod	modulus	/Products?$filter=Prices mod 5 eq 0	All products with price divisible by 5

Functions

Function	Example	Returns
contains¹	/Employees?$filter=contains(Name, 'f')	All employees with names that contain an ‘f’
startswith	/Employees?$filter=startswith(Name, 'f')	All employees with names that start with ‘f’
endswith	/Employees?$filter=endswith(Name, 'f')	All employees with names that end with ‘f’
length	/Employees?$filter=length(Name) eq 5	All employees with names that have a length of 5
year	/Employees?$filter=year(DateOfBirth) eq 1990	All employees born in the year 1990
month	/Employees?$filter=month(DateOfBirth) eq 5	All employees born in May
day	/Employees?$filter=day(DateOfBirth) eq 31	All employees born on the 31st day of the month
hour	/Employees?$filter=hour(Registration) eq 13	All employees registered between 13:00 (1 PM) and 13:59 (1:59 PM)
minute	/Employees?$filter=minute(Registration) eq 55	All employees registered on the 55th minute of any hour
second	/Employees?$filter=second(Registration) eq 55	All employees registered on the 55th second of any minute of any hour

Combining Filters

Filters can be combined with and, or, not, and ().

For example: ?$filter=Name eq 'John' and (Age gt 65 or Age lt 11)

Combination	Example
and	/Person?$filter=Name eq 'John' and Age gt 65
or	/Person?$filter=Age gt 65 or Age lt 11
not	/Person?$filter=not(Name eq 'John')
( )	/Person?$filter=Name eq 'John' and (Age gt 65 or Age lt 11)

Sorting

You can sort the result using the

$orderby query option.

For example: ?$orderby=Name or ?$orderby=BirthPlace/CityName

The default direction is ascending, and you can make this explicit.

For example: ?$orderby=Name asc

You can also order the result in a descending direction.

For example: ?$orderby=Name desc

It is possible to sort on multiple attributes, which have to be comma-separated.

For example: ?$orderby=Name asc,Age desc

Selecting fields

You can select which attributes and associations to return by specifying the$selectquery option.

For example?$select=Name,Address

Paging

Paging allows you to load data incrementally to better handle large amounts of data. Paging occurs when the client requests a lot of data, and the server returns a subset and a link to request the rest.

Top (Limit)

You can limit the number of returned objects using the $top query option, where the limit is a positive integer.

For example:?$top=100

Skip (Offset)

You can skip a number of objects before retrieving the result using the $skip query option, where the offset is a positive integer.

For example:?$skip=100

will return objects starting with the 101st object in the list.

Null Values

You can compare values against the null value.

For example: ?$filter=Name eq null

In this example ,Name is a string attribute that can have no assigned value in the database. Note that null means no value as opposed to '' (which is an empty string).

When you filter against associations, null literals can be quite useful.

For example: ?$filter=Association_A_B ne null

In this example, you query for objects of entity type A that have at least one association set to objects of entity type B.

Counting the Number of Objects

Retrieving a Count of Objects

You can find out how many objects there are by passing the $count

query option. In this case, the result is an integer which is the number of objects.

For example: {{url}}/dataaccess/customer/$count

(Inline) Count

Set the $count query option to true, a count of the number of items returned will be included in the result.

For example:

{{url}}/dataaccess/customer?$count=true