Supercharging ASP.NET 6.0 with ODATA | CRUD | Batching | Pagination
Tags: dotnet, odata, webapi, batching
Published: 2022-03-09 07:23:34

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

```

- 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:**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/82qljmuiq70b7kgd4z6t.PNG)





## Testing API on Postman

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gd5vrao9yswkquxxz21a.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1h73cfz1rbcglttj21vl.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/90509pv5gay4ojp565c5.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/odnkbt9ivglf5xtpnq21.png)

- 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6vaumdwuxhvawq89sm76.png)

- 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ljr0e72nj5lu2300y9wp.png)

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9j3be5s7n4ms09qivhg2.png)

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uqog6vyjtg8wbfc6im3y.png)

- 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`

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zhu7doho5io3rx4oi6k9.png)
   
**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;
        }
```
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/eqfbt2vzbcitts1g7b4t.png)


## 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 _


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b48130rxsw2pxqsqfzpj.png)

**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`


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9evnfmoynvikrmtc3xg5.png)

**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](https://docs.microsoft.com/en-us/odata/client/batch-operations)_

## Repository
You can check for the source code here.
[ODATA-Student-API/ODataStudent](https://github.com/renukapatil-21/ODATA-Student-API)


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!_