Sorting in asp.net core web api and entity framework

Sorting using ASP.NET Core Web API and EF Core

Welcome to the blog! We're glad you're here. In the previous tutorial, we got to know how to filter data using ASP.NET Core Web API and Entity Framework Core. In this tutorial, we'll learn how to implement sorting using ASP.NET Core Web API and Entity Framework Core. This will order the rows based on the field. Before diving into implementation, let's first understand what sorting is.

What is Sorting?

Sorting is the process of arranging items in a particular order such as ascending or descending order. It can be applied to various data types including numbers, strings, objects, and more complex data structures. It presents the data in a more readable and organized way and structures the data in a meaningful way, which is useful in data analysis, reporting, etc.

Let's Implement Sorting in ASP.NET Core Web API

In the previous blogs, we've already discussed how to create ASP.NET Core Web API and also discussed on pagination and filtering. Here we'll continue with the same EmployeeManagement Project and will implement sorting. 

Add sorting params in IEmployeeService

We are going to apply sorting on the method, returning list of employees. It will be applied based on the fields we've in our model. 

We need to pass the field name for which we want to sort the data and also the order in which order we want to sort either in ascending or descending order. These values will come as query param from the action method.

We'll add the query params in the action method later. Before that we'll implement the Sorting logic in EmployeeService class. To achieve that, we'll add the params to the GetAllEmployees method in IEmployeeService interface. 

 Task<PaginatedListModel<EmployeeModel>> GetAllEmployees(int pageIndex, int pageSize, FilterModel filters, string sortBy = "id", string sortOrder = "desc");

Here is our GetEmployee method in the EmployeeService Class

public async Task<PaginatedListModel<EmployeeModel>> GetAllEmployees(int pageIndex, int pageSize, FilterModel filters, string sortBy = "id", string sortOrder = "desc")
{
    var employees = employeeDBContext.Employees.AsQueryable();

    //========== Filters start =================
    if (!string.IsNullOrWhiteSpace(filters.Designation))
        employees = employees.Where(filter => filter.Designation == filters.Designation);

    if (!string.IsNullOrWhiteSpace(filters.Department))
        employees = employees.Where(filter => filter.Department == filters.Department);

    if (filters.DobStartDate.HasValue)
        employees = employees.Where(filter => filter.DOB >= filters.DobStartDate);

    if (filters.DobEndDate.HasValue)
        employees = employees.Where(filter => filter.DOB <= filters.DobEndDate);

    if (!string.IsNullOrWhiteSpace(filters.SearchKeyword))
        employees = employees.Where(filter => filter.Name.Contains(filters.SearchKeyword));

    //============ Filters end =================

    //============ Sorting ===================

    employees = SortEmployeeRecords(employees, sortBy, sortOrder);

    //=========== Sorting end ===============

    var totalRecords = await employees.CountAsync();

    var employeesList = await employees.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync();

    return new PaginatedListModel<EmployeeModel>(employeesList, totalRecords, pageIndex, pageSize);
}

Here is sortEmployeeRecords looks like

private IQueryable<EmployeeModel> SortEmployeeRecords(IQueryable<EmployeeModel> employees, string sortBy, string sortOrder)
{
    if (sortBy == "name")
    {
        if (sortOrder == "desc")
            employees = employees.OrderByDescending(employee => employee.Name);
        else
            employees = employees.OrderBy(employee => employee.Name);
    }
    else if (sortBy == "dob")
    {
        if (sortOrder == "desc")
            employees = employees.OrderByDescending(employee => employee.DOB);
        else
            employees = employees.OrderBy(employee => employee.DOB);
    }
    else if (sortBy == "id")
    {
        if (sortOrder == "desc")
            employees = employees.OrderByDescending(employee => employee.Id);
        else
            employees = employees.OrderBy(employee => employee.Id);
    }
    else
        throw new InvalidOperationException();

    return employees;
}

Above we've implemented sorting logic under the private method SortEmployeeRecords(), which is checking the sortBy field and if the the field matches, then it applies the sorting based on sort order. This design is good for fewer fields i.e. when we have less fields to in the result or the requirement is to sort on one or two field. 

But, this this will be mess when we've large no of field in our result and we need sort on almost all the fields, then this will increase as the no of fields increase and also will be error prone when adding multiple fields.

To overcome, there is a better solution using System.Linq.Dynamic.Core.

Implement sorting using System.Linq.Dynamic.Core

Add NuGet Package

Add the System.Linq.Dynamic.Core into our project using NuGet Package Manager.

Update the GetEmployeeMethod() in EmployeeService

First of all, remove the method call

SortEmployeeRecords(employees, sortBy, sortOrder)

from GetEmployee method as we're going to use dynamic sorting using DynamicCore.

Now, add the below line where the SortEmployeeRecords was getting called.

employees = employees.OrderBy($"{sortBy} {sortOrder}");

We no longer need the SortEmployeeRecords method at all. The above line will handle sorting for all the fields using DynamicCore. It will internally create the expression tree and apply the OrderBy logic. This will be efficient and less error prone when working with large no. of columns.

Update the Get() action method in controller

// GET: api/<EmployeesController>
[HttpGet]
public async Task<IActionResult> Get([FromQuery] FilterModel filters, int pageIndex = 1, int pageSize = 10, string sortBy = "id", string sortOrder = "desc")
{
    var results = await _employeeService.GetAllEmployees(pageIndex, pageSize, filters, sortBy, sortOrder);
    return Ok(results);
}

Our sorting logic is implemented on the controller method which is returning list of emplyees, It will take sortBy and sortOrder as query param. So, we've to pass these values to that method and based on that it will sort and order the results. Logic is handled by the service method. 

By, default it will pass id as sortBy and desc as sort order to the service method. 

 That is all we need for sorting. 

Conclusion 

In this blog, we got to know about the different approaches for sorting. We've to make the choice that which approach we need. If we have less no of fields present for sorting then the first approach is better as it is simple. But, dynamic sorting, the second approach has more option. We can also apply multiple sorting using the second approach. 

We also want to say that, sorting is not limited to only these approaches. We can apply sorting using multiple different approaches. We can add dynamic sorting to our project easily using the above steps.

Howerver, we haven't added any validation for sorting like the sortBy and sortOrder is valid or not. But, you must take care of those validations in your project. This will prevent the unnecessary database call returning query error.

 

Out of all, we found these two approaches are best and simple. We can also implement dynamic sorting without the use of System.Linq.DynamicCore library. We'll surely discuss that in future.

If you like the post, kindly share this to your friends and family who is learning these technology or want to know. 

Thank you so much for your time. We'll meet again with the next blog. Till then Bye Bye!