Use OData services in ASP.NET Core

Recently Microsoft released OData Preview 8 for ASP.NET Core 5. OData or Open Data protocol is a standard for creating REST style services that offer inbuilt querying capabilities to developers. This allows for implementing paging, sorting, and reshaping of data quickly and easily. In this article I will take you through the basics of creating an OData service in ASP.NET Core 5. We will then invoke the OData service using Postman.

So, let's get going!

Begin by creating a new ASP.NET Core API project as you normally do. Make sure to pick ASP.NET Core 5 as the target framework since we will be using the newest release of NuGet packages available as of this writing.

Once the project is created, open the Manage NuGet packages page and add the following two NuGet packages (latest preview versions).

• Microsoft.AspNetCore.OData
• Microsoft.EntityFrameworkCore.SqlServer

The following figure shows these packages upon successfully adding them to the project.

Then add a folder named DataAccess in the project's root and add an entity class called Employee. The Employee class is shown below:

[Table("Employees")]
public class Employee
{
    public int EmployeeID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Title { get; set; }
}

Here, I am assuming that you have Northwind sample database installed in your SQL Server and want to use Employees table for this example. hat's not the case you need to create an entity class that maps to whatever table you want to use.

Then proceed to create a DbContext class called AppDbContext as shown below :

public class AppDbContext:DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> 
options) : base(options)
    {

    }
    public DbSet<Employee> Employees { get; set; }
}

Next, open appsettings.json file and add Northwind database connection string.

"ConnectionStrings": {
  "AppDb": "data source=.;initial catalog=Northwind;
integrated security=true"
}

Make sure to change the connection string as per your SQL Server setup.

Now, open the Startup class and add the following code in the ConfigureServices() method.

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllers();

    services.AddDbContext<AppDbContext>(o => 
o.UseSqlServer(Configuration.GetConnectionString("AppDb")));

    services.AddOData(o =>
    {
        o.AddModel("odata", GetEdmModel());
        o.Select();
        o.Filter();
        o.Expand();
        o.Filter();
        o.OrderBy();
        o.Count();
        o.MaxTop = 100;
    });
}

Notice the lines marked in bold letters. First, we register the AppDbContext with the DI container. Then we call AddOData() method. The AddOData() method specifies various OData options. More important for us is the call to AddModel() method. This method supplies an Entity Data Model or EDM for our model. The GetModel() method creates the necessary IEdmModel object for us and is shown below:

private static IEdmModel GetModel()
{
    ODataConventionModelBuilder builder = 
new ODataConventionModelBuilder();
    builder.EntitySet<Employee>("Employees");
    return builder.GetEdmModel();
}

Notice that GetModel() method is a static method and returns IEdmModel object to the caller. Inside, we use ODataConventionModelBuilder to build our model.

The AddOData() call also specifies various OData features we want to support for our service. For example, we use Select(), Filter(), and OrderBy() methods to enable the respective features. You will use these features later in this article when you test the service using Postman tool.

We aren't adding anything specific in the Configure() method. But just ensure that it resembles this :

public void Configure(IApplicationBuilder app, 
IWebHostEnvironment env)
{
    ...

    app.UseStaticFiles();

    app.UseRouting();

    app.UseAuthorization();

    app.UseEndpoints(endpoints =>
    {
        endpoints.MapControllers();
    });
}

So far so good. Now we will proceed to create an OData service that performs CRUD operations on the Employees table.

To add the OData service, add a new class under the Controllers folder named EmployeesController.

Once added, modify the class definition like this:

public class EmployeesController : ODataController
{
...
}

The EmployeesController class inherits from ODataController.

We will now inject the AppDbContext into the EmployeesController class.

private AppDbContext db;

public EmployeesController(AppDbContext db)
{
    this.db = db;
}

Now let's add the Get() actions.

[EnableQuery]
public IActionResult Get()
{
    return Ok(db.Employees);
}

[EnableQuery]
public IActionResult Get(int key)
{
    return Ok(db.Employees.FirstOrDefault
(c => c.EmployeeID == key));

}

The [EnableQuery] attribute enables OData querying for the underlying action. The second Get() action takes an integer key parameter that represents the EmployeeID whose details are to be retrieved.

Complete the remaining actions - Post(), Put(), and Delete() as shown below:

public IActionResult Post([FromBody] Employee emp)
{
    db.Employees.Add(emp);
    db.SaveChanges();
    return Created(emp);
}


public IActionResult Put(int key, 
[FromBody] Employee emp)
{
    db.Employees.Update(emp);
    db.SaveChanges();
    return NoContent();
}

public IActionResult Delete(int key)
{
    db.Employees.Remove(_db.Employees.
Find(key));
    db.SaveChanges();
    return NoContent();
}

These actions are quite straightforward and hence aren't explained here.

This completes the Employees OData service. We can now test all the CRUD operations using Postman tool.

To do so, first run the OData service project by pressing F5.

Then start the Postman tool and issue a GET request to /odata/Employees as shown below ("odata" in URL comes from AddModel() method we used in the ConfigureServices() method) :

This query will return a list of employees in JSON format:

Now let's retrieve just a single employee. Enter this URL : /odata/Employees(1) in Postman.

And it will return just the specified employee (EmployeeID = 1) :

Now, let's re-shape the data by specifying the columns that are returned.

Enter this query in the Postman URL : /odata/Employees?$Select = EmployeeID. FirstName, LastName

And you will get only three column values back:

As you can see, we haven't written any logic to select these columns in our OData service Get() action. But OData does all that work for us and returns the requested data.

Let's complete our GET queries by issuing an OrderBy request.

Enter this URL : /odata/Employees/$OrderBy = Title

And we get employees sorted on the basis of their Title values:

You can use multiple query clauses (say, $Select and $OrderBy by separating them using & character).

Next, let's add a new Employee by making a POST request.

Enter this POST URL in Postman : /odata/Employees. And specify the request's body to be the following JSON formatted Employee:

{
 "FirstName": "fname123",             
 "LastName": "lname123",             
 "Title": "Sales Representative"
}

Your Postman should resemble this:

We don't specify EmployeeID here because it's an identity column. Postman will show you the newly created employee.

On the same lines you can check PUT and DELETE requests.

Here, 100 is the EmployeeID to be updated and deleted respectively.

That's it for now! Keep coding!!