OData Syntax

OData gives your SQL endpoints a query language without you writing a line of SQL: clients express what they need in the URL, and Portway translates it safely. This reference covers each supported query option and its syntax.

Query Options Overview ​

Basic Query Structure ​

GET /api/{environment}/{endpoint}?{query_options}

Example:

GET /api/prod/Products?$select=ItemCode,Description&$filter=Price gt 50&$orderby=Price desc&$top=10

$select - Field Selection ​

Select specific fields from the entity:

Syntax ​

$select=field1,field2,field3

Examples ​

# Single field
GET /api/prod/Products?$select=ItemCode

# Multiple fields
GET /api/prod/Products?$select=ItemCode,Description,Price

# All allowed fields (based on entity configuration)
GET /api/prod/Products

Field Selection Rules ​

• Field names are case-sensitive
• Only fields listed in AllowedColumns can be selected
• Invalid field names return an error
• If no $select is specified, all allowed fields are returned

$filter - Filtering Data ​

Filter results based on conditions:

Basic Syntax ​

$filter=field operator value

Comparison Operators ​

String Functions ​

Logical Operators ​

Filter Examples ​

# Exact match
GET /api/prod/Products?$filter=ItemCode eq 'PROD001'

# Numeric comparison
GET /api/prod/Products?$filter=Price gt 50.00

# String contains
GET /api/prod/Products?$filter=contains(Description,'Widget')

# Multiple conditions
GET /api/prod/Products?$filter=Price gt 100 and Assortment eq 'Electronics'

# OR condition
GET /api/prod/Products?$filter=Status eq 'Active' or Status eq 'Pending'

# Complex filter
GET /api/prod/Products?$filter=(Price gt 100 and Price lt 500) or contains(Description,'Special')

$orderby - Sorting Results ​

Sort results by one or more fields:

Syntax ​

$orderby=field [asc|desc]

Examples ​

# Single field ascending (default)
GET /api/prod/Products?$orderby=Name

# Single field descending
GET /api/prod/Products?$orderby=Price desc

# Multiple fields
GET /api/prod/Products?$orderby=Category,Price desc

# Complex sorting
GET /api/prod/Products?$orderby=Category asc,Price desc,Name asc

Sorting Rules ​

• Default sort order is ascending
• Use desc for descending order
• Multiple fields are sorted in order listed
• Field names are case-sensitive

$top and $skip - Pagination ​

Control result set size and implement pagination:

$top Syntax ​

$top=number

$skip Syntax ​

$skip=number

Pagination Examples ​

# First 10 items
GET /api/prod/Products?$top=10

# Skip first 20 items
GET /api/prod/Products?$skip=20

# Page 2 with 10 items per page
GET /api/prod/Products?$top=10&$skip=10

# Page 3 with 25 items per page
GET /api/prod/Products?$top=25&$skip=50

Always include $orderby when paginating to ensure consistent results across pages. Use the NextLink in the response for easy sequential navigation.

$count - Total Result Count ​

When you're paginating, it helps to know how many rows match in total, not just how many came back on this page. Adding $count=true asks Portway to run an additional COUNT query with the same $filter, and the result arrives as a totalCount property in the response:

GET /api/prod/Products?$filter=Price gt 100&$top=10&$count=true

{
  "success": true,
  "count": 10,
  "totalCount": 342,
  "value": [ "..." ]
}

A few things worth knowing:

• count is the number of items on this page; totalCount is the unpaged total that matches your filter
• $top, $skip, $select, and $orderby have no effect on totalCount; only $filter shapes it
• The extra COUNT query only runs when you ask for it, so requests without $count pay nothing
• totalCount is omitted from the response entirely when $count is not requested

Since the count is one more round-trip to your database, it's most useful on the first page of a listing; subsequent pages can usually reuse it.

Combining Query Options ​

Multiple query options can be combined in a single request:

# Complete query example
GET /api/prod/Products
  ?$select=ItemCode,Description,Price,Category
  &$filter=Price gt 50 and Category eq 'Electronics'
  &$orderby=Price desc
  &$top=20
  &$skip=0

Data Types in Queries ​

String Values ​

• Enclose in single quotes: 'value'
• Escape single quotes with double quotes: 'O''Brien'

$filter=Name eq 'Product Name'
$filter=Description eq 'It''s a product'

Numeric Values ​

• No quotes needed
• Use decimal point for floating numbers

$filter=Price eq 99.99
$filter=Quantity gt 10

Date Values ​

• Use ISO 8601 format
• Can compare with standard operators

$filter=CreatedDate gt 2024-01-01T00:00:00Z
$filter=ModifiedDate le 2024-12-31T23:59:59Z

Boolean Values ​

• Use true or false (lowercase)

$filter=IsActive eq true
$filter=IsDeleted eq false

Null Values ​

• Use null keyword

$filter=DeletedDate eq null
$filter=AssignedTo ne null

Special Characters and Encoding ​

URL Encoding ​

Special characters need to be URL encoded:

Reserved Characters ​

These characters have special meaning in OData:

• $ - Query option prefix
• ( ) - Function and grouping
• ' - String delimiter
• , - List separator

Query Response Format ​

Successful queries return a JSON response:

{
  "Count": 50,
  "Value": [
    {
      "ItemCode": "PROD001",
      "Description": "Widget A",
      "Price": 99.99
    },
    {
      "ItemCode": "PROD002",
      "Description": "Widget B",
      "Price": 149.99
    }
  ],
  "NextLink": "/api/prod/Products?$top=10&$skip=20"
}

Response Properties ​

Common Query Patterns ​

Search by Text ​

# Contains search
GET /api/prod/Products?$filter=contains(Description,'widget')

# Starts with search
GET /api/prod/Products?$filter=startswith(Name,'A')

Date Range Queries ​

# Records created this year
GET /api/prod/Orders?$filter=CreatedDate ge 2024-01-01T00:00:00Z

# Records in date range
GET /api/prod/Orders?$filter=OrderDate ge 2024-01-01 and OrderDate lt 2024-02-01

Null Checking ​

# Find unassigned items
GET /api/prod/Tasks?$filter=AssignedTo eq null

# Find completed items
GET /api/prod/Tasks?$filter=CompletedDate ne null

Complex Filters ​

# Multiple conditions with grouping
GET /api/prod/Products
  ?$filter=(Price gt 100 and Price lt 500) and 
           (Category eq 'Electronics' or Category eq 'Computers')

Query Limitations ​

Maximum Values ​

Performance Considerations ​

1. Use indexed fields in filters and sorting
2. Limit result sets with $top
3. Avoid complex string operations on large datasets
4. Use specific filters rather than post-filtering

Error Responses ​

Query Syntax Errors ​

{
  "error": "Invalid filter syntax",
  "details": "Unknown operator 'equals' at position 15",
  "success": false
}

Invalid Field Names ​

{
  "error": "Invalid field name",
  "details": "Field 'InvalidField' is not allowed",
  "success": false
}

Type Mismatch ​

{
  "error": "Type mismatch",
  "details": "Cannot compare string field 'Name' with numeric value",
  "success": false
}

Related Topics ​

• Filter Operations - Detailed filter operations
• Sorting & Pagination - Advanced sorting and pagination
• SQL Endpoints - SQL endpoint configuration
• API Overview - General API reference