Tag Archives: OData

OData as a flexible data feed for React search

In this post, I’d like to show you a scenario, where OData makes perfect sense. This will be a React application with .Net Core 3.1 back-end with just a single endpoint serving filtered results.

What is OData

OData is a convention of building REST-ful interfaces, that define how resources should be exposed and how should be handled. In this url-based convention you can not only fetch data but also:

  • filter by one or more properties
  • select only those properties, that you need
  • fetch nested properties
  • take only top X entities
  • and more

How those urls could look like? 

  • Order products by rating
https://services.odata.org/OData/OData.svc/Products?$orderby=Rating asc
  • Getting the second page of 30 products
https://services.odata.org/OData/OData.svc/Products?$skip=30&$top=30
  • Selecting only Price and Name of a product
https://services.odata.org/OData/OData.svc/Products?$select=Price,Name

More examples can be found here (although it is an older version of the convention) – https://www.odata.org/documentation/odata-version-2-0/uri-conventions/

Create a project from a template

I created an application from a template from Visual Studio. This is a Web Api with React on the front-end.

It will work beautifully, right after you run it. You should see something like this:

This is the page that we will modify later.

I added an Entity Framework Core with NuGet packages:

  • Microsoft.EntityFrameworkCore
  • Microsoft.EntityFrameworkCore.Design
  • Microsoft.EntityFrameworkCore.SqlServer

Then created an aspnetcoreContext:

public partial class aspnetcoreContext : DbContext
{
    public aspnetcoreContext(DbContextOptions<aspnetcoreContext> options)
        : base(options)
    {
    }

    public virtual DbSet<Profile> Profiles { get; set; }
}

And Profiles class:

public partial class Profile
{
    public Guid Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string UserName { get; set; }
    public string Email { get; set; }
    public string Street { get; set; }
    public string City { get; set; }
    public string ZipCode { get; set; }
    public string Country { get; set; }
    public string PhoneNumber { get; set; }
    public string Website { get; set; }
    public string CompanyName { get; set; }
    public string Notes { get; set; }
}

Then with command:

dotnet ef migrations add InitMigration

I generated a EF Core migration to add a Profiles table to my database. Last missing piece is to run database upgrade at program startup to execute those migrations. So in Startup class I added:

private void UpgradeDatabase(IApplicationBuilder app)
{
    using (var serviceScope = app.ApplicationServices.CreateScope())
    {
        var context = serviceScope.ServiceProvider.GetService<aspnetcoreContext>();
        if (context != null && context.Database != null)
        {
            context.Database.Migrate();
        }
    }
}

And as the last instruction in the Configure method, I added:

UpgradeDatabase(app);

Simple, right? With very little work we created a Profiles table with EF Core migrations mechanism. This way creating a DB is a part of program start, so apart from providing a connection string, there is no need to do anything else to start this app.

You can check the full project in this GitHub repo.

Let’s start with building a OData endpoint

There are only a few lines that we need to add to the Startup class to make OData work.

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews(mvcOptions =>
        mvcOptions.EnableEndpointRouting = false);

    services.AddOData();

    // Entity Framework
    services.AddDbContext<aspnetcoreContext>(options =>
          options.UseSqlServer(Configuration.GetConnectionString("LocalDB")));
}

In the Configure method add:

public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
    app.UseMvc(routeBuilder =>
    {
        routeBuilder.Select().Expand().Filter().OrderBy().MaxTop(1000).Count();
        routeBuilder.MapODataServiceRoute("odata", "odata", GetEdmModel());
        routeBuilder.EnableDependencyInjection();
    });
}

This is all you need to configure OData, now let’s create a controller.

public class ProfilesController : ControllerBase
{
    private readonly aspnetcoreContext _localDbContext;

    public ProfilesController(aspnetcoreContext localDbContext)
    {
        _localDbContext = localDbContext;
    }

    [HttpGet]
    [EnableQuery()]
    public IQueryable<Profile> Get()
    {
        return _localDbContext.Profiles.AsNoTracking().AsQueryable();
    }
}

And that’s it. Now when you run your app and go to this url:

https://localhost:44310/odata

You will see the OData collection available.

And when you go to profiles and check top 50, you will see something like this:

Front-end side

React application is located in the ClientApp directory and what we need to change is in FetchData.js file.

I’m not an expert in front-end, but I managed to rewrite this part to hooks and include very simple logic to fetch data from OData endpoint.

You can check the full project in this GitHub repo.

The result

Check out how it works in this short movie. Notice how fast it is with around 500.000+ profiles. 

You probably noticed what kind of urls we fetch from the front-end. Let’s check for example this one:

https://localhost:44310/odata/profiles?$top=25&$filter=contains(FirstName,%27john%27)%20And%20contains(LastName,%27art%27)

Now, let’s run SQL Profiler and check what is called on the DB side:

Notice that I didn’t have to write this SQL, it was all generated for me.

The beauty of OData

This example showed how easy is to expose data with OData. It perfectly matches with Entity Framework Core and generates SQL for you. With a simple URL convention, you get huge possibilities of filtering and shaping the data you receive. 

From the functional point of view OData is:

  • very flexible, perfect for forms
  • can be used where API has many clients that want output in a different form
  • gives full REST-ful experience

I hope you like this post. All code can be found on my GitHub repo.

Enjoy!

 

Implementing OData in ASP.Net API

OData is a protocol that allows creating custom queries to simple REST services. Using OData query parameters you can filter, sort or transform output you’re getting to fit your needs without any implementation changes on the API side. It sounds groundbreaking and innovative and it actually is, but it’s not a new thing – Microsoft introduced it in 2007.

Is it a lot of work to introduce OData to existing API?

No! It is surprisingly easy. Let’s try it on a simple WebApi controller in a ASP.NET framework. First you need to install nuget package: Microsoft.Data.OData. Let’s say we have a such REST api controller:

public class FoldersController : ApiController
{
    private IFoldersAndFilesProvider _provider;

    public FoldersController(IFoldersAndFilesProvider provider)
    {
        _provider = provider;
    }

    [Route("api/Folders")]
    public IHttpActionResult GetFolders()
    {
        var folders = _provider.GetFolders();
        return Ok(folders);
    }
}

This is a very simple controller that returns a list of folders in a tree structure. All there is need to be done to make this endpoint OData friendly, we need to change endpoints attributes and return IQueryable result.

[Route("odata/Folders")]
[EnableQuery]
public IQueryable<Folder> GetFolders()
{
    var folders = _provider.GetFolders();
    return folders.AsQueryable();
}

And this is it! So…

Let’s see some magic

Plain old REST endpoint would return all folders, but with OData we can query that output.

http://localhost:51196/odata/Folders

Will return the same full result.

http://localhost:51196/odata/Folders?$orderby=Size

This query will sort the output by folders size.

http://localhost:51196/odata/Folders?$top=5

There is a way to return only few results.

http://localhost:51196/odata/Folders?$skip=10&$top=5

Or use for returning partial result or even paging.

http://localhost:51196/odata/Folders?$filter=Folders/all(folder: folder/Size ge 10000)

More complex query can get folders only above certain size.

http://localhost:51196/odata/Folders?$filter=Folders/all(f: f/Hidden eq false)

Or only those that are not hidden.

You can find more examples like this here.

Not only getting data

OData is perfect for querying data, but it also can be used for adding, updating, patching and deleting entities. In Visual Studio you can add a ODataController and it will prepare a controller for you with pre-generated CRUD operations that you can use.

odata-add-controller

There are good developer articles about OData here.

This post is just scratching the surface and Microsoft implementation offers a lot, but it offers only a subset of OData features. Works on this subject seems to stop a few years ago, but there’s a new hope on the horizon. Microsoft is working on OData support for .Net Core APIs. You can track progress in this gitub repository. And here you can find some guidelines how to start using this new package.

What can I use it for

OData offers query options for simple REST APIs, that would normally require a lot of developers work to handle all the cases. In my opinion OData is perfect for scenarios where you serve data for many clients that needs different data. It could be a perfect API for automatition tests, that can fetch data that they need at the moment, without hardcoding them. Also it can be a nice addon for APIs that you don’t intend to maintain actively.

All code posted here is also available in my github repo here.