Arithmetic and built-in functions for $filter

While the syntax described for filter in the previous post allows you to do some nifty things, there are still a few more things that an ADO.NET Data Service supports.

The first are operators for arithmetic: +, -, *, /, %. These also have mnemonics:

So the following is a very elaborate but silly filter:

/Region?$filter=(1 add 1 eq 2) and (1 sub 1 eq 0) and (1 mul 1 eq 1) and (1 div 1 eq 1) and (1 mod 1 eq 0)

There are also a number of built-in functions that you can use. These are fairly simple and are likely to be implemented in some way by the data provider for your data service. These functions must be recognized by the provider for the data source to be usable; the provider for Linq to Objects and Linq to Entities already recognize them and in the latter case, the Entity Framework is able to have them execute directly on the database server, for best efficiency.

The functions are the following. I'll give an obvious example of each that always evaluates to true, just to show how they can be used. Remember you can always use properties or more complex expressions in place of constants.

String Functions

contains - checks whether a string is contained in another one
/Region?$filter=contains('abc', 'ab')

endswith - checks whether a string ends with another one
/Region?$filter=contains('abc', 'bc')

startswith - checks whether a string starts with another one
/Region?$filter=contains('abc', 'b')

indexof - gets the index of a substring (zero-based)
/Region?$filter=2 eq indexof('abc', 'c')

insert - inserts a string inside another one
/Region?$filter='abcd' eq insert('ad', 1, 'bc')

tolower - converts a string to lowercase
/Region?$filter='abc' eq tolower('ABC')

toupper - converts a string to uppercase
/Region?$filter='ABC' eq toupper('abc')

trim - trims leading and trailing whitespace from a string
/Region?$filter='abc' eq trim(' abc ')

remove - removes characters from a string from a given offset (length optional)
/Region?$filter='abc' eq remove('a00bc', 1, 2)
/Region?$filter='a' eq remove('a00bc', 1)

substring - gets characters from a string from a given offset (length optional)
/Region?$filter='00' eq substring('a00bc', 1, 2)
/Region?$filter='00bc' eq substring('a00bc', 1)

concat - concatenates two strings
/Region?$filter='abc' eq concat('ab', 'c')

length - gets the length of a string
/Region?$filter=3 eq length('abc')

DateTime Functions

year - gets the year of a DateTime value
/Region?$filter=1990 eq year('1990-12-20')

month - gets the month of a DateTime value
/Region?$filter=12 eq month('1990-12-20')

day - gets the day of the month of a DateTime value
/Region?$filter=20 eq day('1990-12-20')

hour - gets the hour of the day of a DateTime value
/Region?$filter=14 eq hour('1990-12-20T14:10:20')

minute - gets the minute of the hour of a DateTime value
/Region?$filter=10 eq minute('1990-12-20T14:10:20')

second - gets the second of the minute of a DateTime value
/Region?$filter=20 eq second('1990-12-20T14:10:20')

Math Functions

round - rounds a value to its nearest integral value
Region?$filter=2 eq round(1.8)

floor - gets the largest integral value (in magnitude) that is less than or equal a given value
/Region?$filter=1 eq floor(1.8)
/Region?$filter=-2 eq floor(-1.8)

ceiling - gets the smallest integral value (in magnitude) that is greater than or equal a given value
/Region?$filter=2 eq ceiling(1.8)
/Region?$filter=-1 eq ceiling(-1.8)

Type Functions

isof - checks whether something is of the given type - useful in inheritance scenarios
/Region?$filter=isof('Model.Region') -> in the ATOM payload, I see 'Model.Region as the adsm:type - this depends on the namespace for your types)

cast - casts something to a given type - useful in inheritance scenarios
/Region?$filter=(cast('Model.Region'))/RegionID ge 0

Whew! Well, that's it for functions. Put them to good use!

This post is part of the transparent design exercise in the Astoria Team. To understand how it works and how your feedback will be used please look at this post.