OData with .NET 6
Tags: odata, dotnet, webapi, opensource
Published: 2021-10-02 12:20:07

In this tutorial, I will give you how to create Web API using .NET 6 and OData. We will create simple CRUD of Note App.

## Preparation

1. Install .NET 6 SDK (Currently at RC.1, but maybe after this article published, it will become stable): https://dotnet.microsoft.com/download/dotnet/6.0

2. Install PostgreSQL and Setup (Feel free to use another database provider, since we will use EF Core which support In-Memory too): https://www.postgresql.org/download/

3. Install dotnet ef tools (need to install .NET 6 SDK first): `dotnet tool install -g dotnet-ef --version 6.0.0-rc.1`


## Create Project

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

## Prepare Dependencies

1. Install OData: `dotnet add ODataTutorial package Microsoft.AspNetCore.OData`
2. Install EF Core Design: `dotnet add ODataTutorial package Microsoft.EntityFrameworkCore.Design --version 6.0.0-rc.1 --prerelease`
3. Install EF Core Tools: `dotnet add ODataTutorial package Microsoft.EntityFrameworkCore.Tools --version 6.0.0-rc.1 --prerelease`
4. Install EF Core PostgreSQL: `dotnet add ODataTutorial package Npgsql.EntityFrameworkCore.PostgreSQL --version 6.0.0-rc.1 --prerelease`
5. Also install `Swashbuckle.AspNetCore.Swagger` : `dotnet add ODataTutorial package Swashbuckle.AspNetCore.Swagger --version 6.4.0`
6. Install `Swashbuckle.AspNetCore.SwaggerGen` : `dotnet add ODataTutorial package Swashbuckle.AspNetCore.SwaggerGen --version 6.4.0`
7. Install `Swashbuckle.AspNetCore.SwaggerUI` : `dotnet add ODataTutorial package Swashbuckle.AspNetCore.SwaggerUI --version 6.4.0`

Thanks for `hajsf` to raise the issue when setup the project.

{% embed https://github.com/bervProject/ODataTutorial/issues/27 %}

For setup dependencies, you can see this result (file: `ODataTutorial/ODataTutorial.csproj`:

```xml
<Project Sdk="Microsoft.NET.Sdk.Web">

  <PropertyGroup>
    <TargetFramework>net6.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.OData" Version="8.0.3" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.0-rc.1">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="6.0.0-rc.1">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="6.0.0-rc.1" />
    <PackageReference Include="Swashbuckle.AspNetCore" Version="6.1.5" />
    <PackageReference Include="Swashbuckle.AspNetCore.Swagger" Version="6.4.0" />
    <PackageReference Include="Swashbuckle.AspNetCore.SwaggerGen" Version="6.4.0" />
    <PackageReference Include="Swashbuckle.AspNetCore.SwaggerUI" Version="6.4.0" />
  </ItemGroup>

</Project>
```

## Connect Project to Database

* Add `ConnectionString` to `appsettings.json`. (This is only partial of file to make it short. Please change with your database settings.)

```json
{
"ConnectionStrings": {
    "Default": "Host=localhost;Username=postgres;Password=;Database=odatatutorial"
  }
}
```

* Add Note Entity. `ODataTutorial/Entities/Note.cs`

```csharp
using System.ComponentModel.DataAnnotations;

namespace ODataTutorial.Entities;

public class Note
{
    public Guid Id { get; set; }
    [Required]
    public string MessageNote { get; set; } = default!;
}
```

* Add Note DbContext. `ODataTutorial/EntityFramework/NoteAppContext.cs`

```csharp
using Microsoft.EntityFrameworkCore;
using ODataTutorial.Entities;

namespace ODataTutorial.EntityFramework;

public class NoteAppContext : DbContext
{
    public DbSet<Note> Notes { get; set; } = default!

    public NoteAppContext(DbContextOptions<NoteAppContext> options) : base(options)
    {

    }
}
```

* Update `ODataTutorial/Program.cs` to setup the DbContext

```csharp
using Microsoft.EntityFrameworkCore;
using ODataTutorial.Entities;
using ODataTutorial.EntityFramework;
// ... all existing using if have

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddDbContext<NoteAppContext>(
    options => options.UseNpgsql(builder.Configuration.GetConnectionString("Default"))
);

// ... the rest of Program.cs
```

* Create New Migration: `dotnet ef migrations --project ODataTutorial add AddNoteTable`

* Update Database: `dotnet ef database update --project ODataTutorial`

## Prepare the API

1. Add the Controller at `ODataTutorial/Controllers/NotesController.cs`.

```csharp
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 ODataTutorial.Entities;
using ODataTutorial.EntityFramework;

namespace ODataTutorial.Controllers;

public class NotesController : ODataController
{
    private readonly NoteAppContext _db;

    private readonly ILogger<NotesController> _logger;

    public NotesController(NoteAppContext dbContext, ILogger<NotesController> logger)
    {
        _logger = logger;
        _db = dbContext;
    }

    [EnableQuery(PageSize = 15)]
    public IQueryable<Note> Get()
    {
        return _db.Notes;
    }

    [EnableQuery]
    public SingleResult<Note> Get([FromODataUri] Guid key)
    {
        var result = _db.Notes.Where(c => c.Id == key);
        return SingleResult.Create(result);
    }

    [EnableQuery]
    public async Task<IActionResult> Post([FromBody] Note note)
    {
        _db.Notes.Add(note);
        await _db.SaveChangesAsync();
        return Created(note);
    }

    [EnableQuery]
    public async Task<IActionResult> Patch([FromODataUri] Guid key, Delta<Note> note)
    {
        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }
        var existingNote = await _db.Notes.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);
    }

    [EnableQuery]
    public async Task<IActionResult> Delete([FromODataUri] Guid key)
    {
        var existingNote = await _db.Notes.FindAsync(key);
        if (existingNote == null)
        {
            return NotFound();
        }

        _db.Notes.Remove(existingNote);
        await _db.SaveChangesAsync();
        return StatusCode(StatusCodes.Status204NoContent);
    }

    private bool NoteExists(Guid key)
    {
        return _db.Notes.Any(p => p.Id == key);
    }
}
```

2. Update `ODataTutorial/Program.cs` to add OData Settings.

```csharp
using Microsoft.AspNetCore.OData;
using Microsoft.OData.Edm;
using Microsoft.OData.ModelBuilder;
// ... another existing using

static IEdmModel GetEdmModel()
{
    ODataConventionModelBuilder builder = new();
    builder.EntitySet<Note>("Notes");
    return builder.GetEdmModel();
}

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
// another services setup
builder.Services.AddControllers().AddOData(opt => opt.AddRouteComponents("v1", GetEdmModel()).Filter().Select().Expand());
```

The `ODataTutorial/Program.cs` will become like this.

```csharp
using Microsoft.AspNetCore.OData;
using Microsoft.EntityFrameworkCore;
using Microsoft.OData.Edm;
using Microsoft.OData.ModelBuilder;
using ODataTutorial.Entities;
using ODataTutorial.EntityFramework;

static IEdmModel GetEdmModel()
{
    ODataConventionModelBuilder builder = new();
    builder.EntitySet<Note>("Notes");
    return builder.GetEdmModel();
}

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddDbContext<NoteAppContext>(
    options => options.UseNpgsql(builder.Configuration.GetConnectionString("Default"))
);
builder.Services.AddControllers().AddOData(opt => opt.AddRouteComponents("v1", GetEdmModel()).Filter().Select().Expand());
builder.Services.AddSwaggerGen(c =>
{
    c.SwaggerDoc("v1", new() { Title = "ODataTutorial", Version = "v1" });
});

var app = builder.Build();

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "ODataTutorial v1"));
}

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();
```

## Repository

You can visit the repository in here.

{% github bervProject/ODataTutorial %}

## Test the API use Postman

* Testing POST or Create

![POST notes](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9rzswj8ps05va82ruh03.png)
 
* Testing GET

![GET](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/70dwvwxxrwiskb0qcioy.png)

![Get by Id](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3uozrdmbe874wpstctza.png)

![Get by Id with Select](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wxej1dqio6uh69cwj8yt.png)

![Get List use filter](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lusts1wo2bnnxc7smlgv.png)
 
* Testing PATCH (Update)

![PATCH](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ai1zh3qbe4c37gce2hkd.png)

![Check the Update](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hyq94spt9072bvrmujt4.png)

* Testing DELETE

![DELETE](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a1v0apr1wbn15bmrxlm4.png)
 
![GET by Id should not found](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z99tpr5090ps9ky9gu48.png)

![Should be not in the list](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8up3tlsxe0prea3ytuo8.png)

You can explore the `OData` with postman. The advantage you use `OData` you can query the list with filter, sort, etc., and it reflect to database query. You can the log for the query, as example like this (when filter with equal).

![Filter by Equal](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mcuo2uks7coeolajfkn1.png)

Happy exploring!

## Thank you

![Thank you GIF](https://media.giphy.com/media/X34JfwIifIYphb0p32/giphy.gif)

Thank you. Hope you like it, if you have another suggestion about the article/tutorial please comment in here.

Have a nice day!