This is the tutorial where we will create Web API using ASP.NET 6 and OData. We will perform CRUD operations, pagination, batching in Student App.
ODATA
OData(Open Data Protocol) defines a set of best practices for building and consuming RESTful APIs.
It is an extension on top of rest to provide rich data experience.
OData advocates a standard way of implementing REST APIs that allows for SQL-like querying capabilities.
OData supports:
- Ordering or Sorting
- filtering
- Pagination
- Batching etc.,
Scenario: There is a Student API that returns all students objects who are registered but sometimes there will be a requirement like we want the information only the students who are falling within particular criterion and for that, you have to implement a special API endpoint and here comes the OData in the picture. You can perform the actions like filtering, sorting and ordering the data on the client-side itself, don't have to provide or create a new endpoint on the developer side.
1. CRUD Operations
Preparation
- Install .NET 6 SDK: (https://dotnet.microsoft.com/download/dotnet/6.0)
- Install SQL and Setup: (https://www.microsoft.com/en-in/sql-server/sql-server-downloads)
- Install Postman for testing the API: https://www.postman.com/downloads/
Create Project
- Use this command:
dotnet new webapi -o ODataStudent
- Create Solution:
dotnet new sln
- Connect solution with project:
dotnet sln add ODataStudent
Add Dependencies
- Install OData:
Install-Package Microsoft.AspNetCore.OData -Version 8.0.4
- Install EF Core Design:
Install-Package Microsoft.EntityFrameworkCore.Design -Version 6.0.0
- Install EF Core Tools:
Install-Package Microsoft.EntityFrameworkCore.Tools -Version 6.0.0
- Install EF Core SQL:
Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 6.0.2
- Install WebApi WebHost:
Install-Package Microsoft.AspNet.WebApi.WebHost -Version 5.2.7
Connect Project to Database
- Create
Models
folder. In the same folder createStudent.cs
file.
namespace ODataStudent.Models
{
public class Student
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
}
- Create
Data
folder and add Student Context inStudentDataContext.cs
file.
using Microsoft.EntityFrameworkCore;
using ODataStudent.Models;
namespace ODataStudent.Data
{
public class StudentDataContext: DbContext
{
public StudentDataContext(DbContextOptions<StudentDataContext> options) : base(options)
{
}
public DbSet<Student> Students { get; set; }
}
}
- Add
ConnectionString
toappsettings.json
(Change as per your database settings)
{
"ConnectionStrings": {
"DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=ODataStudent;Trusted_Connection=True;MultipleActiveResultSets=True"
},
}
- Add to connectionString and StudentDataContext in
Program.cs
file.
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddDbContext<StudentDataContext>(x =>
{
x.UseSqlServer(connectionString);
});
- Open Package Manager Console and run the following commands.
Create New Migration:
add-migration AddStudents
Update Database:
update-database
Create API Controller
- Add IEdmModel to the
Program.cs
file. (EDM - Entity data model, acts as a mapper between data source and data engine. EDM turns raw data into entities that allow functionality like count, select and performed through API.)
static IEdmModel GetEdmModel()
{
ODataConventionModelBuilder builder = new();
builder.EntitySet<Student>("Students");
return builder.GetEdmModel();
}
-Create StudentsController.cs
file in Controllers folder.
- [EnableQuery] attribute: This enables clients to query the underlying data source. Pagination, search/filtering, ordering and selecting is enabled by a single attribute!
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.OData.Deltas;
using Microsoft.AspNetCore.OData.Formatter;
using Microsoft.AspNetCore.OData.Query;
using Microsoft.AspNetCore.OData.Results;
using Microsoft.AspNetCore.OData.Routing.Controllers;
using Microsoft.EntityFrameworkCore;
using ODataStudent.Data;
using ODataStudent.Models;
namespace ODataStudent.Controllers
{
public class StudentsController: ODataController
{
private readonly StudentDataContext _db;
private readonly ILogger<StudentsController> _logger;
public StudentsController(StudentDataContext dbContext, ILogger<StudentsController> logger)
{
_logger = logger;
_db = dbContext;
}
//Get all students
[EnableQuery]
public IQueryable<Student> Get()
{
return _db.Students;
}
//Get by Id
[EnableQuery]
public SingleResult<Student> Get([FromODataUri] int key)
{
var result = _db.Students.Where(c => c.Id == key);
return SingleResult.Create(result);
}
[EnableQuery]
public async Task<IActionResult> Post([FromBody] Student student)
{
_db.Students.Add(student);
await _db.SaveChangesAsync();
return Created(student);
}
//Patch
[EnableQuery]
public async Task<IActionResult> Patch([FromODataUri] int key, Delta<Student> note)
{
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
var existingNote = await _db.Students.FindAsync(key);
if (existingNote == null)
{
return NotFound();
}
note.Patch(existingNote);
try
{
await _db.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException)
{
if (!NoteExists(key))
{
return NotFound();
}
else
{
throw;
}
}
return Updated(existingNote);
}
//Delete
[EnableQuery]
public async Task<IActionResult> Delete([FromODataUri] int key)
{
Student existingNote = await _db.Students.FindAsync(key);
if (existingNote == null)
{
return NotFound();
}
_db.Students.Remove(existingNote);
await _db.SaveChangesAsync();
return StatusCode(StatusCodes.Status204NoContent);
}
private bool NoteExists(int key)
{
return _db.Students.Any(p => p.Id == key);
}
}
}
Add OData service to the
Program.cs
file and enable filter, select and expand features.AddRouteComponents():Its a extension method in odata alerts the prefix path of odata controller and the EdmModel name should keep the same with odata controller name.
builder.Services.AddControllers().AddOData(opt => opt.AddRouteComponents("v1", GetEdmModel()).Filter().Select().OrderBy().Expand());
- Program.cs file:
using Microsoft.AspNetCore.OData;
using Microsoft.EntityFrameworkCore;
using Microsoft.OData.Edm;
using Microsoft.OData.ModelBuilder;
using ODataStudent.Data;
using ODataStudent.Models;
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
static IEdmModel GetEdmModel()
{
ODataConventionModelBuilder builder = new();
builder.EntitySet<Student>("Students");
return builder.GetEdmModel();
}
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddDbContext<StudentDataContext>(x =>
{
x.UseSqlServer(connectionString);
});
builder.Services.AddControllers().AddOData(opt => opt.AddRouteComponents("v1", GetEdmModel()).Filter().Select().OrderBy().Expand());
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new() { Title = "ODataStudents", Version = "v1" });
});
var app = builder.Build();
// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
app.UseSwagger();
app.UseSwaggerUI();
}
app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();
- File Structure:
Testing API on Postman
1) Get all the students: https://localhost:7086/v1/Students
2) Get the student by id: https://localhost:7086/v1/Students/1
3) Create the student: https://localhost:7086/v1/Students
4) Update(Patch) the student: https://localhost:7086/v1/Students/1
- Check the updation again with Get action again:
https://localhost:7086/v1/Students
5) Delete the student: https://localhost:7086/v1/Students/1
- Check the updation again with Get action again:
https://localhost:7086/v1/Students
Testing of all the OData query options like $Select, $filter, $OrderBy $expand etc.,
1) $select- return the firstnames of all students: https://localhost:7086/v1/Students?$select=FirstName
The $select system query option allows clients to request a specific set of properties for each entity or complex type. The set of properties will be comma-separated while requesting.
2) $filter- return the student whose firstname is equal to Suresh :https://localhost:7086/v1/Students?$filter=FirstName eq 'Suresh'
The $filter filters data based on a boolean condition. The following are conditional operators that have to be used in URLs.
- eq - equals to.
- ne - not equals to
- gt - greater than
- ge - greater than or equal
- lt - less than
- le - less than or equal
3) $OrderBy- returns the students Firstname in ascending order: https://localhost:7086/v1/Students?$orderby=FirstName
The $orderby sorts the data using 'asc' and 'desc' keywords. We can do sorting on multiple properties using comma separation.
- Check other query options on
https://docs.microsoft.com/en-us/aspnet/web-api/overview/odata-support-in-aspnet-web-api/supporting-odata-query-options
2. Pagination
Pagination is used to improve the web API request by breaking the result into small chunks.
It also helps to improve the response time of a request when dealing with big data set.
Scenario: There are one lakh records but the user needs only fifty records out of that. If we display all records on a single page, it would affect performance.
There are 2 ways of implementing paging:
1. Client-driven paging: Here client decides how many records are contained by the page and tell the server for page size.
A client can perform this with the use of $skip and $top keywords.
$skip- Skip the records.
$top- Retrive the specified records.
-Enable the option SetMaxTop() in Program.cs file
builder.Services.AddControllers().AddOData(opt => opt.AddRouteComponents("v1", GetEdmModel()).Filter().Select().Expand().OrderBy().SetMaxTop(25));
https://localhost:7086/v1/Students?$skip=1&$top=1
2. Server-driven paging: Server has the control on page size. At developer size pass the page size to Enablequery or quarable attribute.
- Add option pagesize = 2 in EnableQuery, it return only 2 records per page.
[EnableQuery(PageSize = 2)]
public IQueryable<Student> Get()
{
return _db.Students;
}
3. Batching
Batching enables sending an array or list of API calls and executing them as one simple API call.
Scenario: If we want to update, delete or create a bunch of records it needs a special request for each and every record individually. But with batching you can gather all that info up and send them all to the API as one simple API call and get back a response according to the requests unless there is no dependency.
- Add BatchHandler and enable the batch service.
var batchHandler = new DefaultODataBatchHandler();
builder.Services.AddControllers().AddOData(opt => opt.AddRouteComponents("v1", GetEdmModel(), batchHandler).Filter().Select().Expand().OrderBy().SetMaxTop(25));
- Enable batch middleware to pipeline before UseRouting() middleware.
app.UseODataBatching();
app.UseRouting();
Now OData service can handle $batch request.
- Post the following url to the Postman
https://localhost:7086/v1/$batch
_Here creates a Json file requesting the Get request with providing id and url. This request goes to the API and fetch the all students from API _
Request from Json file to API:
{
"requests":[
{
"id":"1",
"method": "GET",
"url": "Students",
"headers":
{
"content-type": "application/json"
}
}
]
}
Response from API:
{
"responses": [
{
"id": "1",
"status": 200,
"headers": {
"content-type": "application/json; odata.metadata=minimal; odata.streaming=true",
"odata-version": "4.0"
},
"body": {
"@odata.context": "https://localhost:7086/v1/$metadata#Students",
"value": [
{
"Id": 2,
"FirstName": "Suresh",
"LastName": "Jain"
},
{
"Id": 1002,
"FirstName": "Jayesh",
"LastName": "Shah"
}
],
"@odata.nextLink": "https://localhost:7086/v1/Students?$skip=2"
}
}
]
}
-Now we request for GET and POST actions in Json file. The API responses these 2 calls in sequence: https://localhost:7086/v1/$batch
Request from Json file to API:
{
"requests":[
{
"id":"1",
"method": "GET",
"url": "Students",
"headers":
{
"content-type": "application/json"
}
},
{
"id":"2",
"method": "POST",
"url": "Students",
"body": {
"FirstName": "Add firstname",
"LastName": "Add Lastname"
},
"headers":
{
"content-type": "application/json"
}
}
]
}
Response from API:
{
"responses": [
{
"id": "1",
"status": 200,
"headers": {
"content-type": "application/json; odata.metadata=minimal; odata.streaming=true",
"odata-version": "4.0"
},
"body": {
"@odata.context": "https://localhost:7086/v1/$metadata#Students",
"value": [
{
"Id": 2,
"FirstName": "Suresh",
"LastName": "Jain"
},
{
"Id": 1002,
"FirstName": "Jayesh",
"LastName": "Shah"
}
],
"@odata.nextLink": "https://localhost:7086/v1/Students?$skip=2"
}
},
{
"id": "2",
"status": 201,
"headers": {
"location": "https://localhost:7086/v1/Students(1005)",
"content-type": "application/json; odata.metadata=minimal; odata.streaming=true",
"odata-version": "4.0"
},
"body": {
"@odata.context": "https://localhost:7086/v1/$metadata#Students/$entity",
"Id": 1005,
"FirstName": "Add firstname",
"LastName": "Add Lastname"
}
}
]
}
Read more from docs.microsoft.com
Repository
You can check for the source code here.
ODATA-Student-API/ODataStudent
Keep Learning!
Thank You
Hope this helps, feel free to share your ideas or comment on this article and let me know your thoughts or if you have any questions!
Top comments (5)
Thank you so much. This is useful explantion. Keep it up
Keep it up💥💥
Hi.
Really great howto. Thanks for that.
But i have a problem when i try to use the batching.
When i send my request:
POST localhost:49191/odata/$batch HTTP/1.1
Content-Type: application/json
{
"requests": [
{
"id": "1",
"method": "GET",
"url": "Systems",
"headers":
{
"content-type": "application/json"
}
}
]
}
I get the response:
{
"responses": [
{
"id": "1",
"status": 404,
"headers": {}
}
]
}
And that is at all requests in a batch. I can request every single request like navigating to "Systems" and get all systems, but in batch it says 404 for the single requests. The response for the whole batch is a 200 ok with 404 in every "subrequest"
Do you have an idea?
With kind regards
Kai
I think you missed the config for OData batching
Is versioning possible with Odata?