ASP.NET Core IQueryable Extensions

Lightweight API that construct custom IQueryable LINQ Extensions to help you filter, sort and paginate your objects from a custom Class and expose it as GET parameter.

Table of Contents

• ASP.NET Core IQueryable Extensions
  • Table of Contents
• How
• Sort
• Paging
• All in One
• Criterias for filtering
• Different database fields name
• Or Operator
• Why
• License

---

How

You should install AspNetCore.IQueryable.Extensions with NuGet:

    Install-Package AspNetCore.IQueryable.Extensions

Or via the .NET Core command line interface:

    dotnet add package AspNetCore.IQueryable.Extensions

Create a class with filtering properties:

public class UserSearch
{
    public string Username { get; set; }

    [QueryOperator(Operator = WhereOperator.GreaterThan)]
    public DateTime? Birthday { get; set; }

    [QueryOperator(Operator = WhereOperator.Contains, HasName = "Firstname")]
    public string Name { get; set; }
}

Expose this class as GET in your API and use it to Filter your collection:

[HttpGet("")]
public async Task<ActionResult<IEnumerable<User>>> Get([FromQuery] UserSearch search)
{
    var result = await context.Users.AsQueryable().Filter(search).ToListAsync();

    return Ok(result);
}

Done! You can send a request to you API like this: https://www.myapi.com/users?username=bhdebrito@gmail.com&name=bruno

The component will construct a IQueryable. If you are using an ORM like EF Core it construct a SQL query based in IQueryable, improving performance.

Sort

A comma separetd fields. E.g username,birthday,-firstname

-(minus) for descending +(plus) or nothing for ascending

public class UserSearch
{
    public string Username { get; set; }

    public string SortBy { get; set; }
}

[HttpGet("")]
public async Task<ActionResult<IEnumerable<User>>> Get([FromQuery] UserSearch search)
{
    var result = await context.Users.AsQueryable().Filter(search).Sort(search.SortBy).ToListAsync();

    return Ok(result);
}

Example GET: https://www.myapi.com/users?username=bruno&sortby=username,-birtday

Paging

A exclusive extension for paging

public class UserSearch
{
    public string Username { get; set; }

    [QueryOperator(Max = 100)]
    public int Limit { get; set; } = 10;

    public int Offset { get; set; } = 0;
}

Limit is the total results in response. Offset is how many rows to Skip. Optionally you can set the Max attribute to restrict the max items of pagination.

[HttpGet("")]
public async Task<ActionResult<IEnumerable<User>>> Get([FromQuery] UserSearch search)
{
    var result = await context.Users.AsQueryable().Filter(search).Paging(search.Limit, search.Offset).ToListAsync();

    return Ok(result);
}

Example GET: https://www.myapi.com/users?username=bruno&limit=10&offset=20

All in One

Create a search class like this

public class UserSearch : IQuerySort, IQueryPaging
{
    public string Username { get; set; }

    [QueryOperator(Operator = WhereOperator.GreaterThan)]
    public DateTime? Birthday { get; set; }

    [QueryOperator(Operator = WhereOperator.Contains, HasName = "Firstname")]
    public string Name { get; set; }

    public int Offset { get; set; }
    public int Limit { get; set; } = 10;
    public string Sort { get; set; }
}

Call Apply method, instead calling each one with custom parameters.

[HttpGet("")]
public async Task<ActionResult<IEnumerable<User>>> Get([FromQuery] UserSearch search)
{
    var result = await context.Users.AsQueryable().Apply(search).ToListAsync();

    return Ok(result);
}

IQuerySort and IQueryPaging give the ability for method Apply use Sort and Pagination. If don't wanna sort, just use pagination remove IQuerySort Interface from Class.

Criterias for filtering

When creating a Search class, you can define criterias by decorating your properties:

public class CustomUserSearch
{
    [QueryOperator(Operator = WhereOperator.Equals, UseNot = true)]
    public string Category { get; set; }

    [QueryOperator(Operator = WhereOperator.GreaterThanOrEqualTo)]
    public int OlderThan { get; set; }

    [QueryOperator(Operator = WhereOperator.StartsWith, CaseSensitive = true)]
    public string Username { get; set; }

    [QueryOperator(Operator = WhereOperator.GreaterThan)]
    public DateTime? Birthday { get; set; }

    [QueryOperator(Operator = WhereOperator.Contains)]
    public string Name { get; set; }
}

Different database fields name

You can specify different property name to hide you properties original fields

public class CustomUserSearch
{
    [QueryOperator(Operator = WhereOperator.Equals, UseNot = true, HasName = "Privilege")]
    public string Category { get; set; }

    [QueryOperator(Operator = WhereOperator.GreaterThanOrEqualTo)]
    public int OlderThan { get; set; }

    [QueryOperator(Operator = WhereOperator.StartsWith, CaseSensitive = true, HasName = "Username")]
    public string Email { get; set; }
}

Or Operator

You can use Or operator for your queries.

public class CustomUserSearch
{
    [QueryOperator(Operator = WhereOperator.Equals, UseOr = true]
    public string Category { get; set; }

    [QueryOperator(Operator = WhereOperator.GreaterThanOrEqualTo)]
    public int OlderThan { get; set; }

    [QueryOperator(Operator = WhereOperator.StartsWith, CaseSensitive = true, HasName = "Username")]
    public string Email { get; set; }
}

Take care, Or replace all "AND" at query.

Why

RESTFul api's are hard to create. See the example get:

https://www.myapi.com/users?name=bruno&age_lessthan=30&sortby=name,-age&limit=20&offset=20

How many code you need to perform such search? A custom filter for each Field, maybe a for and a switch for each sortby and after all apply pagination. How many resources your api have?

This lightweight API create a custom IQueryable based in Querystring to help your ORM or LINQ to filter data.

---

License

AspNet.Core.IQueryable.Extensions is Open Source software and is released under the MIT license. This license allow the use of AspNet.Core.IQueryable.Extensions in free and commercial applications and libraries without restrictions.