OData filter options (handle null or empty values)

Question

We are using $filter system query option in OData to execute filters where the filter value is sent in at runtime.

As an example the OData URL would be like this:

http://services.odata.org/Northwind/Northwind.svc/Customers?$filter=CustomerID eq @InCustomerID and Country eq @InCountry

where @InCustomerID & @InCountry are the input values for the equal filter.

At run time when the user enters some value for Customer ID (say 'ABCD') we would replace the @InCustomerID by 'ABCD'

At runtime the query would be as follows:

http://services.odata.org/Northwind/Northwind.svc/Customers?$filter=CustomerID eq 'ABCD' and Country eq 'US'

In the above case the user has entered the following values: CustomerID => 'ABCD' and Country => 'US'

My question is regarding handling of null values in OData $filter. If the user does not enter any value for CustomerID then we want to select all customers from specific country.

In sql case this would be something like:

select * from Customers where ((CustomerID = @InCustomerID) or (@CustomerID is null)) and (Country = @Country).

Essentially how to handle null or empty values so that the specific predicate in the logical condition would always be true.

Does OData filtering enables this option?

Answer 1

You can compare to null using the equality operator like this:

$filter=CustomerID eq null 

In your case the query would degenerate to something like:

$filter=(CustomerID eq null) or (null eq null) 

Which should work, but it's not very nice. Did you consider removing the predicate completely in such case?