OData! Oh my!! So powerful indeed!!!
Welcome back, today we will talk about one of my favorite technologies. OData!! You don't know how many times this standard has save me time and discussions as it is a standard.
What is OData?
"The Open Data Protocol (OData) is a data access protocol built on core protocols like HTTP and commonly accepted methodologies like REST for the web"
Basically, you can query your model via URL with selectors, paging, ordering, etc.
I think I started using OData when I first heard of it for table storage in Azure back in the day. (Remind me please to talk about the repository pattern I have for multiple providers)
This will be a lengthy post, but not that long that I need to split it.
As always, here is the GitHub with the sample code
jtenoriodseldon
Let's begin!
Setting up the Database
We will be using the sample Adventure Works Light database from Microsoft, why? Well, it has sufficient data, and it is a well-known database with relationships (this will become important later on)
For this example, I will restore it locally on SQL express, but also you can execute a docker SQL server and restore it there; it needs more configuration, but it is a valid approach; in fact, for unit and integration tests it is a must.
(LocalDb)\MSSQLLocalDB
Once it is restored, we will create the model using reverse engineering with EF Core Power Tools. This tool will read the database schema and using T4 Scaffolding will create the classes and DB context.
Extension
Reverse Engineer
Connection to the Database
Tables to create on model
Final settings and result
Model out of the way, we can continue with our API.
Solution
Here is a snapshot of the solution. I will explain step by step what each section means (except the model)
Settings
We start by building a simple settings class that will contain default configuration for our OData custom result.
/// <summary>
/// Represents the settings for OData configuration.
/// </summary>
public class ODataSettings
{
#region Properties
/// <summary>
/// Gets or sets the host name for the OData service.
/// </summary>
public required string HostName { get; set; }
/// <summary>
/// Gets or sets a value indicating whether HTTPS is used.
/// </summary>
public bool IsHttps { get; set; }
/// <summary>
/// Gets or sets the maximum page size for OData queries.
/// </summary>
public int MaxPageSize { get; set; } = 2500;
/// <summary>
/// Gets or sets the default page size for OData queries.
/// </summary>
public int DefaultPageSize { get; set; } = 250;
#endregion
}- Host Name. Will hold the hostname you want the next URL (for paging) will use.
- IsHttps. Self-explanatory.
- MaxPageSize. The MAX number of items returned in the result and the default.
- DefaultPageSize. The default page size and the default.
appsettings.json
Here we add the connection string which is using the database we restored with the integrated security and the OData settings.
"ConnectionStrings": {
"DefaultConnection": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=AdventureWorksLT2019;Integrated Security=True;Connect Timeout=30;"
},
"ODataSettings": {
"HostName": "localhost",
"IsHttps": true,
"MaxPageSize": 200,
"DefaultPageSize": 100
}Open API
We will be using a modified OData result, as we will be skipping the xml metadata, and we will be using the Open API specification.
Since there are not default parameters for OData in Open API, I created this operation filter to be able to use it from Swagger UI.
/// <summary>
/// A filter that adds OData query parameters to the OpenAPI documentation.
/// </summary>
public class ODATAParamsFilter : IOperationFilter
{
#region Methods
/// <summary>
/// A list of OData query parameters to be added to the OpenAPI documentation.
/// </summary>
private static readonly List<OpenApiParameter> ODataOpenAPIParameters = (new List<(string Name, string Description)>()
{
( "$top", "The max number of records to return."),
( "$skip", "The number of records to skip."),
( "$filter", "A function that must evaluate to true for a record to be returned."),
( "$select", "Specifies a subset of properties to return."),
( "$orderby", "Determines which values are used to order a collection of records."),
( "$expand", "Use to add related query data.")
}).Select(pair => new OpenApiParameter
{
Name = pair.Name,
Required = false,
Schema = new OpenApiSchema { Type = "String" },
In = ParameterLocation.Query,
Description = pair.Description,
AllowReserved = true
}).ToList();
/// <summary>
/// Applies the OData query parameters to the given OpenAPI operation.
/// </summary>
/// <param name="operation">The OpenAPI operation to modify.</param>
/// <param name="context">The context for the operation filter.</param>
public void Apply(OpenApiOperation operation, OperationFilterContext context)
{
try
{
if (context.ApiDescription.ParameterDescriptions[0].ModelMetadata.ModelType.Name.Contains("ODataQueryOptions", StringComparison.InvariantCultureIgnoreCase))
{
operation.Parameters ??= [];
foreach (var item in ODataOpenAPIParameters)
operation.Parameters.Add(item);
}
}
catch (Exception)
{
//ignore and continue
}
}
#endregion Methods
}Note that the apply filter only adds the parameters if the first argument on the method is of type ODataQueryOptions. Will make sense once we look at the controller.
Program.cs
Now for the fun part and start putting everything together.
var builder = WebApplication.CreateBuilder(args);Initializes a new instance of WebApplicationBuilder which is used to configure and build the web application.
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");var odataSettings = builder.Configuration.GetSection("ODataSettings").Get()!;• connectionString retrieves the database connection string from the configuration.
• odataSettings retrieves OData settings from the configuration and maps them to an ODataSettings object.
builder.Services.AddDbContext<AdventureWorksLT2019Context>(options => options.UseSqlServer(connectionString));
builder.Services.AddSingleton(odataSettings);
builder.Services.AddMvc(options => options.EnableEndpointRouting = false);
builder.Services.AddControllers()
.AddOData(options => options.Select().Filter().OrderBy().Expand().Count());
builder.Services
.AddControllers()
.AddNewtonsoftJson(options =>
{
options.SerializerSettings.Formatting = Newtonsoft.Json.Formatting.None;
options.SerializerSettings.NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore;
options.SerializerSettings.MissingMemberHandling = Newtonsoft.Json.MissingMemberHandling.Ignore;
options.SerializerSettings.DateTimeZoneHandling = Newtonsoft.Json.DateTimeZoneHandling.Local;
options.SerializerSettings.ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver();
options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;
})
.AddODataNewtonsoftJson();• AddDbContext: Registers the AdventureWorksLT2019Context with the specified SQL Server connection string.
• AddSingleton: Registers the odataSettings as a singleton service.
• AddMvc: Adds MVC services to the container with endpoint routing disabled.
• AddControllers: Adds controller services and configures OData with support for select, filter, order by, expand, and count functionalities.
• AddNewtonsoftJson: Configures JSON serialization settings using Newtonsoft.Json.
• AddODataNewtonsoftJson: Adds support for OData with Newtonsoft.Json.
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen(c =>
{
c.IncludeXmlComments(Path.Combine(AppContext.BaseDirectory, $"{Assembly.GetExecutingAssembly().GetName().Name}.xml"));
c.OperationFilter<ODATAParamsFilter>();
});• AddEndpointsApiExplorer: Adds services for API endpoint exploration.
• AddSwaggerGen: Configures Swagger for API documentation, including XML comments and a custom operation filter (ODATAParamsFilter).
var app = builder.Build();
app.UseRouting();
app.UseSwagger();
app.UseSwaggerUI();
app.UseAuthorization();
app.MapControllers();
await app.RunAsync();
• app.UseRouting(): Adds routing middleware.
• app.UseSwagger(): Enables middleware to serve generated Swagger as a JSON endpoint.
• app.UseSwaggerUI(): Enables middleware to serve Swagger UI.
• app.UseAuthorization(): Adds authorization middleware.
• app.MapControllers(): Maps controller routes.
• app.RunAsync(): Runs the application asynchronously.
Final program.cs
using Microsoft.AspNetCore.OData;
using Microsoft.AspNetCore.OData.NewtonsoftJson;
using Microsoft.EntityFrameworkCore;
using ODataAPI.Filter;
using ODataAPI.Models;
using ODataAPI.Settings;
using System.Reflection;
var builder = WebApplication.CreateBuilder(args);
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
var odataSettings = builder.Configuration.GetSection("ODataSettings").Get<ODataSettings>()!;
builder.Services.AddDbContext<AdventureWorksLT2019Context>(options => options.UseSqlServer(connectionString));
builder.Services.AddSingleton(odataSettings);
builder.Services.AddMvc(options => options.EnableEndpointRouting = false);
builder.Services.AddControllers()
.AddOData(options => options.Select().Filter().OrderBy().Expand().Count());
builder.Services
.AddControllers()
.AddNewtonsoftJson(options =>
{
options.SerializerSettings.Formatting = Newtonsoft.Json.Formatting.None;
options.SerializerSettings.NullValueHandling = Newtonsoft.Json.NullValueHandling.Ignore;
options.SerializerSettings.MissingMemberHandling = Newtonsoft.Json.MissingMemberHandling.Ignore;
options.SerializerSettings.DateTimeZoneHandling = Newtonsoft.Json.DateTimeZoneHandling.Local;
options.SerializerSettings.ContractResolver = new Newtonsoft.Json.Serialization.CamelCasePropertyNamesContractResolver();
options.SerializerSettings.ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore;
})
.AddODataNewtonsoftJson();
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen(c =>
{
c.IncludeXmlComments(Path.Combine(AppContext.BaseDirectory, $"{Assembly.GetExecutingAssembly().GetName().Name}.xml"));
c.OperationFilter<ODATAParamsFilter>();
});
var app = builder.Build();
app.UseRouting();
app.UseSwagger();
app.UseSwaggerUI();
app.UseAuthorization();
app.MapControllers();
await app.RunAsync();Custom MVC OData Executor and Result
We move now to the DTOs
Custom Error when the OData Controller fails.
/// <summary>
/// Represents a custom error for OData API.
/// </summary>
public class ODataCustomError
{
#region Properties
/// <summary>
/// Gets or sets the error message.
/// </summary>
public string? ErrorMessage { get; set; }
#endregion
}OData Envelope that contains the basic information about the results.
/// <summary>
/// Represents an envelope for OData responses.
/// </summary>
public class ODataEnvelope
{
#region Properties
/// <summary>
/// Gets or sets the total count of items.
/// </summary>
public int Count { get; set; }
/// <summary>
/// Gets or sets the size of each page.
/// </summary>
public int PageSize { get; set; }
/// <summary>
/// Gets or sets the total number of pages.
/// </summary>
public int TotalPages { get; set; }
/// <summary>
/// Gets or sets the current page number.
/// </summary>
public int CurrentPage { get; set; }
/// <summary>
/// Gets or sets the URL for the next page.
/// </summary>
public string? NextUrl { get; set; }
#endregion
}OData Results with envelope, it is using generics as it can be used with multiple entities.
/// <summary>
/// Represents a collection of results with an associated OData envelope.
/// </summary>
/// <typeparam name="T">The type of the results contained in the collection.</typeparam>
public class ODataResultEnvelopeCollection<T>
{
#region Properties
/// <summary>
/// Gets or sets the list of results.
/// </summary>
public List<T>? Results { get; set; }
/// <summary>
/// Gets or sets the OData envelope associated with the results.
/// </summary>
public ODataEnvelope? Envelope { get; set; }
#endregion
}OData Result when the select odata parameter is used. This is necessary as it does not use generics but an IQueryable result.
/// <summary>
/// Represents a projection of OData result envelope.
/// </summary>
public class ODataResultEnvelopeProjection
{
#region Properties
/// <summary>
/// Gets or sets the queryable results.
/// </summary>
public IQueryable? Results { get; set; }
/// <summary>
/// Gets or sets the OData envelope.
/// </summary>
public ODataEnvelope? Envelope { get; set; }
#endregion
}Executor/Result
This class and method are the core of the project. It generates an OData result based on the provided query options, queryable data, and OData settings. It returns an IActionResult.
var pageSize = odataSettings.DefaultPageSize;
var maximumPageSize = odataSettings.MaxPageSize;
if (options.Count == null)
options.Request.QueryString = options.Request.QueryString.Add("$count", "true");
options.Request.Host = new HostString(odataSettings.HostName);
options.Request.IsHttps = odataSettings.IsHttps;
var alternateOptions = new ODataQueryOptions<T>(options.Context, options.Request);
• pageSize and maximumPageSize are set from the odataSettings.
• If the $count query option is not present, it is added to the request.
• The request host and HTTPS settings are updated based on odataSettings.
• alternateOptions is created to apply the modified request.
long? count;
try
{
if (alternateOptions.Filter != null)
count = alternateOptions.Count.GetEntityCount(alternateOptions.Filter.ApplyTo(queryable, new ODataQuerySettings()));
else
count = alternateOptions.Count.GetEntityCount(queryable);
}
catch (Exception)
{
count = null;
}
The count of entities is calculated, considering any filters applied. If an exception occurs, count is set to null.
try
{
IQueryable? results = null;
if (alternateOptions.Top != null)
{
if (alternateOptions.Top.Value > maximumPageSize || options.Top.Value < 1)
return new BadRequestObjectResult($"The page size cannot exceed {maximumPageSize} or be less than 1");
pageSize = alternateOptions.Top.Value;
results = alternateOptions.ApplyTo(queryable, new ODataQuerySettings { PageSize = pageSize });
}
else
results = alternateOptions.ApplyTo(queryable, new ODataQuerySettings { PageSize = pageSize });
• If the $top query option is present, it is used to set the page size, ensuring it does not exceed the maximum or fall below 1.
• The query is applied to the queryable data source to obtain the results.
if (count == null)
{
try
{
count = results.Cast<T>().Count();
}
catch (Exception)
{
try
{
count = results.Cast<IEnumerable>().Count();
}
catch (Exception)
{
var jsonResult = JsonConvert.SerializeObject(results);
if (jsonResult != null)
{
var dynamicResult = JsonConvert.DeserializeObject(jsonResult) as dynamic;
if (dynamicResult != null)
count = dynamicResult.Count;
}
}
}
}
If count is still null, it attempts to calculate the count by casting the results to different types and, as a last resort, serializing to JSON and deserializing to a dynamic object.
var skip = alternateOptions.Skip != null ? alternateOptions.Skip.Value : 0;
var currentPage = (skip + pageSize) / pageSize;
var totalPages = (int)Math.Ceiling((decimal)(count ?? 0) / pageSize);
string? nextUrl = null;
if (currentPage < totalPages)
{
try
{
nextUrl = alternateOptions.Request.GetNextPageLink(pageSize, null, null)?.ToString().Replace("$top=0", $"$top={pageSize}");
if (nextUrl == null)
{
var http = odataSettings.IsHttps ? "https://" : "http://";
var queryString = alternateOptions.Request.Query;
var finalQueryString = "?";
foreach (var query in queryString)
{
if (!query.Key.Contains("top") && !query.Key.Contains("skip"))
finalQueryString += $"{query.Key}={query.Value}&";
}
finalQueryString += $"$top={pageSize}&$skip={pageSize * currentPage}";
nextUrl = $"{http}{odataSettings.HostName}{alternateOptions.Request.Path}{finalQueryString}";
}
}
catch (Exception)
{
nextUrl = null;
}
}
• Calculates pagination details: skip, currentPage, totalPages, and nextUrl.
• If there are more pages, it generates the nextUrl.
try
{
if (count <= 0)
return new NotFoundObjectResult(
new ODataEnvelope
{
Count = 0,
CurrentPage = 0,
NextUrl = string.Empty,
PageSize = 0,
TotalPages = 0
});
return new OkObjectResult(
new ODataResultEnvelopeCollection<T>
{
Envelope = new ODataEnvelope
{
Count = (int)(count.HasValue ? count.Value : 0),
PageSize = pageSize,
TotalPages = totalPages,
CurrentPage = currentPage,
NextUrl = nextUrl
},
Results = count >= 0 ? [.. results.Cast<T>()] : null
});
}
catch (InvalidCastException)
{
return new OkObjectResult(
new ODataResultEnvelopeProjection
{
Envelope = new ODataEnvelope
{
Count = (int)(count.HasValue ? count.Value : 0),
PageSize = pageSize,
TotalPages = totalPages,
CurrentPage = currentPage,
NextUrl = nextUrl
},
Results = count >= 0 ? results : null
});
}
• If count is zero or less, returns a NotFoundObjectResult with an empty envelope.
• Otherwise, returns an OkObjectResult with the results and envelope.
• Handles InvalidCastException by returning a projection envelope.
catch (ODataException ex)
{
return new BadRequestObjectResult(
new ODataCustomError
{
ErrorMessage = ex.Message
});
}
If an ODataException occurs, returns a BadRequestObjectResult with the error message.
Final ODataCustomResult.cs
/// <summary>
/// OData Custom Result
/// </summary>
public static class ODataCustomResult
{
#region Methods
/// <summary>
/// Obtains the OData result based on the provided query options, queryable data, and OData settings.
/// </summary>
/// <typeparam name="T">The type of the data in the queryable.</typeparam>
/// <param name="options">The OData query options.</param>
/// <param name="queryable">The queryable data source.</param>
/// <param name="odataSettings">The OData settings.</param>
/// <returns>An IActionResult containing the OData result.</returns>
public static IActionResult ObtainResult<T>(ODataQueryOptions<T> options, IQueryable queryable, ODataSettings odataSettings)
{
if (queryable == null)
{
return new NotFoundObjectResult(
new ODataEnvelope
{
Count = 0,
PageSize = 0,
TotalPages = 0,
CurrentPage = 0,
NextUrl = string.Empty
});
}
var pageSize = odataSettings.DefaultPageSize;
var maximumPageSize = odataSettings.MaxPageSize;
// Add the count context and use the alternate options, have to do this to inject into the ODataQueryOptions pipeline
if (options.Count == null)
options.Request.QueryString = options.Request.QueryString.Add("$count", "true");
// Because the get next URL takes current context, we need to change it to the context of the frontend
options.Request.Host = new HostString(odataSettings.HostName);
options.Request.IsHttps = odataSettings.IsHttps;
var alternateOptions = new ODataQueryOptions<T>(options.Context, options.Request);
long? count;
// Perform count with or without filter, this will give us the correct count and page size
try
{
if (alternateOptions.Filter != null)
count = alternateOptions.Count.GetEntityCount(alternateOptions.Filter.ApplyTo(queryable, new ODataQuerySettings()));
else
count = alternateOptions.Count.GetEntityCount(queryable);
}
catch (Exception)
{
count = null;
}
try
{
// Obtain results, if top querystring exists, then use that one instead of the default, also make sure the top does not go over the limit
IQueryable? results = null;
if (alternateOptions.Top != null)
{
if (alternateOptions.Top.Value > maximumPageSize || options.Top.Value < 1)
return new BadRequestObjectResult($"The page size cannot exceed {maximumPageSize} or be less than 1");
pageSize = alternateOptions.Top.Value;
results = alternateOptions.ApplyTo(queryable, new ODataQuerySettings { PageSize = pageSize });
}
else
results = alternateOptions.ApplyTo(queryable, new ODataQuerySettings { PageSize = pageSize });
// BUG with OData, have to cast to IEnumerable, since casting to specific DTO does not work as the transmutation is from SelectMany to DTO
// IEnumerable will work as it does not care about the type, performance is impacted, but not that much
if (count == null)
{
try
{
count = results.Cast<T>().Count();
}
catch (Exception)
{
try
{
count = results.Cast<IEnumerable>().Count();
}
catch (Exception)
{
// Edge case, serialize to dynamic, as it does not matter the type at this moment, just the count
// This is slow as it will serialize twice
var jsonResult = JsonConvert.SerializeObject(results);
if (jsonResult != null)
{
var dynamicResult = JsonConvert.DeserializeObject(jsonResult) as dynamic;
if (dynamicResult != null)
count = dynamicResult.Count;
}
}
}
}
// Calculate envelope
var skip = alternateOptions.Skip != null ? alternateOptions.Skip.Value : 0;
var currentPage = (skip + pageSize) / pageSize;
var totalPages = (int)Math.Ceiling((decimal)(count ?? 0) / pageSize);
string? nextUrl = null;
if (currentPage < totalPages)
{
try
{
nextUrl = alternateOptions.Request.GetNextPageLink(pageSize, null, null)?.ToString().Replace("$top=0", $"$top={pageSize}");
if (nextUrl == null)
{
// Calculate old fashion
var http = odataSettings.IsHttps ? "https://" : "http://";
var queryString = alternateOptions.Request.Query;
var finalQueryString = "?";
foreach (var query in queryString)
{
if (!query.Key.Contains("top") && !query.Key.Contains("skip"))
finalQueryString += $"{query.Key}={query.Value}&";
}
finalQueryString += $"$top={pageSize}&$skip={pageSize * currentPage}";
nextUrl = $"{http}{odataSettings.HostName}{alternateOptions.Request.Path}{finalQueryString}";
}
}
catch (Exception)
{
nextUrl = null;
}
}
try
{
if (count <= 0)
return new NotFoundObjectResult(
new ODataEnvelope
{
Count = 0,
CurrentPage = 0,
NextUrl = string.Empty,
PageSize = 0,
TotalPages = 0
});
return new OkObjectResult(
new ODataResultEnvelopeCollection<T>
{
Envelope = new ODataEnvelope
{
Count = (int)(count.HasValue ? count.Value : 0),
PageSize = pageSize,
TotalPages = totalPages,
CurrentPage = currentPage,
NextUrl = nextUrl
},
Results = count >= 0 ? [.. results.Cast<T>()] : null
});
}
catch (InvalidCastException)
{
//casting to specific type failed, try to cast to IEnumerable as the projection is not a known type
return new OkObjectResult(
new ODataResultEnvelopeProjection
{
Envelope = new ODataEnvelope
{
Count = (int)(count.HasValue ? count.Value : 0),
PageSize = pageSize,
TotalPages = totalPages,
CurrentPage = currentPage,
NextUrl = nextUrl
},
Results = count >= 0 ? results : null
});
}
}
catch (ODataException ex)
{
return new BadRequestObjectResult(
new ODataCustomError
{
ErrorMessage = ex.Message
});
}
}
#endregion Methods
}Customers Controller, Entity Framework and IQueryable
We have been using IQueryable all over the place, but what does it mean and why it is important.
Well, in short is deferred query execution.
Short story, if used correctly, you can chain multiple LINQ queries and when ready to return the result it will mash them together and try to create one single command.
The controller and OData executor follows this pattern, at the last step it will try to execute one single command.
Controller
#region Constructors
/// <summary>
/// Initializes a new instance of the <see cref="CustomersController"/> class.
/// </summary>
/// <param name="logger">The logger instance.</param>
/// <param name="dbContext">The database context.</param>
/// <param name="oDataSettings">The OData settings.</param>
public CustomersController(ILogger<CustomersController> logger, AdventureWorksLT2019Context dbContext, ODataSettings oDataSettings)
{
_logger = logger;
_dbContext = dbContext;
_odataSettings = oDataSettings;
}
#endregion
The constructor initializes the CustomersController with the provided logger, dbContext, and oDataSettings.
#region API
/// <summary>
/// Retrieves all customers with OData query options.
/// </summary>
/// <param name="options">The OData query options.</param>
/// <returns>A collection of customers or a count of customers based on the query options.</returns>
[HttpGet]
[Route("api/customers")]
[Produces("application/json")]
[ProducesResponseType(typeof(ODataResultEnvelopeCollection<Customer>), Status200OK)]
[ProducesResponseType(typeof(ODataEnvelope), Status404NotFound)]
[ProducesResponseType(typeof(ODataCustomError), Status400BadRequest)]
public IActionResult RetrieveAll([SwaggerIgnore] ODataQueryOptions<Customer> options)
{
return ODataCustomResult.ObtainResult(options, _dbContext.Customers.AsQueryable(), _odataSettings);
}
#endregion
Attributes
• [HttpGet]: Specifies that this method handles HTTP GET requests.
• [Route("api/customers")]: Defines the route for this action as api/customers.
• [Produces("application/json")]: Specifies that the response is in JSON format.
• [ProducesResponseType(typeof(ODataResultEnvelopeCollection), Status200OK)]: Indicates that a successful response returns a 200 OK status with an ODataResultEnvelopeCollection.
• [ProducesResponseType(typeof(ODataEnvelope), Status404NotFound)]: Indicates that a 404 NotFound status returns an ODataEnvelope.
• [ProducesResponseType(typeof(ODataCustomError), Status400BadRequest)]: Indicates that a 400 BadRequest status returns an ODataCustomError.
Method
• RetrieveAll: This method retrieves all customers based on the provided OData query options.
• options: The OData query options passed to the method.
• ODataCustomResult.ObtainResult: Calls the ObtainResult method from the ODataCustomResult class, passing the query options, the Customers DbSet as a queryable, and the OData settings.
This method leverages the ODataCustomResult class to handle the OData query options and return the appropriate result, whether it's a collection of customers, a count, or an error message.
Final CustomersController.cs
/// <summary>
/// Controller to handle customer-related operations.
/// </summary>
[ApiController]
[ApiExplorerSettings(IgnoreApi = false)]
public class CustomersController : ODataController
{
#region Members
private readonly ILogger<CustomersController> _logger;
private readonly AdventureWorksLT2019Context _dbContext;
private readonly ODataSettings _odataSettings;
#endregion
#region Constructors
/// <summary>
/// Initializes a new instance of the <see cref="CustomersController"/> class.
/// </summary>
/// <param name="logger">The logger instance.</param>
/// <param name="dbContext">The database context.</param>
/// <param name="oDataSettings">The OData settings.</param>
public CustomersController(ILogger<CustomersController> logger, AdventureWorksLT2019Context dbContext, ODataSettings oDataSettings)
{
_logger = logger;
_dbContext = dbContext;
_odataSettings = oDataSettings;
}
#endregion
#region API
/// <summary>
/// Retrieves all customers with OData query options.
/// </summary>
/// <param name="options">The OData query options.</param>
/// <returns>A collection of customers or a count of customers based on the query options.</returns>
[HttpGet]
[Route("api/customers")]
[Produces("application/json")]
[ProducesResponseType(typeof(ODataResultEnvelopeCollection<Customer>), Status200OK)]
[ProducesResponseType(typeof(ODataEnvelope), Status404NotFound)]
[ProducesResponseType(typeof(ODataCustomError), Status400BadRequest)]
public IActionResult RetrieveAll([SwaggerIgnore] ODataQueryOptions<Customer> options)
{
return ODataCustomResult.ObtainResult(options, _dbContext.Customers.AsQueryable(), _odataSettings);
}
#endregion
}
That was a lot of explanation, but we reached finally the testing phase!
Swagger UI
If you execute the project, it will open the swagger UI. Since we are decorating with attributes the controller, it will show nicely.
And guess what? It shows the OData parameters as well.
Executing the GET customers will retrieve the results and envelope
And we can check the SQL that is being executed by entity framework
Now let's try the filter and select OData parameters
https://localhost:7113/api/customers?$filter=firstName%20eq%20'Orlando'&$select=customerId,firstName,lastName&$orderby=lastName
HTTP file
I included the http file with some samples
@ODataAPI_HostAddress = https://localhost:7113
### TOP
GET {{ODataAPI_HostAddress}}/api/customers?$top=5
Accept: application/json
### Filter
GET {{ODataAPI_HostAddress}}/api/customers?$top=5&$filter=firstName%20eq%20'Orlando'
Accept: application/json
### Order By
GET {{ODataAPI_HostAddress}}/api/customers?$orderby=lastName
Accept: application/json
### Select
GET {{ODataAPI_HostAddress}}/api/customers?$select=customerId,firstName,lastName
Accept: application/json
### Expand
GET {{ODataAPI_HostAddress}}/api/customers?$expand=customerAddresses&$filter=customerAddresses/any(i:i%20ne%20null)&$top=5
Accept: application/jsonIn the next post we will examine the rest of the OData endpoints and some advances queries
Happy coding!!!