Implementing OData in ASP.NET Core Microservices

Back to: Microservices using ASP.NET Core Web API Tutorials

OData in ASP.NET Core Web API Microservices

In this post, we will discuss How to Implement OData in ASP.NET Core Web API. In modern microservice-based systems, APIs are consumed by highly dynamic user interfaces such as Admin Dashboards, Catalog Grids, Reporting Screens, and Analytics Views. These UIs require flexible data access patterns such as filtering, sorting, paging, column selection, and aggregation, often driven by user interactions rather than predefined API contracts.

Traditionally, supporting such requirements leads to Endpoint Explosion: multiple APIs for each filter combination, sort order, or screen variation. OData (Open Data Protocol) solves this problem by allowing clients to shape the data they need through standardized query options, while the server remains in control of Validation, Performance, and Security.

Real-World Scenario: Admin Product Catalog Grid

Now, we will implement OData to support an Admin Product Catalog Grid, similar to what we see in real production systems such as e-commerce admin panels. This grid typically supports:

• Searching and Filtering Products (Active, In-Stock, Category-wise)
• Sorting by Price, Creation Date, Stock, or Rating
• Paging Large Datasets Efficiently
• Selecting only the Columns Required for the UI
• Expanding related data, such as product images
• Showing total record counts for pagination controls

Instead of building separate APIs for each of these use cases, we will expose a single OData endpoint that supports them all via URL-based query options. Let us proceed and implement this step by step in our Product Microservice.

Step 1: Install the OData NuGet package (ProductService.API)

ASP.NET Core does not include OData support by default. The Microsoft.AspNetCore.OData package provides:

• OData Routing Engine: Enables the API to map OData-style URLs (such as $filter, $select, and $orderby) to the appropriate controller actions.
• Query Parsing and Validation: It reads OData query options such as $filter, $select, $orderby, $top, etc., and validates them to prevent invalid or unsafe queries.
• EDM (Entity Data Model) Support: It builds an EDM metadata model that describes our API entities and relationships so OData knows what can be queried and how.
• Translation of OData Queries into LINQ Expressions: It converts OData query options into LINQ, so our database query (via EF Core or IQueryable) is executed efficiently based on the request.

By installing this package in the API Layer, we ensure that OData remains an API Concern, without leaking intothe  domain or infrastructure layers.

Visual Studio steps

• Right-click ProductService.API ➜ Manage NuGet Packages
• Browse ➜ search Microsoft.AspNetCore.OData
• Install it

Package Manager Console

Install-Package Microsoft.AspNetCore.OData

Step 2: Exposing IQueryable at the Repository Level

When we work with a normal API, we usually write fixed endpoints like:

• Get All Products
• Get Active Products
• Get Products by Category
• Get Top Selling Products

But in real applications (especially an Admin Product Grid), the UI keeps changing the query depending on what the admin does:

• Search Box → Filter by name/SKU
• Dropdown → Filter by category
• Column Click → Sort by price or created date
• Pagination → Skip and take records

If we create separate APIs for every possible combination, we end up creating Too Many Endpoints. OData solves this by allowing the client to send the query in the URL, like:

• ?$filter=IsActive eq true
• ?$orderby=CreatedOn desc
• ?$top=25&$skip=50

Why is IQueryable required?

OData works like this:

1. The Repository returns a Query Plan (not the actual data); it uses deferred execution.
2. OData reads the URL and adds extra conditions to that query plan.
3. EF Core converts the final query plan into a single SQL query and executes it in the database.

That query plan is exactly what IQueryable<T> represents.

What if we return List<T> instead?

If we return a List<Product> (already loaded), then:

• OData can still filter/sort/page,
• But it will do it in memory after loading all rows.

That means:

• Huge database reads
• More RAM usage
• Slow response
• Not scalable for large tables

For OData, always return an IQueryable from the repository.

Why the Repository Method Should Not Use Include()

Include means it will eagerly load related entities when the primary entity is loaded in a single query via a JOIN. For example, whenever someone requests a product, also fetch all related tables (images, reviews, discounts, etc.). But most screens don’t need everything. If we always include:

• ProductImages
• Reviews
• Discounts

Then:

• SQL Becomes Heavy (Big JOINs)
• Paging Becomes Slow
• Network Payload Becomes Large
• Performance Down

So, the repository should return a Clean Base Query with IQueryable, and higher layers decide what extra data is needed.

Modifying the IProductRepository Interface

Open, ProductService.Domain/Repositories/IProductRepository.cs class file, and then add the following method signature. Here, the method returns IQueryable<Product>. Returning a simple IQueryable<Product> gives OData full control to build efficient queries while keeping the repository fast, clean, and scalable.

IQueryable<Product> Query();

Implement in Infrastructure Repository

Open, ProductService.Infrastructure/Repositories/ProductRepository.cs class file, and then add the following method implementation.

public IQueryable<Product> Query()
{
    // IMPORTANT:
    // Return a base IQueryable without Include().
    return _dbContext.Products.AsNoTracking();
}

 

Code Explanations:

Instead of returning ready-made data, the repository exposes an IQueryable<Product> so OData can dynamically add filtering, sorting, paging, and selection before the database query is executed. This keeps the database work efficient and avoids loading unnecessary data into memory.

• IQueryable represents a query definition, not the data it returns.
• OData composes its query on top of this base query.
• SQL is generated only at the final execution stage.

Why the Implementation Uses AsNoTracking() and No Include()?

The repository returns a clean base query using AsNoTracking() to optimize read-only access and avoids Include() to prevent heavy joins and over-fetching. This design keeps the repository generic and lets higher layers decide what data shape is required.

• AsNoTracking() improves read performance
• No Include() means no unnecessary joins
• Repository stays focused on data access, not UI needs

Step 3: Enhancing ProductDTO with Navigation Properties

In OData-based APIs, the DTO plays a crucial role because it defines the exact shape of data the API exposes, not just the main entity but also its related data. OData relies on the DTO to determine which properties and relationships are queryable or expandable using options such as $select and $expand.

Please update the ProductService.Application/DTOs/ProductDTO as follows. Adding navigation properties to ProductDTO defines which related data OData clients can query or expand, while maintaining control over performance and API contracts. Here, we have added the Images navigation property.

namespace ProductService.Application.DTOs
{
    public class ProductDTO
    {
        public Guid Id { get; set; }
        public string Name { get; set; } = null!;
        public string SKU { get; set; } = null!;
        public string? Description { get; set; }
        public decimal Price { get; set; }
        public int StockQuantity { get; set; }
        public bool IsActive { get; set; }
        public Guid CategoryId { get; set; }
        public DateTime CreatedOn { get; set; }
        public DateTime? ModifiedOn { get; set; }
        public decimal DiscountedPrice { get; set; }
        public string? PrimaryImageUrl { get; set; }
        public string? PrimaryImageAltText { get; set; }
        public decimal AverageRating { get; set; }
        public int TotalReviews { get; set; }
        public ICollection<ProductImageDTO> Images { get; set; } = new List<ProductImageDTO>();
    }
}

 

Code Explanations:

By adding the Images navigation property to ProductDTO, we explicitly tell OData that product images are a valid relationship that clients can request as needed. This enables scenarios such as admin grids, catalog pages, or product detail screens to fetch related images in the same request without exposing internal domain entities.

• DTOs define the public contract of the API, not the database structure
• Navigation properties control what can be $expand-ed in OData
• Only approved related data (like Images) is exposed to clients

Why Navigation Properties Belong in the DTO?

Adding navigation properties to the DTO does not mean data is always loaded. It only makes the relationship queryable on demand, allowing the server to maintain control over performance. The actual data loading still depends on how the query is projected and whether the client explicitly asks for it.

• Relationships are exposed safely, not loaded eagerly
• Clients request related data only when required
• Keeps domain models hidden and API responses consistent

Step 4: Creating an OData-Specific Query Layer

Before this step, our repository is only responsible for returning the base query, which will return raw product data from the database as IQueryable<Product>. Now the question is: Where should we convert that raw data into something the UI actually needs?

The answer is NOT:

• Controller
• Repository
• Existing Business Service (used for Create / Update / Delete)

Instead, we create a separate OData-specific query layer.

What does this OData query layer actually do?

Think of this layer as a Translator:

• Input: IQueryable<Product> (Raw Database Entities)
• Output: IQueryable<ProductDTO> (Clean API Contract)

While translating, it:

• Calculates discounts
• Calculates Ratings
• Selects Primary Image
• Shapes Child Collections (images)
• Avoids Loading Unnecessary Data

All of this happens before SQL execution.

ProductQueryExtensions.cs

First, create a folder named OData at the root directory of ProductService.Application Layer Project. Then, add a class file named ProductQueryExtensions.cs within the OData folder, and copy-paste the following code.

using ProductService.Application.DTOs;
using ProductService.Domain.Entities;

namespace ProductService.Application.OData
{
    public static class ProductQueryExtensions
    {
        public static IQueryable<ProductDTO> ProjectToProductDtoForOData(this IQueryable<Product> query)
        {
            // We capture the current UTC time once so EF Core treats it like a parameter value
            var now = DateTime.UtcNow;

            // IMPORTANT:
            // We do NOT use Include() here.
            // Instead, we shape a DTO using SQL-friendly subqueries.
            //
            // Benefits:
            // - Only fetches the data we need (projection), not entire entity graphs.
            // - Avoids huge JOINs and duplicate rows caused by multiple Includes.
            // - Keeps paging/filtering/sorting efficient (very important for OData grids).
            // - Produces a clean DTO that OData can apply query options against.
            return query
                .Select(p => new
                {
                    // Keep the root product for basic scalar fields.
                    Product = p,

                    // PRIMARY IMAGE:
                    // Fetch only the primary image (thumbnail) for grid/listing UI.
                    // This uses a subquery that EF translates into SQL efficiently.
                    PrimaryImage = p.ProductImages
                        .Where(pi => pi.IsPrimary)
                        .OrderByDescending(pi => pi.CreatedOn) // in case multiple primaries exist, pick latest
                        .Select(pi => new { pi.ImageUrl, pi.AltText })
                        .FirstOrDefault(),

                    // ACTIVE DISCOUNT:
                    // Find the most recent active discount within the time window (StartDate..EndDate).
                    // We project only the fields required to compute DiscountedPrice.
                    ActiveDiscount = p.Discounts
                        .Where(d => d.IsActive && d.StartDate <= now && d.EndDate >= now)
                        .OrderByDescending(d => d.CreatedOn)
                        .Select(d => new { d.DiscountPercentage, d.DiscountAmount })
                        .FirstOrDefault(),

                    // REVIEW SUMMARY:
                    // We compute count and average as scalars so the DB does the heavy lifting.
                    // This avoids loading all reviews into memory.
                    TotalReviews = p.Reviews.Count(),

                    AvgRating = p.Reviews.Any()
                        ? p.Reviews.Average(r => (decimal)r.Rating)
                        : 0m,

                    // IMAGE GALLERY (navigation-style):
                    // This prepares a child collection for UI pages that need an image gallery.
                    // NOTE: If you plan to use real OData $expand on entities, this part may be optional.
                    // Here, we intentionally project to DTOs so the response can include images.
                    Images = p.ProductImages
                        .OrderByDescending(pi => pi.IsPrimary)     // primary image first
                        .ThenByDescending(pi => pi.CreatedOn)      // newest first within each group
                        .Select(pi => new ProductImageDTO
                        {
                            Id = pi.Id,
                            ProductId = pi.ProductId,
                            ImageUrl = pi.ImageUrl,
                            AltText = pi.AltText,
                            IsPrimary = pi.IsPrimary,
                            CreatedOn = pi.CreatedOn
                        })
                        .ToList()
                })
                .Select(x => new ProductDTO
                {
                    // Basic product fields (scalars).
                    Id = x.Product.Id,
                    Name = x.Product.Name,
                    SKU = x.Product.SKU,
                    Description = x.Product.Description,
                    Price = x.Product.Price,
                    StockQuantity = x.Product.StockQuantity,
                    IsActive = x.Product.IsActive,
                    CategoryId = x.Product.CategoryId,
                    CreatedOn = x.Product.CreatedOn,
                    ModifiedOn = x.Product.ModifiedOn,

                    // Flattened thumbnail fields for list/grid views.
                    PrimaryImageUrl = x.PrimaryImage != null ? x.PrimaryImage.ImageUrl : null,
                    PrimaryImageAltText = x.PrimaryImage != null ? x.PrimaryImage.AltText : null,

                    // Review summary fields for UI badges and sorting.
                    TotalReviews = x.TotalReviews,

                    // Round rating for a stable UI display.
                    // (If a provider ever complains about translating Math.Round, we can round post-query.)
                    AverageRating = Math.Round(x.AvgRating, 2),

                    // DISCOUNTED PRICE:
                    // Compute effective price based on the active discount.
                    // - Percentage discount takes precedence when provided and > 0
                    // - Otherwise, amount discount applies when provided and > 0
                    // - Final protection ensures price never goes below 0
                    DiscountedPrice =
                        x.ActiveDiscount == null
                            ? x.Product.Price
                            : (x.ActiveDiscount.DiscountPercentage.HasValue && x.ActiveDiscount.DiscountPercentage.Value > 0)
                                ? Math.Round(
                                    (x.Product.Price - (x.Product.Price * x.ActiveDiscount.DiscountPercentage.Value / 100m)) < 0
                                        ? 0m
                                        : (x.Product.Price - (x.Product.Price * x.ActiveDiscount.DiscountPercentage.Value / 100m)),
                                    2)
                                : (x.ActiveDiscount.DiscountAmount.HasValue && x.ActiveDiscount.DiscountAmount.Value > 0)
                                    ? Math.Round(
                                        (x.Product.Price - x.ActiveDiscount.DiscountAmount.Value) < 0
                                            ? 0m
                                            : (x.Product.Price - x.ActiveDiscount.DiscountAmount.Value),
                                        2)
                                    : x.Product.Price,

                    // Attach the image collection to support gallery views in the same payload.
                    Images = x.Images
                });
        }
    }
}

 

Why We Use a Projection Instead of Include()?

First, you need to remember that:

• Include() Loads Data
• Select() Shapes Data

If we use Include():

• EF Core Loads Full Tables
• SQL Becomes Large and Slow
• Paging Can Break
• Memory Usage Increases

If we use Select():

• Only required columns are fetched
• SQL stays efficient
• Calculations happen in the database
• Paging and filtering remain fast

That is why this layer never uses Include().

Create IProductODataQueryService Interface

OData queries are dynamic and read-only, so they should not be mixed with normal CRUD business logic or repositories. This interface clearly defines what the OData layer needs: a query for all products and a query for a single product, both returned as IQueryable, so OData can apply filtering, sorting, paging, and selection at the database level.

• Keeps OData logic separate from business services
• Exposes onlythe  query operations needed by OData
• IQueryable lets OData build SQL efficiently

Create an interface named IProductODataQueryService.cs within the ProductService.Application/OData folder, and then copy-paste the following code.

using ProductService.Application.DTOs;
namespace ProductService.Application.OData
{
    public interface IProductODataQueryService
    {
        // Returns an IQueryable so the OData pipeline can apply query options
        // ($filter, $orderby, $select, $expand, $top, $skip, $count) efficiently at the database level.
        IQueryable<ProductDTO> Query();

        // Returns a key-filtered IQueryable (single product) while still allowing OData
        IQueryable<ProductDTO> QueryById(Guid id);
    }
}

Create ProductODataQueryService Implementation

This service takes a clean base query from the repository and shapes it into ProductDTO using projection, ensuring only allowed fields are exposed. It prepares the data so OData can safely apply query options without loading unnecessary data.

• The repository gives raw data; this service shapes it
• Projection avoids over-fetching and heavy joins
• The same logic is reused for list and single-item queries

Create a class file named ProductODataQueryService.cs within the ProductService.Application/OData folder, and then copy-paste the following code.

using ProductService.Application.DTOs;
using ProductService.Domain.Repositories;
namespace ProductService.Application.OData
{
    public class ProductODataQueryService : IProductODataQueryService
    {
        private readonly IProductRepository _productRepository;

        public ProductODataQueryService(IProductRepository productRepository)
        {
            _productRepository = productRepository;
        }

        public IQueryable<ProductDTO> Query()
        {
            // Start with a simple base query from the repository (no Include).
            // We return IQueryable so the OData middleware can append query options
            // and translate the final composed query into SQL.
            return _productRepository
                .Query()
                // Project entities into a lightweight DTO using SQL-friendly subqueries.
                // This prevents over-fetching and avoids loading large navigation graphs.
                .ProjectToProductDtoForOData();
        }

        public IQueryable<ProductDTO> QueryById(Guid id)
        {
            // Key-based query for Product details.
            return _productRepository
                .Query()
                // Apply the key predicate first so the database filters early.
                .Where(p => p.Id == id)
                // Reuse the same projection to ensure consistent output shape.
                .ProjectToProductDtoForOData();
        }
    }
}

Step 5: Registering the OData Query Service

Registering the OData query service in DI allows:

• Loose coupling between controllers and data access
• Easier unit testing
• Clear separation of responsibilities

Add the following service registrations within the ProductService.API/Program.cs class file:

builder.Services.AddScoped<IProductODataQueryService, ProductODataQueryService>();

Step 6: Building the EDM (Entity Data Model)

In a normal ASP.NET Core Web API, routing is very simple:

• The Controller Name decides the URL.
• The Action Method decides what data is returned.

But OData does not work like a normal API. In OData, the framework does not first look at controller routes. Instead, it looks at something called the EDM (Entity Data Model).

What is EDM in Simple Words?

You can think of the EDM as a blueprint or map that tells OData:

• What data exists?
• What is allowed to be queried?
• How should the URLs behave?

Why OData Needs EDM

Unlike traditional APIs:

• OData URLs are dynamic.
• Clients can decide the filtering, sorting, paging, and selection.
• URLs like $filter=Price gt 500 must be Validated Automatically.

To do this safely, OData must know the data structure and the queries it supports in advance. That structure is defined using EDM.

What Does the EDM Define?

The EDM clearly tells OData:

• Which entities are exposed (for example: Products)?
• What is the primary key of each entity?
• What properties exist on the entity?
• Which relationships (navigation properties) exist?
• What can be queried, filtered, sorted, or expanded?

First, create a folder named OData at the root directory of ProductService.API layer project. Then, add a class file named ProductEdmModelBuilder.cs within the OData folder, and copy-paste the following code.

using Microsoft.OData.Edm;
using Microsoft.OData.ModelBuilder;
using ProductService.Application.DTOs;

namespace ProductService.API.OData
{
    public static class ProductEdmModelBuilder
    {
        public static IEdmModel GetEdmModel()
        {
            // Build the OData EDM (Entity Data Model).
            // This model is the "schema contract" for OData:
            // - /odata/$metadata generation
            // - which entity sets are exposed (e.g., /odata/Products)
            // - how OData understands keys, properties, and queryability
            var builder = new ODataConventionModelBuilder();

            // Register an EntitySet named "Products".
            // This is the collection endpoint clients will query:
            // GET /odata/Products?$filter=...&$orderby=...&$top=...&$skip=...
            builder.EntitySet<ProductDTO>("Products");

            // Explicitly define the primary key for ProductDTO.
            // OData needs a key to support:
            // - single-entity access: /odata/Products(<key>)
            // - stable identity in responses
            // - correct EDM metadata and routing behavior
            builder.EntityType<ProductDTO>().HasKey(x => x.Id);

            // Finalize and return the EDM model that ASP.NET Core OData uses at startup.
            return builder.GetEdmModel();
        }
    }
}

Step 7: Registering OData Middleware and Route Components

OData introduces its own routing model that works alongside ASP.NET Core MVC. By registering route components:

• /odata becomes the OData root
• Query options are parsed before hitting controllers
• Validation rules are enforced consistently

This ensures predictable and secure query execution across environments. Please modify the AddControllers() service registration as follows in ProductService.API/Program.cs class file.

// Add services to the container.
builder.Services.AddControllers()
.AddJsonOptions(options =>
{
    options.JsonSerializerOptions.PropertyNamingPolicy = null;
    options.JsonSerializerOptions.Converters.Add(new JsonStringEnumConverter());
})
// Enable OData on top of ASP.NET Core MVC.
.AddOData(opt =>
{
    // Register the OData route prefix and EDM model.
    // This exposes endpoints like:
    // - GET /odata/$metadata
    // - GET /odata/Products
    opt.AddRouteComponents("odata", ProductEdmModelBuilder.GetEdmModel())

       // Enable $select to allow clients to request only required columns.
       // Example: /odata/Products?$select=Id,Name,Price
       .Select()

       // Enable $filter for server-side filtering (translated to SQL).
       // Example: /odata/Products?$filter=IsActive eq true and Price gt 1000
       .Filter()

       // Enable $orderby for server-side sorting.
       // Example: /odata/Products?$orderby=CreatedOn desc
       .OrderBy()

       // Enable $count for pagination UI (total rows matching the filter).
       // Example: /odata/Products?$count=true&$top=25&$skip=0
       .Count()

       // Enable $expand to fetch related entities in a single request.
       // Example: /odata/Products?$expand=Images
       .Expand()

       // Safety limit: prevents clients from requesting huge page sizes.
       // Example: $top=5000 will be rejected/limited depending on settings.
       // Helps protect the DB and keeps response times predictable.
       .SetMaxTop(100);
});

Also, add the following two namespaces.

• using Microsoft.AspNetCore.OData;
• using ProductService.API.OData;

Step 8: Creating a Dedicated Production OData Controller

This controller is designed specifically for read-only, UI-driven queries such as admin grids and catalog listings. Instead of hardcoded filters or multiple endpoints, it accepts flexible query instructions directly from the URL and applies them safely on the server.

• Accepts ODataQueryOptions<T> from the request URL
• Validates query complexity to protect the database
• Rejects malformed or abusive queries early

Please create an empty controller named ProductsODataController within the ProductService.API/Controllers folder, and then copy-paste the following code. The OData controller safely builds a query from the URL and executes it only at response time, ensuring efficient, database-level filtering and paging.

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.OData.Formatter;
using Microsoft.AspNetCore.OData.Query;
using Microsoft.AspNetCore.OData.Query.Validator;
using Microsoft.AspNetCore.OData.Routing.Controllers;
using Microsoft.OData;
using ProductService.Application.DTOs;
using ProductService.Application.OData;

namespace ProductService.API.Controllers
{
    // This is an OData Controller (read-only).
    // It is mainly used for UI screens like:
    // - Admin Product Grid
    // - Catalog listing pages
    //
    // Why OData here?
    // Because the UI can send queries in URL like:
    // $filter, $orderby, $top, $skip, $select, $count, $expand etc.
    //
    // Base route:
    // - GET /odata/Products
    // - GET /odata/Products(<guid>)
    [Route("odata/Products")]
    public class ProductsODataController : ODataController
    {
        // This service returns IQueryable<ProductDTO>.
        // IQueryable is required so OData can add filters/sorts/paging before SQL runs.
        private readonly IProductODataQueryService _queryService;

        // Logger for storing useful warnings when someone sends invalid OData queries.
        private readonly ILogger<ProductsODataController> _logger;

        public ProductsODataController(
            IProductODataQueryService queryService,
            ILogger<ProductsODataController> logger)
        {
            _queryService = queryService;
            _logger = logger;
        }

        // Real-time scenario: Admin Product Catalog Grid
        // Example URL:
        // /odata/Products?$filter=IsActive eq true&$orderby=CreatedOn desc&$top=25&$skip=0&$count=true
        //
        // What happens here?
        // 1) We read OData query options from URL (options)
        // 2) Validate them (for safety)
        // 3) Apply them to IQueryable
        // 4) EF Core converts final query to SQL and fetches data
        [HttpGet]
        public IActionResult Get(ODataQueryOptions<ProductDTO> options)
        {
            try
            {
                // Step A: Add safety limits (Guardrails)
                // Why?
                // OData is powerful. If we don’t limit it, a client can request huge data,
                // causing slow SQL queries and heavy load on the database.
                var validation = new ODataValidationSettings
                {
                    // MaxTop means client cannot ask more than 100 records using $top.
                    MaxTop = 100
                };

                // Step B: Validate the incoming OData query
                // If the query is wrong or too expensive, this throws an ODataException.
                options.Validate(validation);

                // Step C: Get base query from Application layer
                // IMPORTANT: This is still IQueryable (SQL is not executed yet).
                var baseQuery = _queryService.Query();

                // Step D: Configure how OData should build expressions
                // HandleNullPropagation = False usually generates cleaner SQL for EF Core.
                var settings = new ODataQuerySettings
                {
                    HandleNullPropagation = HandleNullPropagationOption.False
                };

                // Step E: Apply OData query options to IQueryable
                // Example:
                // - $filter becomes SQL WHERE
                // - $orderby becomes SQL ORDER BY
                // - $top/$skip becomes SQL OFFSET/FETCH
                //
                // Note: SQL is still executed later when data is actually enumerated.
                var result = options.ApplyTo(baseQuery, settings);

                // Step F: Return results
                // ASP.NET Core will execute the query when it writes the response.
                return Ok(result);
            }
            catch (ODataException ex)
            {
                // If someone sends invalid query like wrong syntax or too high $top,
                // return 400 (Bad Request) with message.
                _logger.LogWarning(ex, "Invalid OData query request.");
                return BadRequest(new { Message = "Invalid OData query.", Details = ex.Message });
            }
        }

        // Get a single product by Id (OData key lookup)
        // Correct URL format:
        // /odata/Products(<guid>)
        //
        // NOTE:
        // Do NOT add an extra slash like /odata/Products/(<guid>)
        // That is not standard OData key syntax.
        [HttpGet("({key})")]
        public IActionResult Get([FromODataUri] Guid key, ODataQueryOptions<ProductDTO> options)
        {
            try
            {
                // Safety validation for single item query
                // MaxTop=1 means client can’t request multiple items here.
                var validation = new ODataValidationSettings
                {
                    MaxTop = 1
                };

                options.Validate(validation);

                // Build query for one product
                // Still IQueryable so client can use $select (choose fields).
                var baseQuery = _queryService.QueryById(key);

                // Apply OData options (mostly $select here)
                var result = options.ApplyTo(baseQuery);

                return Ok(result);
            }
            catch (ODataException ex)
            {
                _logger.LogWarning(ex, "Invalid OData key query request.");
                return BadRequest(new { Message = "Invalid OData query.", Details = ex.Message });
            }
        }
    }
}

 

What ODataQueryOptions<T> Actually Does?

The ODataQueryOptions<T> reads OData query parameters such as $filter, $orderby, $top, $skip, $select, $expand, and $count from the URL and converts them into a structured form. These options are not executed immediately—they are applied to an IQueryable so they can be translated into SQL later.

• Reads query options from the URL
• Converts them into expression trees
• Works only with IQueryable

When Is the Database Query Executed?

The database query is not executed when:

• The repository returns IQueryable
• OData options are validated
• ApplyTo() is called

The query is executed only when the result is enumerated, which happens when ASP.NET Core starts writing the response (inside return Ok(result)).

• Query building is lazy (deferred execution)
• SQL runs at response serialization time
• All filters, sorting, and paging are executed in one SQL query

Step 9: Exposing OData Through the API Gateway

In microservices, clients never call services directly. By routing OData through the API Gateway:

• We keep a single public entry point
• We enforce consistent security and routing rules
• We allow independent scaling of ProductService
• We prevent accidental bypass of gateway policies

Placing the OData route above the REST route ensures correct request matching. Open ApiGateway/ocelot.json, then add the following route ABOVE the existing ProductService Route so it matches first.

{
  "UpstreamPathTemplate": "/products/odata/{everything}",
  "UpstreamHttpMethod": [ "Get"],

  "DownstreamPathTemplate": "/odata/{everything}",
  "DownstreamScheme": "https",

  "UseServiceDiscovery": true,
  "ServiceName": "ProductService",

  "LoadBalancerOptions": {
    "Type": "RoundRobin"
  }
}

Run Consul in Development Mode

Please run the following command in Command Prompt.

consul agent -dev -client=0.0.0.0 -ui -node=consul-dev

Note: Keep this command window open while working with your microservices. If you close it, Consul stops, and services cannot register or be discovered.

Open Consul UI

Once Consul is running, we can monitor everything from the browser. Open your browser and navigate to: http://localhost:8500

Run Microservices

Please run all Microservices projects, specifically the API gateway and Product Microservice.

How to test OData via Gateway

Our:

• Gateway Base: https://localhost:7204
• Upstream OData prefix: /products/odata

Test Metadata (first and most important)

GET https://localhost:7204/products/odata/$metadata

If this works, routing is correct.

Scenario 1: Admin Grid – Paging + Total Count

First page

GET https://localhost:7204/products/odata/Products?$count=true&$top=5&$skip=0

Next page

GET https://localhost:7204/products/odata/Products?$count=true&$top=5&$skip=5

Scenario 2: Admin Grid – Select only the columns shown in UI (performance)

GET https://localhost:7204/products/odata/Products?$select=Id,Name,SKU,Price,StockQuantity,IsActive&$top=25

Scenario 3: Active products only (catalog filter)

GET https://localhost:7204/products/odata/Products?$filter=IsActive eq true

Scenario 4: Category-wise view (category page)

GET https://localhost:7204/products/odata/Products?$filter=CategoryId eq D7F8E3C4-6A7B-4A12-9C9F-2B3456789ABC and IsActive eq true&$orderby=CreatedOn desc&$top=20

Scenario 5: In-stock products (avoid showing out-of-stock)

GET https://localhost:7204/products/odata/Products?$filter=StockQuantity gt 0 and IsActive eq true&$orderby=StockQuantity desc&$top=50

Scenario 6: Price range slider (₹500 to ₹5000)

GET https://localhost:7204/products/odata/Products?$filter=Price ge 500 and Price le 5000&$orderby=Price asc&$top=50

Scenario 7: Combined “Power Query” (real admin grid state)

GET https://localhost:7204/products/odata/Products?$filter=IsActive eq true and StockQuantity gt 0&$orderby=CreatedOn desc&$select=Id,Name,SKU,Price,StockQuantity,AverageRating,DiscountedPrice&$count=true&$top=25&$skip=0

Scenario 8: Get products with images (expand)

GET https://localhost:7204/products/odata/Products?$top=10&$expand=Images

Scenario 9: Expand images but only bring the required image fields (nested select)

GET https://localhost:7204/products/odata/Products?$top=10&$select=Id,Name,Price&$expand=Images($select=Id,ImageUrl,IsPrimary)

Scenario 10: Expand images only for one product (product details page)

GET https://localhost:7204/products/odata/Products/(C968B396-8C5B-47F5-8D2E-4C9A6DD7F37D)?$expand=Images

Conclusion: Why OData Matters?

OData allows a single API endpoint to serve many UI needs—such as admin grids, catalog listings, and reports—without creating multiple endpoints for every filter or screen. This keeps the API simple while giving the frontend full flexibility to request exactly the data it needs.

When used correctly with validation, DTOs, efficient database queries, and API Gateway control, OData becomes a safe and scalable solution rather than a performance risk.

• One endpoint supports many UI scenarios
• Validation protects the database
• DTOs control what data is exposed
• Gateway integration keeps access secure

OData reduces API complexity while giving flexible, efficient, and controlled data access for modern UI-driven applications.