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

May 12, 2019

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 Expose ASP.NET Core validation for SPAs like React, Vue or Angular →