Exposing a queryable api with Odata
Tags: dotnet, odata, webdev, csharp
Published: 2019-06-01 19:54:59


**tl;dr:** Odata provides a nice, conventional, powerful and easy-to-setup api. This post is a walkthrough to expose a readonly model.

We will

1. Initialize a new web api project
2. Add Odata for ASP.NET Core and also Microsofts api versioning library
3. In the `Startup.cs` class configure services and the middleware pipeline to enable Odata
4. Add a sample model and Odata controller
5. Implement `IModelConfiguration` to configure the Odata model
6. Execute some queries against the Odata controller

The resulting source code is available on github:

```
git clone https://github.com/jannikbuschke/pragmatic-cqrs-with-asp-net-core-for-spas
```

---

## Introduction

This is the first post of a mini series about creating an opinionated ASP.NET Core api with the help of Odata (among others).

Odata is a _conventional_ restful api, meaning that some query operations like `top, skip, select, order by` and `filter` (typical sql-like operations) are standardized as part of the api. Odata also provides conventions for _commands_ (if we think in terms of `CQRS`), which I will leave out, as in my experience this part is overly complex to use and the normal webapi is powerfull and easily adjusted/modified to provide custom conventions.

This post is a walk-through to setup a simple queryable (readonly) api which in my opinion gives the most "bang for your buck".

### Pragmatic CQRS with ASP.NET Core and SPAs - Series overview

1. Expose a queryable api with Odata (this)
2. [Enhance ASP.NET Core api controllers with Odata](https://www.jannikbuschke.de/blog/webapi-enable-query/)
3. [Expose ASP.NET Core validation for clients](https://www.jannikbuschke.de/blog/expose-aspnetcore-validation/)
4. Create an opinionated/conventional api optimized for (SPA) forms (composing above approaches)

-----
## Project setup
Lets create our project and add Odata as well as mvc- and odata-versioning.

```
dotnet new webapi --name MyApp
cd MyApp
dotnet package add Microsoft.AspNetCore.Odata
dotnet package add Microsoft.AspNetCore.Mvc.Versioning
dotnet package add Microsoft.AspNetCore.OData.Versioning
```

Our .csproj file now should look something like this:

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

  <PropertyGroup>
    <TargetFramework>netcoreapp2.2</TargetFramework>
    <AspNetCoreHostingModel>InProcess</AspNetCoreHostingModel>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.AspNetCore.App" />
    <PackageReference Include="Microsoft.AspNetCore.Odata" Version="7.1.0" />
    <PackageReference Include="Microsoft.AspNetCore.Mvc.Versioning" Version="3.1.2" />
    <PackageReference Include="Microsoft.AspNetCore.OData.Versioning" Version="3.1.0" />
    <PackageReference Include="Microsoft.AspNetCore.Razor.Design" Version="2.2.0" PrivateAssets="All" />
  </ItemGroup>

</Project>
```

Add Odata and ApiVersioning services in the `ConfigureServices` method (Startup.cs):

```csharp
public void ConfigureServices(IServiceCollection services)
{
    services.AddMvcCore(options =>
    {
        options.EnableEndpointRouting = false;
    });

    services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
    services.AddApiVersioning();
    services.AddOData().EnableApiVersioning();
}
```
Also add Odata routes in the `Configure` method (Startup.cs). The `Configure` method needs a `VersionedODataModelBuilder` as an an additional parameter:

```csharp
public void Configure(
    IApplicationBuilder app,
    IHostingEnvironment env,
    VersionedODataModelBuilder modelBuilder
)
{
    if (env.IsDevelopment())
    {
        app.UseDeveloperExceptionPage();
    }
    else
    {
        app.UseHsts();
    }

    app.UseHttpsRedirection();
    app.UseMvc(builder =>
    {
        builder.Select().Expand().Filter().OrderBy().Count();
        builder.MapVersionedODataRoutes("odata", "odata", modelBuilder.GetEdmModels());
    });
}
```

## Adding a Model, Configuration and Controller

Next create a model (Person.cs):

```csharp
public class Person
{
    public Guid Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}
```

and the Odata configuration (OdataModelConfigurations.cs)

```csharp
public class OdataModelConfigurations : IModelConfiguration
{
    public void Apply(ODataModelBuilder builder, ApiVersion apiVersion)
    {
        builder.EntitySet<Person>("Persons");
    }
}
```

Finally lets create a controller with sample data created on the fly (PersonsController.cs):

```csharp
public class PersonsController : ODataController
{
    [EnableQuery]
    public IQueryable<Person> Get()
    {
        return new string[]
            { "Alice", "Bob", "Chloe", "Dorothy", "Emma", "Fabian", "Garry", "Hannah", "Julian" }
            .Select(v => new Person { FirstName = v, Id = Guid.NewGuid(), Age = new Random().Next(80) })
            .AsQueryable();
    }
}
```

If you have experience with `Entity Framework Core` you could just return a `DbSet<T>` (or `DbQuery<T>`) property of your `DbContext` implemenenation in above `Get()` method.

## Run and test

Now that we have a controller we can run the project and execute queries in a browser:
```
dotnet run
```

open `https://localhost:5001/odata/Persons?api-version=1.0` in a browser (or `http://localhost:5000/...`).

You should get the full resultset in json format.

Some parameters that can be tried now:

```
/odata/Persons?api-version=1.0&$top=5                                   // first page with pagesize 5
/odata/Persons?api-version=1.0&$top=5&$skip=5                           // second page
/odata/Persons?api-version=1.0&$orderby=age                             // order by age ascending (young to old)
/odata/Persons?api-version=1.0&$orderby=age desc                        // order by age descending
/odata/Persons?api-version=1.0&$filter=startswith(firstName,'G')        // only show Persons whos firstname starts with 'G'
/odata/Persons?api-version=1.0&$filter=contains(firstName,'e')          // only show Persons whos firstName contains an 'e'
/odata/Persons?api-version=1.0&$filter=age gt 20 and age lt 50          // only show Persons older than 20 and younger than 50
```