31

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?

mhu
  • 17,720
  • 10
  • 62
  • 93
Venki
  • 2,129
  • 6
  • 32
  • 54

1 Answers1

36

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?

Gonzalo.-
  • 12,512
  • 5
  • 50
  • 82
Vitek Karas MSFT
  • 13,130
  • 1
  • 34
  • 30
  • I agree not a clean solution. But we are restricted because the filter is formed statically and at runtime we may get null values for certain filter input. In such a scenario, there is not any easy alternative. It would be difficult to remove the predicate completely since that would involve some of amount of parsing the url and removing the null predicate – Venki Feb 02 '11 at 18:01