Make ASP.NET Core api controller methods queryable with a single attribute
Tags: dotnet, csharp
Published: 2019-06-11 22:24:24



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

1. [Getting started with Odata: Expose a queryable api](https://www.jannikbuschke.de/blog/odata-getting-started/)
2. ▶️️️ Enhance ASP.NET Core api controllers with Odata
3. [Expose ASP.NET Core validation for clients](https://www.jannikbuschke.de/blog/expose-aspnetcore-validation/)
4. [CQRS with MediatR (Commands) and Odata (Queries)](https://www.jannikbuschke.de/blog/cqrs-with-mediatr-and-odata/)

---


## Applying [EnableQuery]

In order to make us of the `[EnableQuery]` attribute, we first need to configure Odata as described in [Step 1](/odata-getting-started/) 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:

```csharp
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:

```csharp
[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.