OData (Open Data Protocol)

OData (Open Data Protocol) is an open standard protocol for standardizing RESTful APIs and enabling unified querying and manipulation of data.

What is OData

Overview

OData is a protocol standardized by OASIS (Organization for the Advancement of Structured Information Standards) for publishing and consuming data over the web.

Key Features:

RESTful API based on HTTP protocol
Flexible data retrieval with standardized query syntax
Self-describing schema through metadata
Support for JSON and XML formats
Unified interface for CRUD operations

OData Versions

OData v2: Widely adopted initial version (now legacy)
OData v4: Current standard version (approved as OASIS standard in 2014)
OData v4.01: Latest specification (approved in 2020)

Core OData Features

1. Unified Resource Addressing

OData represents resources using URLs.

# Entire collection
GET https://api.example.com/odata/Products

# Specific entity
GET https://api.example.com/odata/Products(1)

# Navigation property
GET https://api.example.com/odata/Products(1)/Category

# Direct property value access
GET https://api.example.com/odata/Products(1)/Name/$value

2. Standardized Query Options

OData's most powerful feature is the ability to filter and query data using URL query parameters.

$filter (Filtering)

Filter data for retrieval.

# Products with price >= 100
GET /odata/Products?$filter=Price ge 100

# Products containing "Pro" in name
GET /odata/Products?$filter=contains(Name, 'Pro')

# Multiple conditions (AND/OR)
GET /odata/Products?$filter=Price lt 50 and Category/Name eq 'Electronics'

# Date filter
GET /odata/Orders?$filter=OrderDate ge 2024-01-01T00:00:00Z

Comparison Operators:

Operator	Meaning	Example
`eq`	Equal to	`Price eq 100`
`ne`	Not equal to	`Status ne 'Cancelled'`
`gt`	Greater than	`Quantity gt 10`
`ge`	Greater than or equal	`Price ge 50`
`lt`	Less than	`Stock lt 5`
`le`	Less than or equal	`Discount le 0.2`

Logical Operators:

Operator	Meaning	Example
`and`	AND condition	`Price gt 10 and Price lt 100`
`or`	OR condition	`Category eq 'Books' or Category eq 'Music'`
`not`	NOT condition	`not (Status eq 'Inactive')`

String Functions:

Function	Description	Example
`contains(field, 'value')`	Partial match	`contains(Name, 'Phone')`
`startswith(field, 'value')`	Prefix match	`startswith(Email, 'admin')`
`endswith(field, 'value')`	Suffix match	`endswith(Email, '@example.com')`
`length(field)`	String length	`length(Name) gt 10`
`tolower(field)`	Convert to lowercase	`tolower(Name) eq 'product'`
`toupper(field)`	Convert to uppercase	`toupper(Code) eq 'ABC'`

Arithmetic Functions:

# Discounted price <= 100
GET /odata/Products?$filter=(Price mul (1 sub Discount)) le 100

# Stock <= 50% of threshold
GET /odata/Products?$filter=Stock le (Threshold div 2)

Date/Time Functions:

Function	Description	Example
`year(field)`	Get year	`year(CreatedDate) eq 2024`
`month(field)`	Get month	`month(CreatedDate) eq 12`
`day(field)`	Get day	`day(CreatedDate) eq 25`
`hour(field)`	Get hour	`hour(CreatedTime) ge 9`
`minute(field)`	Get minute	`minute(CreatedTime) eq 30`
`now()`	Current time	`CreatedDate lt now()`

$select (Property Selection)

Specify which properties to retrieve (improves performance).

# Retrieve specific fields only
GET /odata/Products?$select=Id,Name,Price

# Select nested properties
GET /odata/Products?$select=Name,Category/Name

Expand navigation properties to retrieve related data in one request.

# Include category information
GET /odata/Products?$expand=Category

# Expand multiple related entities
GET /odata/Orders?$expand=Customer,OrderItems

# Nested expansion
GET /odata/Orders?$expand=OrderItems($expand=Product)

# Filter expanded entities
GET /odata/Orders?$expand=OrderItems($filter=Quantity gt 5)

# Combine expansion and selection
GET /odata/Products?$expand=Category($select=Name)&$select=Name,Price

$orderby (Sorting)

Sort results.

# Ascending order (default)
GET /odata/Products?$orderby=Price

# Descending order
GET /odata/Products?$orderby=Price desc

# Sort by multiple fields
GET /odata/Products?$orderby=Category/Name,Price desc

# Sort using functions
GET /odata/Products?$orderby=length(Name) desc

$top and $skip (Pagination)

Implement result pagination.

# Get first 10 items
GET /odata/Products?$top=10

# Skip first 10 items and get next 10
GET /odata/Products?$skip=10&$top=10

# Pagination (page 3, 20 items per page)
GET /odata/Products?$skip=40&$top=20

$count (Counting)

Get the total count of results.

# Get count only
GET /odata/Products/$count

# Get data with count
GET /odata/Products?$count=true&$top=10

# Count after filtering
GET /odata/Products/$count?$filter=Price gt 100

$search (Full-text Search)

Perform full-text search (OData v4 and later).

# Simple search
GET /odata/Products?$search=laptop

# AND search
GET /odata/Products?$search=laptop AND gaming

# OR search
GET /odata/Products?$search=laptop OR desktop

# Phrase search
GET /odata/Products?$search="high performance"

$apply (Aggregation and Grouping)

Perform data aggregation and grouping (OData v4 extension).

# Product count by category
GET /odata/Products?$apply=groupby((Category/Name), aggregate($count as Total))

# Average price by category
GET /odata/Products?$apply=groupby((Category/Name), aggregate(Price with average as AvgPrice))

# Multiple aggregations
GET /odata/Orders?$apply=groupby((Customer/Name), aggregate(TotalAmount with sum as TotalSales, $count as OrderCount))

3. Metadata and Service Document

OData services publish schema information as metadata.

Service Document

GET https://api.example.com/odata/

Response example (JSON):

{
  "@odata.context": "https://api.example.com/odata/$metadata",
  "value": [
    {
      "name": "Products",
      "kind": "EntitySet",
      "url": "Products"
    },
    {
      "name": "Categories",
      "kind": "EntitySet",
      "url": "Categories"
    },
    {
      "name": "Orders",
      "kind": "EntitySet",
      "url": "Orders"
    }
  ]
}

Metadata Document

GET https://api.example.com/odata/$metadata

Response example (XML - CSDL format):

<?xml version="1.0" encoding="UTF-8"?>
<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
  <edmx:DataServices>
    <Schema Namespace="ProductService" xmlns="http://docs.oasis-open.org/odata/ns/edm">
      
      <!-- Entity type definition -->
      <EntityType Name="Product">
        <Key>
          <PropertyRef Name="Id"/>
        </Key>
        <Property Name="Id" Type="Edm.Int32" Nullable="false"/>
        <Property Name="Name" Type="Edm.String" MaxLength="100"/>
        <Property Name="Price" Type="Edm.Decimal" Precision="18" Scale="2"/>
        <Property Name="Stock" Type="Edm.Int32"/>
        <Property Name="CategoryId" Type="Edm.Int32"/>
        <NavigationProperty Name="Category" Type="ProductService.Category"/>
      </EntityType>

      <EntityType Name="Category">
        <Key>
          <PropertyRef Name="Id"/>
        </Key>
        <Property Name="Id" Type="Edm.Int32" Nullable="false"/>
        <Property Name="Name" Type="Edm.String" MaxLength="50"/>
        <NavigationProperty Name="Products" Type="Collection(ProductService.Product)"/>
      </EntityType>

      <!-- Entity container -->
      <EntityContainer Name="Container">
        <EntitySet Name="Products" EntityType="ProductService.Product">
          <NavigationPropertyBinding Path="Category" Target="Categories"/>
        </EntitySet>
        <EntitySet Name="Categories" EntityType="ProductService.Category">
          <NavigationPropertyBinding Path="Products" Target="Products"/>
        </EntitySet>
      </EntityContainer>
      
    </Schema>
  </edmx:DataServices>
</edmx:Edmx>

4. CRUD Operations

Create - POST

POST https://api.example.com/odata/Products
Content-Type: application/json

{
  "Name": "New Product",
  "Price": 99.99,
  "Stock": 50,
  "CategoryId": 1
}

Response (201 Created):

{
  "@odata.context": "https://api.example.com/odata/$metadata#Products/$entity",
  "Id": 123,
  "Name": "New Product",
  "Price": 99.99,
  "Stock": 50,
  "CategoryId": 1
}

Read - GET

GET https://api.example.com/odata/Products(123)

Response (200 OK):

{
  "@odata.context": "https://api.example.com/odata/$metadata#Products/$entity",
  "Id": 123,
  "Name": "New Product",
  "Price": 99.99,
  "Stock": 50,
  "CategoryId": 1
}

Update - PUT / PATCH

PUT - Full update (specify all properties):

PUT https://api.example.com/odata/Products(123)
Content-Type: application/json

{
  "Name": "Updated Product",
  "Price": 89.99,
  "Stock": 45,
  "CategoryId": 1
}

PATCH - Partial update (specify only changed properties):

PATCH https://api.example.com/odata/Products(123)
Content-Type: application/json

{
  "Price": 79.99,
  "Stock": 40
}

Delete - DELETE

DELETE https://api.example.com/odata/Products(123)

Response (204 No Content)

5. Batch Requests

Execute multiple operations at once.

POST https://api.example.com/odata/$batch
Content-Type: multipart/mixed; boundary=batch_36522ad7-fc75-4b56-8c71-56071383e77b

--batch_36522ad7-fc75-4b56-8c71-56071383e77b
Content-Type: application/http
Content-Transfer-Encoding: binary

GET Products(1) HTTP/1.1
Host: api.example.com
Accept: application/json

--batch_36522ad7-fc75-4b56-8c71-56071383e77b
Content-Type: application/http
Content-Transfer-Encoding: binary

GET Products(2) HTTP/1.1
Host: api.example.com
Accept: application/json

--batch_36522ad7-fc75-4b56-8c71-56071383e77b--

OData Data Types

Primitive Types

OData Type	Description	Example
`Edm.String`	String	`"Hello"`
`Edm.Int32`	32-bit integer	`123`
`Edm.Int64`	64-bit integer	`9223372036854775807`
`Edm.Decimal`	Decimal number	`99.99`
`Edm.Double`	Double-precision float	`3.14159`
`Edm.Boolean`	Boolean	`true` / `false`
`Edm.DateTime`	Date/time (v2)	`2024-12-23T10:30:00`
`Edm.DateTimeOffset`	Date/time with timezone (v4)	`2024-12-23T10:30:00+09:00`
`Edm.Date`	Date only (v4)	`2024-12-23`
`Edm.TimeOfDay`	Time only (v4)	`10:30:00`
`Edm.Duration`	Duration	`PT12H30M`
`Edm.Guid`	GUID	`12345678-1234-1234-1234-123456789012`
`Edm.Binary`	Binary data	`T0RhdGE=` (Base64)

Complex Types

<ComplexType Name="Address">
  <Property Name="Street" Type="Edm.String"/>
  <Property Name="City" Type="Edm.String"/>
  <Property Name="PostalCode" Type="Edm.String"/>
  <Property Name="Country" Type="Edm.String"/>
</ComplexType>

<EntityType Name="Customer">
  <Key>
    <PropertyRef Name="Id"/>
  </Key>
  <Property Name="Id" Type="Edm.Int32" Nullable="false"/>
  <Property Name="Name" Type="Edm.String"/>
  <Property Name="BillingAddress" Type="ProductService.Address"/>
  <Property Name="ShippingAddress" Type="ProductService.Address"/>
</EntityType>

Collection Types

<EntityType Name="Product">
  <Property Name="Tags" Type="Collection(Edm.String)"/>
  <Property Name="Dimensions" Type="Collection(Edm.Double)"/>
</EntityType>

OData Best Practices

1. When to Use OData

✅ Suitable Scenarios

Complex Query Requirements
- Clients need dynamic filtering, sorting, and pagination
- Dashboards or reports with various search conditions
Data Exploration and Ad-hoc Queries
- Data analysts or power users need to explore data freely
- BI tool integration
Standardized Interface Required
- Common API across multiple clients (Web, Mobile, Desktop)
- Third-party integration
Microsoft Ecosystem
- Integration with SharePoint, Dynamics 365, Power Platform
- .NET applications (ASP.NET Core OData library)
Metadata-driven Applications
- Dynamically generate UI
- Auto-generate client code from schema

❌ Not Suitable Scenarios

Simple CRUD Operations Only
- OData overhead is unnecessary
- Simple REST API is sufficient
Performance is Critical
- OData query parsing and metadata overhead
- GraphQL or dedicated endpoints may be more efficient
Highly Specialized Query Logic
- Complex business logic that can't be expressed in OData standard queries
- Custom endpoints are more appropriate
Real-time Communication
- WebSocket or SignalR are better suited
- OData is HTTP-based request/response
Large Binary Data Transfer
- Use dedicated endpoints for file upload/download

2. Query Performance Optimization

Use $select to Retrieve Only Necessary Fields

# ❌ Bad: Retrieve all fields
GET /odata/Products

# ✅ Good: Only necessary fields
GET /odata/Products?$select=Id,Name,Price

Limit Page Size with $top

# ❌ Bad: Unlimited retrieval
GET /odata/Products

# ✅ Good: Limit page size
GET /odata/Products?$top=20

Avoid Deep Nested $expand

# ❌ Bad: Deeply nested expansion
GET /odata/Orders?$expand=Customer($expand=Orders($expand=OrderItems($expand=Product)))

# ✅ Good: Expand only necessary levels
GET /odata/Orders?$expand=Customer,OrderItems($expand=Product;$select=Name,Price)

Filter on Indexed Fields

# ✅ Utilize database indexes
GET /odata/Products?$filter=CategoryId eq 5 and Price gt 100

3. Security Best Practices

Limit Query Complexity

Set server-side restrictions:

Maximum $top value: e.g., 100 or 1000
Maximum $expand depth: e.g., 2 levels
$filter complexity: Maximum number of nested conditions

ASP.NET Core Implementation Example:

services.AddControllers()
    .AddOData(opt => opt
        .SetMaxTop(100)
        .Expand().Select().Filter().OrderBy().Count()
        .SetMaxExpansionDepth(2));

Implement Authentication and Authorization

// Entity-level filtering
[Authorize]
[EnableQuery]
public IQueryable<Order> GetOrders()
{
    var userId = User.FindFirstValue(ClaimTypes.NameIdentifier);
    // Return only the logged-in user's orders
    return _context.Orders.Where(o => o.UserId == userId);
}

// Detailed authorization check
[Authorize]
public async Task<IActionResult> Get([FromODataUri] int key)
{
    var order = await _context.Orders.FindAsync(key);
    if (order == null)
        return NotFound();
    
    var userId = User.FindFirstValue(ClaimTypes.NameIdentifier);
    if (order.UserId != userId && !User.IsInRole("Admin"))
        return Forbid();
    
    return Ok(order);
}

Protect Sensitive Data

// Exclude sensitive properties from metadata
public class UserDto
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    // Password hash is not exposed
}

// Or control with attributes
public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    
    [Ignore] // Exclude from OData metadata
    public string PasswordHash { get; set; }
}

SQL Injection Prevention

OData libraries typically use parameterized queries, making them safe
When implementing custom filter functions, always use parameterization

// ✅ Safe: Parameterized query
_context.Products.Where(p => p.CategoryId == categoryId);

// ❌ Dangerous: String concatenation (don't use)
_context.Products.FromSqlRaw($"SELECT * FROM Products WHERE CategoryId = {categoryId}");

// ✅ Safe: Use parameters
_context.Products.FromSqlRaw("SELECT * FROM Products WHERE CategoryId = {0}", categoryId);

4. Error Handling

OData defines a standard error format.

{
  "error": {
    "code": "InvalidQuery",
    "message": "The query specified in the URI is not valid.",
    "target": "$filter",
    "details": [
      {
        "code": "UnknownProperty",
        "message": "Property 'InvalidField' does not exist.",
        "target": "InvalidField"
      }
    ],
    "innererror": {
      "trace": "...",
      "context": "..."
    }
  }
}

Server-side Implementation Example:

public class ODataErrorHandler
{
    public IActionResult HandleODataException(ODataException ex)
    {
        var error = new ODataError
        {
            ErrorCode = "BadRequest",
            Message = ex.Message,
            Target = ex.Target,
            Details = ex.Details?.Select(d => new ODataErrorDetail
            {
                ErrorCode = d.ErrorCode,
                Message = d.Message,
                Target = d.Target
            }).ToList()
        };
        
        return new BadRequestObjectResult(new { error });
    }
}

5. Versioning Strategy

URL Path-based Versioning

GET https://api.example.com/odata/v1/Products
GET https://api.example.com/odata/v2/Products

Custom Header-based Versioning

GET https://api.example.com/odata/Products
API-Version: 2.0

Query Parameter-based Versioning

GET https://api.example.com/odata/Products?api-version=2.0

Implementation Examples

ASP.NET Core Implementation

1. Install Package

dotnet add package Microsoft.AspNetCore.OData

2. Define Entity Models

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

// Models/Category.cs
public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Product> Products { get; set; }
}

3. Configure DbContext

// Data/ApplicationDbContext.cs
public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
    }

    public DbSet<Product> Products { get; set; }
    public DbSet<Category> Categories { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>()
            .HasOne(p => p.Category)
            .WithMany(c => c.Products)
            .HasForeignKey(p => p.CategoryId);
    }
}

4. Configure OData (Program.cs)

using Microsoft.AspNetCore.OData;
using Microsoft.OData.ModelBuilder;

var builder = WebApplication.CreateBuilder(args);

// Build EDM model
var modelBuilder = new ODataConventionModelBuilder();
modelBuilder.EntitySet<Product>("Products");
modelBuilder.EntitySet<Category>("Categories");

// Register OData service
builder.Services.AddControllers()
    .AddOData(options => options
        .Select()           // Enable $select
        .Filter()           // Enable $filter
        .OrderBy()          // Enable $orderby
        .Expand()           // Enable $expand
        .Count()            // Enable $count
        .SetMaxTop(100)     // Set maximum $top value
        .AddRouteComponents("odata", modelBuilder.GetEdmModel()));

builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

var app = builder.Build();

app.UseRouting();
app.UseAuthorization();
app.MapControllers();

app.Run();

5. Implement Controllers

// Controllers/ProductsController.cs
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.OData.Query;
using Microsoft.AspNetCore.OData.Routing.Controllers;

[Route("odata/[controller]")]
public class ProductsController : ODataController
{
    private readonly ApplicationDbContext _context;

    public ProductsController(ApplicationDbContext context)
    {
        _context = context;
    }

    // GET: odata/Products
    [EnableQuery] // Enable OData queries
    public IQueryable<Product> Get()
    {
        return _context.Products;
    }

    // GET: odata/Products(5)
    [EnableQuery]
    public SingleResult<Product> Get([FromRoute] int key)
    {
        return SingleResult.Create(_context.Products.Where(p => p.Id == key));
    }

    // POST: odata/Products
    public async Task<IActionResult> Post([FromBody] Product product)
    {
        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }

        _context.Products.Add(product);
        await _context.SaveChangesAsync();

        return Created(product);
    }

    // PATCH: odata/Products(5)
    public async Task<IActionResult> Patch([FromRoute] int key, [FromBody] Delta<Product> delta)
    {
        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }

        var product = await _context.Products.FindAsync(key);
        if (product == null)
        {
            return NotFound();
        }

        delta.Patch(product);
        await _context.SaveChangesAsync();

        return Updated(product);
    }

    // DELETE: odata/Products(5)
    public async Task<IActionResult> Delete([FromRoute] int key)
    {
        var product = await _context.Products.FindAsync(key);
        if (product == null)
        {
            return NotFound();
        }

        _context.Products.Remove(product);
        await _context.SaveChangesAsync();

        return NoContent();
    }
}

6. Implement Custom Query Options

// Custom filters
public class CustomProductsController : ODataController
{
    private readonly ApplicationDbContext _context;

    public CustomProductsController(ApplicationDbContext context)
    {
        _context = context;
    }

    [EnableQuery]
    [HttpGet("odata/Products/InStock")]
    public IQueryable<Product> GetInStockProducts()
    {
        // Return only items in stock
        return _context.Products.Where(p => p.Stock > 0);
    }

    [EnableQuery]
    [HttpGet("odata/Products/LowStock")]
    public IQueryable<Product> GetLowStockProducts([FromQuery] int threshold = 10)
    {
        // Return low stock items
        return _context.Products.Where(p => p.Stock <= threshold);
    }
}

Client-side Implementation (TypeScript)

// OData client class
class ODataClient {
  private baseUrl: string;

  constructor(baseUrl: string) {
    this.baseUrl = baseUrl;
  }

  // Query builder
  async query<T>(
    entitySet: string,
    options?: {
      filter?: string;
      select?: string[];
      expand?: string[];
      orderby?: string;
      top?: number;
      skip?: number;
      count?: boolean;
    }
  ): Promise<{ value: T[]; '@odata.count'?: number }> {
    const params = new URLSearchParams();

    if (options?.filter) params.append('$filter', options.filter);
    if (options?.select) params.append('$select', options.select.join(','));
    if (options?.expand) params.append('$expand', options.expand.join(','));
    if (options?.orderby) params.append('$orderby', options.orderby);
    if (options?.top) params.append('$top', options.top.toString());
    if (options?.skip) params.append('$skip', options.skip.toString());
    if (options?.count) params.append('$count', 'true');

    const url = `${this.baseUrl}/${entitySet}?${params.toString()}`;
    const response = await fetch(url);

    if (!response.ok) {
      throw new Error(`OData query failed: ${response.statusText}`);
    }

    return await response.json();
  }

  // Get single entity
  async getById<T>(entitySet: string, id: number | string): Promise<T> {
    const url = `${this.baseUrl}/${entitySet}(${id})`;
    const response = await fetch(url);

    if (!response.ok) {
      throw new Error(`OData get failed: ${response.statusText}`);
    }

    return await response.json();
  }

  // Create
  async create<T>(entitySet: string, entity: Partial<T>): Promise<T> {
    const url = `${this.baseUrl}/${entitySet}`;
    const response = await fetch(url, {
      method: 'POST',
      headers: {
        'Content-Type': 'application/json',
      },
      body: JSON.stringify(entity),
    });

    if (!response.ok) {
      throw new Error(`OData create failed: ${response.statusText}`);
    }

    return await response.json();
  }

  // Update
  async update<T>(
    entitySet: string,
    id: number | string,
    entity: Partial<T>
  ): Promise<void> {
    const url = `${this.baseUrl}/${entitySet}(${id})`;
    const response = await fetch(url, {
      method: 'PATCH',
      headers: {
        'Content-Type': 'application/json',
      },
      body: JSON.stringify(entity),
    });

    if (!response.ok) {
      throw new Error(`OData update failed: ${response.statusText}`);
    }
  }

  // Delete
  async delete(entitySet: string, id: number | string): Promise<void> {
    const url = `${this.baseUrl}/${entitySet}(${id})`;
    const response = await fetch(url, {
      method: 'DELETE',
    });

    if (!response.ok) {
      throw new Error(`OData delete failed: ${response.statusText}`);
    }
  }
}

// Usage example
interface Product {
  Id: number;
  Name: string;
  Price: number;
  Stock: number;
  CategoryId: number;
  Category?: Category;
}

interface Category {
  Id: number;
  Name: string;
  Products?: Product[];
}

const client = new ODataClient('https://api.example.com/odata');

// Execute complex query
async function fetchProducts() {
  const result = await client.query<Product>('Products', {
    filter: "Price gt 50 and contains(Name, 'Pro')",
    select: ['Id', 'Name', 'Price'],
    expand: ['Category($select=Name)'],
    orderby: 'Price desc',
    top: 20,
    skip: 0,
    count: true,
  });

  console.log(`Total count: ${result['@odata.count']}`);
  console.log('Products:', result.value);
}

// Pagination
async function fetchProductsPage(page: number, pageSize: number = 20) {
  const skip = (page - 1) * pageSize;

  const result = await client.query<Product>('Products', {
    orderby: 'Id',
    top: pageSize,
    skip: skip,
    count: true,
  });

  return {
    items: result.value,
    total: result['@odata.count'],
    page: page,
    pageSize: pageSize,
    totalPages: Math.ceil((result['@odata.count'] || 0) / pageSize),
  };
}

// CRUD operations
async function crudOperations() {
  // Create
  const newProduct = await client.create<Product>('Products', {
    Name: 'New Product',
    Price: 99.99,
    Stock: 50,
    CategoryId: 1,
  });
  console.log('Created:', newProduct);

  // Read
  const product = await client.getById<Product>('Products', newProduct.Id);
  console.log('Retrieved:', product);

  // Update
  await client.update<Product>('Products', newProduct.Id, {
    Price: 89.99,
    Stock: 45,
  });
  console.log('Updated');

  // Delete
  await client.delete('Products', newProduct.Id);
  console.log('Deleted');
}

OData vs Other API Styles

OData vs REST

Aspect	OData	Standard REST
Standardization	Strictly standardized	Flexible, convention-based
Query Capabilities	Powerful built-in queries	Custom implementation needed
Metadata	Automatically provided	Usually not provided
Learning Curve	Somewhat complex	Simple
Flexibility	Must follow standards	High degree of freedom
Use Cases	Complex queries, enterprise apps	General web apps

OData vs GraphQL

Aspect	OData	GraphQL
Query Language	URL query parameters	Dedicated query language
Type System	EDM (Entity Data Model)	GraphQL schema
Over/Under-fetching	Control with $select/$expand	Specify exactly in query
Mutation Operations	Standard HTTP verbs	Mutation
Real-time	None	Subscription
Tool Ecosystem	Mature (Microsoft-focused)	Very rich

Security Considerations

1. Authentication and Authorization

// JWT Bearer token authentication
builder.Services.AddAuthentication(JwtBearerDefaults.AuthenticationScheme)
    .AddJwtBearer(options =>
    {
        options.TokenValidationParameters = new TokenValidationParameters
        {
            ValidateIssuer = true,
            ValidateAudience = true,
            ValidateLifetime = true,
            ValidateIssuerSigningKey = true,
            ValidIssuer = builder.Configuration["Jwt:Issuer"],
            ValidAudience = builder.Configuration["Jwt:Audience"],
            IssuerSigningKey = new SymmetricSecurityKey(
                Encoding.UTF8.GetBytes(builder.Configuration["Jwt:Key"]))
        };
    });

// Entity-level authorization
[Authorize(Roles = "Admin,User")]
[EnableQuery]
public IQueryable<Product> Get()
{
    return _context.Products;
}

2. Rate Limiting

// ASP.NET Core rate limiting middleware
builder.Services.AddRateLimiter(options =>
{
    options.GlobalLimiter = PartitionedRateLimiter.Create<HttpContext, string>(httpContext =>
        RateLimitPartition.GetFixedWindowLimiter(
            partitionKey: httpContext.User.Identity?.Name ?? httpContext.Request.Headers.Host.ToString(),
            factory: partition => new FixedWindowRateLimiterOptions
            {
                AutoReplenishment = true,
                PermitLimit = 100,
                QueueLimit = 0,
                Window = TimeSpan.FromMinutes(1)
            }));
});

app.UseRateLimiter();

3. CORS Configuration

builder.Services.AddCors(options =>
{
    options.AddPolicy("AllowSpecificOrigin",
        builder => builder
            .WithOrigins("https://yourdomain.com")
            .AllowAnyMethod()
            .AllowAnyHeader()
            .AllowCredentials());
});

app.UseCors("AllowSpecificOrigin");

4. Input Validation

public class ProductValidator : AbstractValidator<Product>
{
    public ProductValidator()
    {
        RuleFor(p => p.Name)
            .NotEmpty().WithMessage("Product name is required")
            .MaximumLength(100).WithMessage("Name must not exceed 100 characters");

        RuleFor(p => p.Price)
            .GreaterThan(0).WithMessage("Price must be greater than 0");

        RuleFor(p => p.Stock)
            .GreaterThanOrEqualTo(0).WithMessage("Stock cannot be negative");
    }
}

// Use in controller
public async Task<IActionResult> Post([FromBody] Product product)
{
    var validator = new ProductValidator();
    var validationResult = await validator.ValidateAsync(product);

    if (!validationResult.IsValid)
    {
        return BadRequest(validationResult.Errors);
    }

    _context.Products.Add(product);
    await _context.SaveChangesAsync();

    return Created(product);
}

Summary

Key Benefits of OData

✅ Standardization: Consistent API design
✅ Powerful Queries: Flexible filtering, sorting, pagination
✅ Metadata: Self-describing schema
✅ Tool Support: Rich libraries and tools
✅ Productivity: Reduces boilerplate code

Challenges of OData

❌ Complexity: Steeper learning curve
❌ Overhead: Metadata and query processing costs
❌ Flexibility Constraints: Must follow standards
❌ Performance: Issues from improper queries

Recommended Use Scenarios

Enterprise Applications: Complex data queries and reports
Data Exploration Tools: Dashboards, BI integration
Microsoft Ecosystem: SharePoint, Dynamics, Power Platform
API Standardization: Consistent API design across organizations
Auto Code Generation: Client generation from metadata

What is OData​

Overview​

OData Versions​

Core OData Features​

1. Unified Resource Addressing​

2. Standardized Query Options​

$filter (Filtering)​

$select (Property Selection)​

$expand (Related Entity Expansion)​

$orderby (Sorting)​

$top and $skip (Pagination)​

$count (Counting)​

$search (Full-text Search)​

$apply (Aggregation and Grouping)​

3. Metadata and Service Document​

Service Document​

Metadata Document​

4. CRUD Operations​

Create - POST​

Read - GET​

Update - PUT / PATCH​

Delete - DELETE​

5. Batch Requests​

OData Data Types​

Primitive Types​

Complex Types​

Collection Types​

OData Best Practices​

1. When to Use OData​

✅ Suitable Scenarios​

❌ Not Suitable Scenarios​

2. Query Performance Optimization​

Use $select to Retrieve Only Necessary Fields​

Limit Page Size with $top​

Avoid Deep Nested $expand​

Filter on Indexed Fields​

3. Security Best Practices​

Limit Query Complexity​

Implement Authentication and Authorization​

Protect Sensitive Data​

SQL Injection Prevention​

4. Error Handling​

5. Versioning Strategy​

URL Path-based Versioning​

Custom Header-based Versioning​

Query Parameter-based Versioning​

Implementation Examples​

ASP.NET Core Implementation​

1. Install Package​

2. Define Entity Models​

3. Configure DbContext​

4. Configure OData (Program.cs)​

5. Implement Controllers​

6. Implement Custom Query Options​

Client-side Implementation (TypeScript)​

OData vs Other API Styles​

OData vs REST​

OData vs GraphQL​

Security Considerations​

1. Authentication and Authorization​

2. Rate Limiting​

3. CORS Configuration​

4. Input Validation​

Summary​

Key Benefits of OData​

Challenges of OData​

Recommended Use Scenarios​

Reference Links​