OData Query Guide

Master the power of OData v4 to query, filter, sort, and shape data from the Exepron API. Build complex queries efficiently with minimal overhead.

On this page

What is OData?

OData (Open Data Protocol) is an ISO/IEC approved standard that defines best practices for building and consuming RESTful APIs. It enables you to:

  • Query data with a standardized query language
  • Filter results with complex conditions
  • Sort and paginate large datasets efficiently
  • Select specific fields to reduce payload size
  • Expand related entities in a single request
  • Count results without retrieving all data
Version: Exepron API implements OData v4 specification. All query options are case-sensitive and must be prefixed with $.

Basic Query Structure

GET /api/v1/projects?$filter=status eq 'Active'&$orderby=startDate desc&$top=10

Breaking it down:
- Base URL: /api/v1/projects
- Filter: $filter=status eq 'Active'
- Sort: $orderby=startDate desc
- Limit: $top=10

Query Options Overview

Option Purpose Example
$select Select specific properties to return $select=id,name,status
$filter Filter results based on conditions $filter=status eq 'Active'
$orderby Sort results by one or more properties $orderby=startDate desc
$expand Include related entities $expand=tasks,resources
$top Limit number of results $top=20
$skip Skip a number of results $skip=40
$count Include total count in response $count=true
$search Full-text search across searchable fields $search="project alpha"
$format Specify response format $format=json

$select - Choose Fields

Use $select to specify which properties should be included in the response. This reduces payload size and improves performance.

Basic Select

GET /api/v1/projects?$select=id,name,status,startDate

Response:
{
  "value": [
    {
      "id": 1,
      "name": "Project Alpha",
      "status": "Active",
      "startDate": "2025-01-15"
    }
  ]
}

Select with Navigation Properties

GET /api/v1/projects?$select=name&$expand=manager($select=firstName,lastName)

Response:
{
  "value": [
    {
      "name": "Project Alpha",
      "manager": {
        "firstName": "John",
        "lastName": "Doe"
      }
    }
  ]
}

Select All with Star (*)

GET /api/v1/projects?$select=*
// Returns all properties of the project entity

$filter - Filter Results

The $filter option allows you to specify conditions that each returned item must satisfy.

Comparison Operators

Operator Description Example
eq Equal $filter=status eq 'Active'
ne Not equal $filter=status ne 'Cancelled'
gt Greater than $filter=budget gt 100000
ge Greater than or equal $filter=completionRate ge 0.5
lt Less than $filter=remainingDays lt 30
le Less than or equal $filter=bufferConsumption le 50

Logical Operators

Operator Description Example
and Logical AND $filter=status eq 'Active' and budget gt 50000
or Logical OR $filter=status eq 'Active' or status eq 'Planning'
not Logical NOT $filter=not(status eq 'Cancelled')

String Functions

# Contains
$filter=contains(name, 'Alpha')

# Starts with
$filter=startswith(name, 'Project')

# Ends with
$filter=endswith(email, '@exepron.com')

# To lower case
$filter=tolower(status) eq 'active'

# To upper case
$filter=toupper(code) eq 'PRJ001'

# Trim whitespace
$filter=trim(name) eq 'Project Alpha'

# String length
$filter=length(description) gt 100

# Substring
$filter=substring(code, 0, 3) eq 'PRJ'

# Index of substring
$filter=indexof(name, 'Alpha') ne -1

Date Functions

# Year
$filter=year(startDate) eq 2025

# Month
$filter=month(startDate) eq 2

# Day
$filter=day(startDate) eq 15

# Date comparison
$filter=startDate ge 2025-01-01 and startDate lt 2025-02-01

# Current date/time
$filter=endDate gt now()

# Date arithmetic
$filter=startDate ge now() sub duration'P30D'  # 30 days ago

Collection Functions

# Any - at least one item matches
$filter=tasks/any(t: t/status eq 'Delayed')

# All - all items match
$filter=tasks/all(t: t/percentComplete eq 100)

# Count
$filter=tasks/$count gt 10

Complex Filter Examples

# Projects with high buffer consumption or overdue
$filter=bufferConsumption gt 70 or endDate lt now()

# Active projects in Q1 2025 with budget over 100k
$filter=status eq 'Active' and startDate ge 2025-01-01 and startDate lt 2025-04-01 and budget gt 100000

# Projects with any critical tasks
$filter=tasks/any(t: t/isCritical eq true and t/status ne 'Completed')

# Projects containing 'Launch' managed by specific user
$filter=contains(name, 'Launch') and managerId eq 12345

Null Handling

# Check for null
$filter=endDate eq null

# Check for not null
$filter=endDate ne null

# Null or specific value
$filter=parentProjectId eq null or parentProjectId eq 0

$orderby - Sort Results

Use $orderby to sort results by one or more properties.

Single Property Sort

# Ascending (default)
$orderby=name
$orderby=startDate asc

# Descending
$orderby=startDate desc
$orderby=budget desc

Multiple Property Sort

# Sort by status, then by start date
$orderby=status asc, startDate desc

# Complex multi-level sort
$orderby=priority desc, bufferConsumption desc, name asc

Sort by Navigation Properties

# Sort by manager's last name
$orderby=manager/lastName asc

# Sort by task count (requires $expand)
$orderby=tasks/$count desc

$expand - Include Relations

Use $expand to include related entities in a single request, reducing the need for multiple API calls.

Basic Expand

# Include tasks with project
GET /api/v1/projects/123?$expand=tasks

Response:
{
  "id": 123,
  "name": "Project Alpha",
  "tasks": [
    {
      "id": 1,
      "name": "Design Phase",
      "status": "InProgress"
    },
    {
      "id": 2,
      "name": "Development",
      "status": "NotStarted"
    }
  ]
}

Multiple Expansions

# Expand multiple relationships
$expand=tasks,resources,milestones

Nested Expand

# Expand tasks and their assigned resources
$expand=tasks($expand=assignedResources)

# Multiple levels
$expand=tasks($expand=assignedResources($expand=user))

Expand with Query Options

# Expand with filtering
$expand=tasks($filter=status eq 'Delayed')

# Expand with sorting
$expand=tasks($orderby=dueDate asc)

# Expand with pagination
$expand=tasks($top=5;$skip=0)

# Expand with field selection
$expand=tasks($select=id,name,status)

# Combined options
$expand=tasks(
  $filter=status ne 'Completed';
  $orderby=priority desc;
  $top=10;
  $select=id,name,status,percentComplete
)

Max Expansion Depth

Limit: Maximum expansion depth is 3 levels to prevent performance issues. Deep expansions may timeout or return partial results.

$top & $skip - Pagination

Implement efficient pagination using $top and $skip parameters.

Basic Pagination

# First page (20 items)
$top=20

# Second page
$top=20&$skip=20

# Third page
$top=20&$skip=40

Pagination with Total Count

GET /api/v1/projects?$top=10&$skip=0&$count=true

Response:
{
  "@odata.count": 157,
  "value": [
    // First 10 projects
  ]
}

Client-Side Pagination Implementation

class PaginationHelper {
    constructor(pageSize = 20) {
        this.pageSize = pageSize;
        this.currentPage = 1;
        this.totalCount = 0;
    }

    async fetchPage(pageNumber) {
        const skip = (pageNumber - 1) * this.pageSize;
        const url = `/api/v1/projects?$top=${this.pageSize}&$skip=${skip}&$count=true`;

        const response = await fetch(url, {
            headers: {
                'Authorization': `Bearer ${accessToken}`
            }
        });

        const data = await response.json();
        this.totalCount = data['@odata.count'];
        this.currentPage = pageNumber;

        return {
            items: data.value,
            currentPage: pageNumber,
            totalPages: Math.ceil(this.totalCount / this.pageSize),
            totalCount: this.totalCount
        };
    }

    get hasNextPage() {
        return this.currentPage * this.pageSize < this.totalCount;
    }

    get hasPreviousPage() {
        return this.currentPage > 1;
    }
}

Server-Driven Pagination

Response includes next/previous links:
{
  "@odata.count": 157,
  "@odata.nextLink": "/api/v1/projects?$skip=20&$top=20",
  "value": [...]
}

$count - Get Total Count

Use $count to include or retrieve the total number of matching entities.

Count with Results

GET /api/v1/projects?$filter=status eq 'Active'&$count=true

Response:
{
  "@odata.count": 42,
  "value": [
    // Filtered projects
  ]
}

Count Only (No Data)

GET /api/v1/projects/$count

Response:
42

Count with Filters

# Count active projects
GET /api/v1/projects/$count?$filter=status eq 'Active'

# Count projects with overdue tasks
GET /api/v1/projects/$count?$filter=tasks/any(t: t/dueDate lt now() and t/status ne 'Completed')

Complex Query Scenarios

Real-world examples combining multiple OData features.

1. Dashboard Query

Get active projects with high buffer consumption, including critical tasks:

GET /api/v1/projects?
  $filter=status eq 'Active' and bufferConsumption gt 60&
  $expand=tasks($filter=isCritical eq true;$select=id,name,status,dueDate)&
  $select=id,name,status,bufferConsumption,startDate,endDate&
  $orderby=bufferConsumption desc&
  $top=10

2. Resource Utilization Report

Get resources with their current task assignments:

GET /api/v1/resources?
  $filter=isActive eq true&
  $expand=taskAssignments(
    $expand=task($select=id,name,projectId);
    $filter=task/status eq 'InProgress'
  )&
  $select=id,name,email,department,utilizationRate&
  $orderby=utilizationRate desc

3. Project Timeline View

Get projects for Gantt chart with milestones and dependencies:

GET /api/v1/projects?
  $filter=startDate ge 2025-01-01 and startDate le 2025-12-31&
  $expand=
    tasks($select=id,name,startDate,endDate,predecessors,successors),
    milestones($select=id,name,date,status)&
  $select=id,name,startDate,endDate,status,percentComplete&
  $orderby=startDate asc

4. Critical Chain Analysis

Get critical chain information with buffer status:

GET /api/v1/projects/123?
  $expand=
    criticalChain($select=id,name,duration,remainingDuration),
    projectBuffer($select=size,consumed,status),
    feedingBuffers($select=chainId,size,consumed)&
  $select=id,name,criticalChainLength,bufferConsumption,projectedCompletion

5. Team Performance Metrics

Get team members with completed tasks in the last 30 days:

GET /api/v1/users?
  $filter=role eq 'TeamMember'&
  $expand=completedTasks(
    $filter=completionDate ge ${new Date(Date.now() - 30*24*60*60*1000).toISOString()};
    $select=id,name,completionDate,estimatedDuration,actualDuration
  )&
  $select=id,firstName,lastName,email&
  $orderby=completedTasks/$count desc

Performance Best Practices

1. Use $select to Reduce Payload

Only request the fields you need:

# Bad - returns all properties
GET /api/v1/projects

# Good - returns only needed fields
GET /api/v1/projects?$select=id,name,status

2. Paginate Large Result Sets

Always use pagination for potentially large collections:

# Bad - returns all records
GET /api/v1/tasks

# Good - returns manageable chunks
GET /api/v1/tasks?$top=50&$skip=0

3. Optimize $expand Usage

Be selective with expansions:

# Bad - expands everything
GET /api/v1/projects?$expand=*

# Good - expands only what's needed with constraints
GET /api/v1/projects?$expand=tasks($top=5;$select=id,name)

4. Use Filters Early

Filter at the database level rather than client-side:

# Bad - get all then filter in code
const projects = await fetch('/api/v1/projects');
const active = projects.filter(p => p.status === 'Active');

# Good - filter on server
const active = await fetch("/api/v1/projects?$filter=status eq 'Active'");

5. Batch Related Requests

Use $expand instead of multiple requests:

# Bad - multiple requests
const project = await fetch('/api/v1/projects/123');
const tasks = await fetch('/api/v1/projects/123/tasks');
const resources = await fetch('/api/v1/projects/123/resources');

# Good - single request with expansions
const project = await fetch('/api/v1/projects/123?$expand=tasks,resources');

6. Index Considerations

Common filter and sort properties should be indexed:

  • status, startDate, endDate for projects
  • dueDate, assignedTo, priority for tasks
  • email, department for resources

Response Time Guidelines

Operation Expected Time Max Time
Simple query (no expand) < 200ms 1s
Single level expand < 500ms 2s
Complex query (multiple expands) < 1s 5s
Count operation < 100ms 500ms

Common Issues & Solutions

Query Syntax Errors

Error: The query specified in the URI is not valid

Common Causes:

  • Missing quotes around string values
  • Incorrect operator syntax
  • Invalid property names
# Wrong
$filter=status eq Active        # Missing quotes
$filter=status = 'Active'       # Wrong operator

# Correct
$filter=status eq 'Active'

Expand Depth Exceeded

Error: The max expansion depth limit of 3 has been exceeded

Solution: Reduce nesting levels or make separate requests

# Too deep
$expand=tasks($expand=resources($expand=user($expand=department)))

# Better approach
$expand=tasks($expand=resources($select=id,name,userId))

Invalid Date Format

Error: Invalid date time format

Solution: Use ISO 8601 format

# Wrong
$filter=startDate gt '01/15/2025'

# Correct
$filter=startDate gt 2025-01-15T00:00:00Z

Property Not Filterable

Error: The property 'X' cannot be used in a $filter query

Solution: Check API documentation for filterable properties

URL Encoding Issues

Issue: Special characters breaking queries

Solution: Properly encode URLs

// JavaScript
const filter = "name eq 'Project & Task'";
const encoded = encodeURIComponent(filter);
const url = `/api/v1/projects?$filter=${encoded}`;

// Results in:
// /api/v1/projects?$filter=name%20eq%20'Project%20%26%20Task'

Quick Reference Card

Common Patterns

# Active projects this month
$filter=status eq 'Active' and startDate ge 2025-02-01 and startDate lt 2025-03-01

# Top 5 highest priority incomplete tasks
$filter=status ne 'Completed'&$orderby=priority desc&$top=5

# Projects with overdue tasks
$filter=tasks/any(t: t/dueDate lt now() and t/status ne 'Completed')

# Search with highlighting
$search="critical"&$select=id,name,description

# Efficient dashboard query
$select=id,name,status,bufferConsumption&$top=10&$count=true

# Export-friendly query (all fields, no expansions)
$select=*&$top=1000

Additional Resources