DEV Community

Cover image for Supercharging ASP.NET 6.0 with ODATA | CRUD | Batching | Pagination
Renuka Patil
Renuka Patil

Posted on • Edited on

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

  1. Install .NET 6 SDK: (https://dotnet.microsoft.com/download/dotnet/6.0)
  2. Install SQL and Setup: (https://www.microsoft.com/en-in/sql-server/sql-server-downloads)
  3. Install Postman for testing the API: https://www.postman.com/downloads/

Create Project

  1. Use this command: dotnet new webapi -o ODataStudent
  2. Create Solution: dotnet new sln
  3. Connect solution with project: dotnet sln add ODataStudent

Add Dependencies

  1. Install OData: Install-Package Microsoft.AspNetCore.OData -Version 8.0.4
  2. Install EF Core Design: Install-Package Microsoft.EntityFrameworkCore.Design -Version 6.0.0
  3. Install EF Core Tools: Install-Package Microsoft.EntityFrameworkCore.Tools -Version 6.0.0
  4. Install EF Core SQL: Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 6.0.2
  5. 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; }
    }
}
Enter fullscreen mode Exit fullscreen mode
  • 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; }
    }

}
Enter fullscreen mode Exit fullscreen mode
  • Add ConnectionString to appsettings.json(Change as per your database settings) 
{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=ODataStudent;Trusted_Connection=True;MultipleActiveResultSets=True"
  },
}
Enter fullscreen mode Exit fullscreen mode
  • Add to connectionString and StudentDataContext in Program.cs file. 
var connectionString = builder.Configuration.GetConnectionString("DefaultConnection");
builder.Services.AddDbContext<StudentDataContext>(x =>
{
    x.UseSqlServer(connectionString);
});
Enter fullscreen mode Exit fullscreen mode
  • 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();
}
Enter fullscreen mode Exit fullscreen mode

-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);
        }

    }
}
Enter fullscreen mode Exit fullscreen mode

  • 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());
Enter fullscreen mode Exit fullscreen mode
  • 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();
Enter fullscreen mode Exit fullscreen mode
  • File Structure:

Image description

Testing API on Postman

1) Get all the students: https://localhost:7086/v1/Students

Image description

2) Get the student by id: https://localhost:7086/v1/Students/1

Image description

3) Create the student: https://localhost:7086/v1/Students

Image description

4) Update(Patch) the student: https://localhost:7086/v1/Students/1

Image description

  • Check the updation again with Get action again: https://localhost:7086/v1/Students

5) Delete the student: https://localhost:7086/v1/Students/1

Image description

  • 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.

Image description

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

Image description

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.

Image description

  • 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));
Enter fullscreen mode Exit fullscreen mode
  • https://localhost:7086/v1/Students?$skip=1&$top=1

Image description

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;
        }
Enter fullscreen mode Exit fullscreen mode

Image description

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));
Enter fullscreen mode Exit fullscreen mode
  • Enable batch middleware to pipeline before UseRouting() middleware. 
app.UseODataBatching();
app.UseRouting();
Enter fullscreen mode Exit fullscreen mode

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 _

Image description

Request from Json file to API:

{
    "requests":[
        {
        "id":"1",
        "method": "GET",
        "url": "Students",
        "headers":
            {
                "content-type": "application/json"
            }
        }
    ]
}
Enter fullscreen mode Exit fullscreen mode

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"
            }
        }
    ]
}
Enter fullscreen mode Exit fullscreen mode

-Now we request for GET and POST actions in Json file. The API responses these 2 calls in sequence: https://localhost:7086/v1/$batch

Image description

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"
            }
        }
    ]
}
Enter fullscreen mode Exit fullscreen mode

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"
            }
        }
    ]
}
Enter fullscreen mode Exit fullscreen mode

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)

Collapse
 
linhvuquach profile image
linhvuquach •

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

Collapse
 
yashkotti profile image
yash-kotti •

Keep it up💥💥

Collapse
 
kaimildner profile image
KaiMildner •

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

Collapse
 
linhvuquach profile image
linhvuquach •

I think you missed the config for OData batching

Collapse
 
rakeshps profile image
Rakesh •

Is versioning possible with Odata?

Read next

dotnetfullstackdev profile image

JWT, OAuth, OpenID Connect, and Azure AD: Authentication Levels

DotNet Full Stack Dev -

moh_moh701 profile image

C# Clean Code: Coding Conventions

mohamed Tayel -

moh_moh701 profile image

C# Advanced: Anonymous Types with LINQ

mohamed Tayel -

juarezasjunior profile image

Fast and Efficient Queries with Dapper

Juarez Júnior -