Exploring OData Query Options: A Comprehensive Guide with Examples (2024)

In the realm of web development, efficient data retrieval and manipulation are essential. OData, or Open Data Protocol, provides a standardized way to query and manipulate data over the web. OData introduces various query options that allow developers to filter, sort, and shape the data returned by their APIs. In this comprehensive guide, we’ll explore each OData query option in detail, along with practical examples to demonstrate their usage.

Before we delve into OData query options, let’s set up a simple .NET project to demonstrate their functionality.

Open your terminal or command prompt and run the following commands:

Bash

dotnet new webapi -n ODataExample
cd ODataExample

Install the Microsoft.AspNetCore.OData package using the following command:

Bash

dotnet add package Microsoft.AspNetCore.OData

Create Product and Category classes in the Models folder to represent our data entities.

Code Snippet

C#

// Product.cs
public class Product
{
 public int Id { get; set; }
 public string Name { get; set; }
 public decimal Price { get; set; }
 public int CategoryId { get; set; }
 public Category Category { get; set; }
}

// Category.cs
public class Category
{
 public int Id { get; set; }
 public string Name { get; set; }
}

Create ProductsController and CategoriesController to expose OData endpoints for our entities.

Code Snippet

C#

// ProductsController.cs
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.OData.Query;
using System.Collections.Generic;
using System.Linq;
namespace ODataExample.Controllers
{
 [ApiController]
 [Route("api/[controller]")]
 public class ProductsController : ControllerBase
 {
 private readonly List<Product> _products = new List<Product>
 {
 new Product { Id = 1, Name = "Product A", Price = 10.99m, CategoryId = 1 },
 new Product { Id = 2, Name = "Product B", Price = 20.49m, CategoryId = 2 },
 new Product { Id = 3, Name = "Product C", Price = 15.99m, CategoryId = 1 }
 };
 [HttpGet]
 [EnableQuery]
 public IActionResult Get()
 {
 return Ok(_products.AsQueryable());
 }
 }
 }

// CategoriesController.cs
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.OData.Query;
using System.Collections.Generic;
using System.Linq;
namespace ODataExample.Controllers
{
 [ApiController]
 [Route("api/[controller]")]
 public class CategoriesController : ControllerBase
 {
 private readonly List<Category> _categories = new List<Category>
 {
 new Category { Id = 1, Name = "Category A" },
 new Category { Id = 2, Name = "Category B" }
 };
 [HttpGet]
 [EnableQuery]
 public IActionResult Get()
 {
 return Ok(_categories.AsQueryable());
 }
 }
 }

Now that we have our project set up, let’s dive into each OData query option.

The $expand option allows you to include related entities inline in the response. This is useful for fetching related data in a single request.

Example

GET /api/Products?$expand=Category

This request will return products along with their associated categories.

The $select option allows you to select a subset of properties to include in the response. This can help reduce the payload size when fetching large datasets.

Example

GET /api/Products?$select=Name,Price

This request will return only the Name and Price properties of products.

The $filter option allows you to filter the results based on a boolean condition. You can construct filter expressions using various supported logical operators and comparison operators.

Example

GET /api/Products?$filter=Price gt 15

This request will return products with a price greater than 15.

The $orderby option allows you to sort the results based on one or more properties. You can specify ascending or descending order for each property.

Example

GET /api/Products?$orderby=Price desc

This request will return products sorted by price in descending order.

The $top option allows you to limit the number of results returned. This is useful for paging through large datasets.

Example

GET /api/Products?$top=5

This request will return the top 5 products.

The $skip option allows you to skip a certain number of results before returning the remaining items. This is useful in combination with $top for paging purposes.

Example

GET /api/Products?$skip=10

This request will skip the first 10 products and return the rest.

The $inlinecount option tells the server to include the total count of matching entities in the response. This is useful for client-side paging and implementing infinite scrolling.

Example

GET /api/Products?$inlinecount=allpages

This request will return products along with the total count of matching entities.

OData query options provide a powerful and versatile mechanism for querying and shaping data in your APIs. By understanding and utilizing these options effectively, you can create efficient and flexible data access layers in your .NET applications. OData empowers you to build robust and scalable APIs that cater to various data retrieval scenarios, including filtering, sorting, paging, and more.

Assuming you’ve run the code example provided earlier, here’s a demonstration of the output you might get for some of the OData queries:

Get all products:

JSON
[
 {
 "Id": 1,
 "Name": "Product A",
 "Price": 10.99,
 "CategoryId": 1,
 "Category": null
 },
 {
 "Id": 2,
 "Name": "Product B",
 "Price": 20.49,
 "CategoryId": 2,
 "Category": null
 },
 {
 "Id": 3,
 "Name": "Product C",
 "Price": 15.99,
 "CategoryId": 1,
 "Category": null
 }
]

Get products with price greater than 15 using $filter:

JSON
[
 {
 "Id": 2,
 "Name": "Product B",
 "Price": 20.49,
 "CategoryId": 2,
 "Category": null
 },
 {
 "Id": 3,
 "Name": "Product C",
 "Price": 15.99,
 "CategoryId": 1,
 "Category": null
 }
]

Get top 2 products using $top:

JSON
[
 {
 "Id": 1,
 "Name": "Product A",
 "Price": 10.99,
 "CategoryId": 1,
 "Category": null
 },
 {
 "Id": 2,
 "Name": "Product B",
 "Price": 20.49,
 "CategoryId": 2,
 "Category": null
 }
]

1. $expand

This request retrieves products and expands the related Category entity inline:

JSON Output

GET /api/Products?$expand=Category

[
 {
 "Id":1,
 "Name":"Product A",
 "Price":10.99,
 "CategoryId":1,
 "Category":{
 "Id":1,
 "Name":"Category A"
 }
 },
 {
 "Id":2,
 "Name":"Product B",
 "Price":20.49,
 "CategoryId":2,
 "Category":{
 "Id":2,
 "Name":"Category B"
 }
 },
 {
 "Id":3,
 "Name":"Product C",
 "Price":15.99,
 "CategoryId":1,
 "Category":{
 "Id":1,
 "Name":"Category A"
 }
 }
]

2. $select

GET /api/Products?$select=Name,Price

[
 {
 "Name":"Product A",
 "Price":10.99
 },
 {
 "Name":"Product B",
 "Price":20.49
 },
 {
 "Name":"Product C",
 "Price":15.99
 }
]

3. $filter

JSON Output

GET /api/Products?$filter=Price gt 15

[
 {
 "Id":2,
 "Name":"Product B",
 "Price":20.49,
 "CategoryId":2,
 "Category":null
 },
 {
 "Id":3,
 "Name":"Product C",
 "Price":15.99,
 "CategoryId":1,
 "Category":null
 }
]

4. $orderby

This request retrieves products sorted by price in descending order:

JSON Output

GET /api/Products?$orderby=Price desc

[
 {
 "Id":2,
 "Name":"Product B",
 "Price":20.49,
 "CategoryId":2,
 "Category":null
 },
 {
 "Id":3,
 "Name":"Product C",
 "Price":15.99,
 "CategoryId":1,
 "Category":null
 },
 {
 "Id":1,
 "Name":"Product A",
 "Price":10.99,
 "CategoryId":1,
 "Category":null
 }
]

5. $top

JSON Output

GET /api/Products?$top=2

[
 {
 "Id":1,
 "Name":"Product A",
 "Price":10.99,
 "CategoryId":1,
 "Category":null
 },
 {
 "Id":2,
 "Name":"Product B",
 "Price":20.49,
 "CategoryId":2,
 "Category":null
 }
]

6. $skip

This request skips the first 10 products and retrieves the remaining ones:

JSON Output

GET /api/Products?$skip=10

[]

7. $inlinecount

This request retrieves products along with the total count of all products:

JSON Output

GET /api/Products?$inlinecount=allpages

{
 "value":[
 {
 "Id":1,
 "Name":"Product A",
 "Price":10.99,
 "CategoryId":1,
 "Category":null
 },
 {
 "Id":2,
 "Name":"Product B",
 "Price":20.49,
 "CategoryId":2,
 "Category":null
 }
 ]
}