DEV Community: Jannik Buschke

Make ASP.NET Core api controller methods queryable with a single attribute

Jannik Buschke — Tue, 11 Jun 2019 22:24:24 +0000

tl;dr: In this post we are going to apply the [EnableQuery] attribute to a web api controller method representing a GET endpoint. This enables clients to query the underlying data source. Pagination, search/filtering, ordering and selecting is enabled by a single attribute!

Sample code is available on github:

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

This is the second post of a mini series about creating an opinionated and standardized ASP.NET Core api.

Series overview

Getting started with Odata: Expose a queryable api
▶️️️ Enhance ASP.NET Core api controllers with Odata
Expose ASP.NET Core validation for clients
CQRS with MediatR (Commands) and Odata (Queries)

Applying [EnableQuery]

In order to make us of the [EnableQuery] attribute, we first need to configure Odata as described in Step 1 of this series.

Afterwards it's trivial to enable often needed querying properties: we just apply the attribute to a GET method of a web api controller:

using Microsoft.AspNet.OData;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Linq;

namespace Sample
{
    [Route("api/[controller]")]
    public class PersonsController : ControllerBase
    {
        [EnableQuery]
        [HttpGet]
        public IQueryable<Person> Get()
        {
            return new string[] { "Alice", "Bob", "Chloe", "Dorothy", "Emma", "Fabian", "Garry", "Hannah" }
                .Select(v => new Person { Name = v, Id = Guid.NewGuid() })
                .AsQueryable();
        }

        public class Person
        {
            public Guid Id { get; set; }
            public string Name { get; set; }
        }
    }
}

With the above code the GET endpoint /api/Persons accepts several Odata query parameters: $take, $skip, $orderby, $filter and $select are supported. $count and $expand seem to only work in a real Odata controller.

Examples:

/api/Persons?$top=5               // first page with pagesize 5
/api/Persons?$top=5&$skip=5       // second page
/api/Persons?$orderby=name        // order alphabetically
/api/Persons?$orderby=name desc   // order alphabetically reversed

Integration with Entity Framework

Note that instead of providing an inmemory collection and calling AsQueryable() on it, we could just return a DbSet<T> provided by Entity Framework. Odata will apply the parameters to any IQueryable<T> and Entity Framework is in many cases able to create perfectly reasonable, clean and performant SQL. Actually in the past year I didn't run into any situations where I would have needed any more optimized micro ORMs like Dapper. Entity Framework, since v2.1, also allows to write raw SQL statements https://docs.microsoft.com/en-us/ef/core/querying/raw-sql, which works perfectly fine with Odata (in this case a SQL subselect is generated).

Adding custom query parameters

If the Odata parameters do not fulfill our needs we can introduce custom query parameters:

[EnableQuery]
[HttpGet]
public IQueryable<Person> Get(string search = "")
{
    return new string[] { "Alice", "Bob", "Chloe", "Dorothy", "Emma", "Fabian", "Garry", "Hannah", "Julian" }
        .Select(v => new Person { Name = v, Id = Guid.NewGuid() })
        .AsQueryable()
        .Contains(v => v.Name.Contains(search));
}

/api/Persons?$orderby=name&search=a

The custom search implementation is actually already provided by Odata $filter=contains(name,'a'). However some more complex filters are easier implemented with custom parameters.

Summary

And thats it. As we have seen, once Odata is configured we only need one attribute to enable very useful query capabilities.

Exposing a queryable api with Odata

Jannik Buschke — Sat, 01 Jun 2019 19:54:59 +0000

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

Initialize a new web api project
Add Odata for ASP.NET Core and also Microsofts api versioning library
In the Startup.cs class configure services and the middleware pipeline to enable Odata
Add a sample model and Odata controller
Implement IModelConfiguration to configure the Odata model
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

Expose a queryable api with Odata (this)
Enhance ASP.NET Core api controllers with Odata
Expose ASP.NET Core validation for clients
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:

<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):

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:

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):

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

and the Odata configuration (OdataModelConfigurations.cs)

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):

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