Renuka Patil

Posted on Mar 9, 2022 • Edited on Mar 20, 2022

Supercharging ASP.NET 6.0 with ODATA | CRUD | Batching | Pagination

#dotnet #odata #webapi #batching

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 create Student.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 in StudentDataContext.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 to appsettings.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)

linhvuquach • Jul 21 '22

Thank you so much. This is useful explantion. Keep it up

yash-kotti • Mar 20 '22

Keep it up💥💥

KaiMildner • May 17 '22

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

linhvuquach • Jul 21 '22

I think you missed the config for OData batching

Rakesh • Sep 28 '23

Is versioning possible with Odata?

DEV Community