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