OData Query Guide¶

PLAN-150 Phase 4.2: Comprehensive OData query documentation

This guide provides comprehensive documentation for OData query capabilities in Cesivi REST API, achieving feature parity with SharePoint Server.

Table of Contents¶

Overview
$filter Query Option
$orderby Query Option
$expand Query Option
Combined Queries
Best Practices
Performance Considerations
Troubleshooting

Overview¶

Cesivi Server implements full OData v4.0 query support for REST API endpoints, providing powerful filtering, sorting, and expansion capabilities.

Supported Query Options: - $filter - Filter results based on conditions - $orderby - Sort results by one or more fields - $expand - Load related entities (navigation properties) - $select - Choose specific fields to return - $top - Limit number of results - $skip - Skip a number of results (pagination)

Base URL Pattern:

GET /_api/web/lists/getbytitle('{list-title}')/items?{query-options}

$filter Query Option¶

Filter results based on logical conditions and comparisons.

Comparison Operators¶

Operator	Description	Example
`eq`	Equal	`Title eq 'Task 1'`
`ne`	Not equal	`Status ne 'Completed'`
`gt`	Greater than	`Priority gt 5`
`ge`	Greater than or equal	`Priority ge 5`
`lt`	Less than	`Score lt 10.5`
`le`	Less than or equal	`Modified le '2025-01-01'`

Logical Operators¶

Operator	Description	Example
`and`	Logical AND	`Status eq 'Active' and Priority gt 3`
`or`	Logical OR	`Status eq 'Active' or Priority gt 8`
`not`	Logical NOT	`not IsActive eq true`

String Functions¶

Function	Description	Example
`startswith(field, 'value')`	Starts with prefix	`startswith(Title, 'Project')`
`endswith(field, 'value')`	Ends with suffix	`endswith(Title, '.pdf')`
`contains(field, 'value')`	Contains substring	`contains(Description, 'urgent')`
`substringof('value', field)`	Legacy contains (OData v2)	`substringof('urgent', Description)`

Data Types¶

String Values - Use single quotes:

Title eq 'My Document'

Numeric Values - Integers and decimals:

Priority eq 5
Score gt 10.5

Boolean Values:

IsActive eq true
IsCompleted eq false

DateTime Values - ISO 8601 format:

Created gt '2025-01-01T00:00:00Z'
Modified le '2025-12-31T23:59:59Z'

Null Values:

Description eq null
Status ne null

Filter Examples¶

Simple Comparison:

GET /_api/web/lists/getbytitle('Tasks')/items?$filter=Status eq 'Active'

Multiple Conditions:

GET /_api/web/lists/getbytitle('Tasks')/items?$filter=Status eq 'Active' and Priority gt 5

String Functions:

GET /_api/web/lists/getbytitle('Documents')/items?$filter=startswith(Title, 'Project')

Complex Conditions:

GET /_api/web/lists/getbytitle('Tasks')/items
  ?$filter=(Status eq 'Active' and Priority gt 3) or (Status eq 'Urgent')

Date Filtering:

GET /_api/web/lists/getbytitle('Documents')/items
  ?$filter=Modified gt '2025-01-01T00:00:00Z'

Nested Property Filter:

GET /_api/web/lists/getbytitle('Documents')/items
  ?$filter=Author/Title eq 'John Doe'

$orderby Query Option¶

Sort results by one or more fields in ascending or descending order.

Syntax¶

$orderby={field} {direction}, {field2} {direction2}, ...

Directions: - asc - Ascending (default if not specified) - desc - Descending

OrderBy Examples¶

Single Field Ascending:

GET /_api/web/lists/getbytitle('Tasks')/items?$orderby=Title
GET /_api/web/lists/getbytitle('Tasks')/items?$orderby=Title asc

Single Field Descending:

GET /_api/web/lists/getbytitle('Tasks')/items?$orderby=Modified desc

Multiple Fields:

GET /_api/web/lists/getbytitle('Tasks')/items
  ?$orderby=Status asc, Priority desc, Created asc

With Filter:

GET /_api/web/lists/getbytitle('Tasks')/items
  ?$filter=Status eq 'Active'
  &$orderby=Priority desc

Nested Property:

GET /_api/web/lists/getbytitle('Documents')/items
  ?$orderby=Author/Name asc

$expand Query Option¶

Load related entities (navigation properties) to reduce round-trips to the server.

Syntax¶

$expand={navigation-property}
$expand={property1},{property2}
$expand={property}/$expand={nested-property}
$expand={property}($select={field1},{field2})

Property	Description	Example
`Author`	User who created the item	`$expand=Author`
`Editor`	User who last modified	`$expand=Editor`
`ContentType`	Content type information	`$expand=ContentType`
`Folder`	Parent folder (for files)	`$expand=Folder`
`LookupField`	Lookup field values	`$expand=Customer`

Expand Examples¶

Single Navigation Property:

GET /_api/web/lists/getbytitle('Tasks')/items?$expand=Author

Multiple Properties:

GET /_api/web/lists/getbytitle('Documents')/items
  ?$expand=Author,ContentType,Folder

With $select:

GET /_api/web/lists/getbytitle('Documents')/items
  ?$expand=Author($select=Title,Email)
  &$select=Title,Created,Author

Nested Expand (Slash Notation):

GET /_api/web/lists/getbytitle('Documents')/items
  ?$expand=Author/Groups

Nested Expand ($expand Notation):

GET /_api/web/lists/getbytitle('Documents')/items
  ?$expand=Author($expand=Groups)

Complex Nested with Select:

GET /_api/web/lists/getbytitle('Documents')/items
  ?$expand=Author($select=Title;$expand=Groups($select=Name))

Multiple Expands with Options:

GET /_api/web/lists/getbytitle('Documents')/items
  ?$expand=Author($select=Title,Email),ContentType($select=Name)

Combined Queries¶

Combine multiple query options for powerful data retrieval.

Example 1: Active Tasks by Priority¶

GET /_api/web/lists/getbytitle('Tasks')/items
  ?$filter=Status eq 'Active'
  &$orderby=Priority desc
  &$top=10
  &$select=Title,Priority,DueDate

Result: Top 10 active tasks ordered by priority (highest first), showing only Title, Priority, and DueDate.

Example 2: Recent Documents with Author¶

GET /_api/web/lists/getbytitle('Documents')/items
  ?$filter=Modified gt '2025-01-01T00:00:00Z'
  &$orderby=Modified desc
  &$expand=Author($select=Title,Email)
  &$select=Title,Modified,Author
  &$top=50

Result: Last 50 documents modified since Jan 1, 2025, sorted by modification date (newest first), with author details.

Example 3: Complex Filter with Expand¶

GET /_api/web/lists/getbytitle('Documents')/items
  ?$filter=(Status eq 'Active' and Priority gt 5) or startswith(Title, 'Important')
  &$orderby=Priority desc, Modified desc
  &$expand=Author($select=Title),ContentType($select=Name)
  &$select=Title,Status,Priority,Modified,Author,ContentType
  &$top=25

Result: Top 25 items matching complex filter criteria, with author and content type details.

Example 4: Nested Property Filter and Sort¶

GET /_api/web/lists/getbytitle('Documents')/items
  ?$filter=Author/Title eq 'John Doe'
  &$orderby=Author/Name asc, Modified desc
  &$expand=Author($select=Title,Email)

Result: All documents by John Doe, sorted by author name then modification date, with author details.

Best Practices¶

1. Use $select to Minimize Payload¶

Bad:

GET /_api/web/lists/getbytitle('Documents')/items

Good:

GET /_api/web/lists/getbytitle('Documents')/items
  ?$select=Title,Modified,Author

Benefits: Smaller response size, faster serialization, reduced network transfer.

2. Combine $filter and $top for Large Lists¶

Bad:

GET /_api/web/lists/getbytitle('Documents')/items  // Returns all items

Good:

GET /_api/web/lists/getbytitle('Documents')/items
  ?$filter=Status eq 'Active'
  &$top=100

Benefits: Reduces server load, faster response, efficient pagination.

3. Use $expand Carefully¶

Bad: (Too many expands)

GET /_api/web/lists/getbytitle('Documents')/items
  ?$expand=Author,Editor,ContentType,Folder,CreatedBy,ModifiedBy,LookupField1,LookupField2

Good: (Only what you need)

GET /_api/web/lists/getbytitle('Documents')/items
  ?$expand=Author($select=Title),ContentType($select=Name)

Benefits: Avoids N+1 query problems, faster queries, smaller payloads.

4. Filter Before Sorting¶

Queries are executed in this order: 1. $filter - Reduces dataset 2. $orderby - Sorts filtered results 3. $top/$skip - Pagination on sorted results 4. $expand - Loads related entities 5. $select - Projects fields

5. Use Indexed Fields in Filters¶

For best performance, filter on indexed fields: - ID - Title - Created - Modified - Author - Custom indexed columns

Good:

$filter=Modified gt '2025-01-01'  // Modified is indexed

Slower:

$filter=Description eq 'urgent'   // Description might not be indexed

Performance Considerations¶

Query Complexity¶

Complexity	Example	Performance
Simple filter	`Status eq 'Active'`	⚡ Fast
Multiple conditions	`Status eq 'Active' and Priority gt 5`	⚡ Fast
String functions	`contains(Title, 'project')`	⚠️ Moderate
Complex nested	`(A and B) or (C and D) or (E and F)`	⚠️ Moderate
Nested property filter	`Author/Groups/Name eq 'value'`	🐌 Slow

Expand Performance¶

N+1 Query Problem: - Cesivi automatically optimizes expand to avoid N+1 queries - Related entities are loaded using efficient joins/batching

Best Practices: 1. Expand only required properties 2. Use $select within $expand to limit fields 3. Limit nesting depth (max 2-3 levels) 4. Combine with $top to limit result set

Large Result Sets¶

For lists with 1000+ items: 1. Always use $top to limit results (max 5000) 2. Implement pagination using $skip 3. Use $filter to reduce dataset 4. Consider indexed views for complex queries

Pagination Example:

// Page 1 (items 1-100)
GET /items?$top=100&$skip=0

// Page 2 (items 101-200)
GET /items?$top=100&$skip=100

// Page 3 (items 201-300)
GET /items?$top=100&$skip=200

Troubleshooting¶

Common Errors¶

1. Filter Parse Error¶

Error: OData filter parsing failed: Expected identifier at position X

Cause: Invalid filter syntax

Solution: Check for: - Missing quotes around strings: Title eq Task1 → Title eq 'Task 1' - Incorrect operators: Title = 'value' → Title eq 'value' - Unbalanced parentheses: (A and B → (A and B)

2. Property Not Found¶

Error: Property 'FieldName' not found on type

Cause: Field name doesn't exist or typo

Solution: - Verify field internal name (may differ from display name) - Check case sensitivity - Use /_api/web/lists/getbytitle('List')/fields to list available fields

3. Type Conversion Error¶

Error: Cannot compare String with Int32

Cause: Comparing incompatible types

Solution: - String fields: Use quotes: Title eq 'value' - Numeric fields: No quotes: Priority eq 5 - Date fields: ISO format: Created gt '2025-01-01T00:00:00Z'

4. Expand Not Working¶

Issue: Related properties return null

Cause: Property not expanded in query

Solution:

// Before (related properties null)
GET /items?$select=Title,Author

// After (Author expanded)
GET /items?$select=Title,Author&$expand=Author

Testing Queries¶

Browser Developer Tools: 1. Open Network tab 2. Execute REST query 3. Check request URL and response 4. Verify query syntax

PowerShell:

$url = "http://localhost:5000/_api/web/lists/getbytitle('Tasks')/items"
$query = "?`$filter=Status eq 'Active'&`$orderby=Priority desc"
Invoke-RestMethod -Uri ($url + $query) -Method Get

cURL:

curl "http://localhost:5000/_api/web/lists/getbytitle('Tasks')/items?\$filter=Status eq 'Active'"

API Reference - Complete REST API documentation
SharePoint Compatibility - Feature parity matrix
Performance Guide - Optimization techniques
Security Guide - API authentication and authorization

Implementation: PLAN-150 Phase F - OData Advanced Features Version: 1.0 Last Updated: 2026-01-18

OData Query Guide¶

Table of Contents¶

Overview¶

$filter Query Option¶

Comparison Operators¶

Logical Operators¶

String Functions¶

Data Types¶

Filter Examples¶

$orderby Query Option¶

Syntax¶

OrderBy Examples¶

$expand Query Option¶

Syntax¶

Common Navigation Properties¶

Expand Examples¶

Combined Queries¶

Example 1: Active Tasks by Priority¶

Example 2: Recent Documents with Author¶

Example 3: Complex Filter with Expand¶

Example 4: Nested Property Filter and Sort¶

Best Practices¶

1. Use $select to Minimize Payload¶

2. Combine $filter and $top for Large Lists¶

3. Use $expand Carefully¶

4. Filter Before Sorting¶

5. Use Indexed Fields in Filters¶

Performance Considerations¶

Query Complexity¶

Expand Performance¶

Large Result Sets¶

Troubleshooting¶

Common Errors¶

1. Filter Parse Error¶

2. Property Not Found¶

3. Type Conversion Error¶

4. Expand Not Working¶

Testing Queries¶

Related Documentation¶