How to create query in ODataV4 Adaptor for filter query contains Integer and Date type in C#?

Question

I have tried using the https://services.odata.org/V4/Northwind/Northwind.svc/Orders?$count=true&$filter=(startswith(cast(Freight, 'Edm.String'),'10'))&$skip=0&$top=12 this query also but its not working . can any one come up with good solutions will help alot.

Answer 1

A Place to Start : This may be of help. Try these sites:

OData API Explorer - https://services.odata.org/ODataAPIExplorer/ODataAPIExplorer.html

OData Services Reference - https://www.odata.org/odata-services/ - Make sure that you choose OData v4.

Whenever I get stuck I start simplifying things. I would start out with the filter and make sure that you can do the CAST.

Good luck! Please let us know how this goes!!

Martin

Answer 2

You cannot directly perform string comparisons on non string types, Cast is for entity and complex types, and not primitive values. SO has lots of discussion on this like casting int to string in OData raises error

In OData v4, the CAST function is used to cast entity types into derived types that are defined in the model or base types. This helps for TPH scenarios where the controller represents the base type of resource but each individual row may be a different derided type, but also for TPT where multiple controllers represent resources that all inherit from a common type.

When designing an OData API, if you want to allow string searches on specific numeric fields, the simple approach is to expose those fields via a computed property on the entity (or DTO) definition.

---

However, because cast is not supported in this way, you can use numeric functions to approximate STARTSWITH:

1. Use numeric range expressions when the range is more than a single discrete integer value
   $filter=Freight ge 10 and Freight lt 20
   https://services.odata.org/V4/Northwind/Northwind.svc/Orders?$count=true&$filter=Freight ge 10 and Freight lt 20

2. Use Floor to compare on a discrete integer value ignoring the decimals
   $filter=floor(Freight) eq 10
   https://services.odata.org/V4/Northwind/Northwind.svc/Orders?$count=true&$filter=floor(Freight) eq 10

The current Max value of Freight in that service is 1007.64, so theoretically you could use a combination filter to return all records that start with '1':
-brackets are redundant with this usage of or, they are shown here to isolate the criteria

$filter=floor(Freight) eq 1
        or (Freight ge 10 and Freight lt 20)
        or (Freight ge 100 and Freight lt 200)
        or (Freight ge 1000 and Freight lt 2000)

https://services.odata.org/V4/Northwind/Northwind.svc/Orders?$count=true&$filter=floor(Freight) eq 1 or Freight ge 10 and Freight lt 20 or Freight ge 100 and Freight lt 200 or Freight ge 1000 and Freight lt 2000 &$top=12

If the user entered a search term of '14', you simply replace 1 in the previous expression with '14' (the value for upper limits to replace is n+1)

$filter=floor(Freight) eq 14
        or (Freight ge 140 and Freight lt 150)
        or (Freight ge 1400 and Freight lt 1500)
        or (Freight ge 14000 and Freight lt 15000)

ENDSWITH on integers can be approximated using modulus to give you an idea, you can return all records where the Freight ends with '10' using a modulus of 100:

$filter=floor(Freight) mod 100 eq 10
https://services.odata.org/V4/Northwind/Northwind.svc/Orders?$count=true&$filter=floor(Freight) mod 100 eq 10 &$top=12&

CONTAINS could therefor be approximated using a combination of the two above approaches:

• Contains '1':

     $filter=(floor(Freight) mod 10) eq 1
            or (floor(Freight) mod 100) ge 10 and (floor(Freight) mod 100) lt 20            
            or (floor(Freight) mod 1000) ge 100 and (floor(Freight) mod 1000) lt 200
            or (floor(Freight) mod 10000) ge 1000 and (floor(Freight) mod 10000) lt 2000
  

• Contains '14':

     $filter=(floor(Freight) mod 10) eq 14
            or (floor(Freight) mod 100) ge 14 and (floor(Freight) mod 100) lt 15            
            or (floor(Freight) mod 1000) ge 140 and (floor(Freight) mod 1000) lt 1500
            or (floor(Freight) mod 10000) ge 1400 and (floor(Freight) mod 10000) lt 15000
  

You could use similar techniques to filter on dates, except that date is more complex, for a starts with '1' you have to decide if you are searching on Day, Moth, Year (or perhaps even time components), there are functions to extract the integer component for each of those elements from a date, so you could combine all this to find dates that have a '2' but this has many more permutations:

$filter=(day(OrderDate) mod 10) eq 2 
       or (day(OrderDate) mod 100) ge 20 and (day(OrderDate) mod 100) lt 30
       or (month(OrderDate) mod 10) eq 2 
       or (month(OrderDate) mod 100) ge 20 and (month(OrderDate) mod 100) lt 30
       or (year(OrderDate) mod 10) eq 2 
       or (year(OrderDate) mod 100) ge 20 and (year(OrderDate) mod 100) lt 30
       or (year(OrderDate) mod 1000) ge 200 and (year(OrderDate) mod 1000) lt 300
       or (year(OrderDate) mod 10000) ge 2000 and (year(OrderDate) mod 10000) lt 3000

https://services.odata.org/V4/Northwind/Northwind.svc/Orders?$count=true&$top=12&$select=OrderDate&$filter=(day(OrderDate) mod 10) eq 2 or (day(OrderDate) mod 100) ge 20 and (day(OrderDate) mod 100) lt 30 or (month(OrderDate) mod 10) eq 2 or (month(OrderDate) mod 100) ge 20 and (month(OrderDate) mod 100) lt 30 or (year(OrderDate) mod 10) eq 2 or (year(OrderDate) mod 100) ge 20 and (year(OrderDate) mod 100) lt 30 or (year(OrderDate) mod 1000) ge 200 and (year(OrderDate) mod 1000) lt 300 or (year(OrderDate) mod 10000) ge 2000 and (year(OrderDate) mod 10000) lt 3000

---

Can it be done, yes, should you be trying to do this... I'm not so sure ;)