OData Syntax

SQL endpoints, and static endpoints with EnableFiltering, accept OData query options in the URL (GET) or in the request body (QUERY). Queries are translated to parameterized SQL.

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

$select ​

$select=field1,field2

Only columns in AllowedColumns (public names) can be selected; other names return 400. Without $select, all allowed columns are returned.

$filter ​

$filter=field operator value

Operators, functions and literals: Filter operations.

$orderby, $top and $skip ​

Sort syntax, paging defaults and nextLink: Sorting & Pagination.

$count ​

With $count=true, Portway runs an additional COUNT query with the same $filter and adds totalCount:

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

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

The count property is the number of rows in the response; totalCount ignores $top, $skip, $select and $orderby and is omitted without $count=true.

$expand ​

GET /api/prod/Products?$expand=Category

The relationship is declared in entity.json and executed as a SQL JOIN on Table and View endpoints. Details: Expanding Related Data.

Response format ​

{
  "success": true,
  "count": 2,
  "value": [
    { "ItemCode": "PROD001", "Description": "Widget A", "Price": 99.99 },
    { "ItemCode": "PROD002", "Description": "Widget B", "Price": 149.99 }
  ],
  "nextLink": "/api/prod/Products?$top=2&$skip=2"
}

A request by id (/api/prod/Products/PROD001) returns the row without the envelope.

URL encoding ​

Errors ​

Malformed options return 400 with "Invalid OData query. Check $filter, $select, $orderby and $expand syntax."; columns outside AllowedColumns in $select return 400 with "Selected columns not allowed: …".

Related topics ​

• Filter Operations
• Sorting & Pagination
• HTTP Methods
• SQL Endpoints