Sorting using ASP.NET Core Web API and EF Core

Sorting using ASP.NET Core Web API and EF Core

Hello Everyone, Welcome to the new blog. In the previous blog, we got to know how to filter data using ASP.NET Core Web API and Entity Framework Core. In this blog, we'll learn how to implement sorting using ASP.NET Core Web API and Entity Framework Core. Here we want to order our data based on the criteria. 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 add sorting functionality on the existing method, which is returning list of employees. Sorting 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 this, 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");

Add Sorting functionality on 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 =================
    employees = employees
    	.Where(x => string.IsNullOrWhiteSpace(filters.Designation) || x.Designation == filters.Designation)
    	.Where(x => string.IsNullOrWhiteSpace(filters.Department) || x.Department == filters.Department)
    	.Where(x => !filters.DobStartDate.HasValue || x.DOB >= filters.DobStartDate)
    	.Where(x => !filters.DobEndDate.HasValue || x.DOB <= filters.DobEndDate)
        .Where(x => string.IsNullOrWhiteSpace(filters.SearchKeyword) || x.Name.Contains(filters.SearchKeyword));
    //============ Filters end =================
    
    var totalRecords = await employees.CountAsync();
    
    //============ Sorting ===================

    employees = SortEmployeeRecords(employees, sortBy, sortOrder);

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

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

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

Add sortEmployeeRecords method

private IQueryable<EmployeeModel> SortEmployeeRecords(IQueryable<EmployeeModel> employees, string sortBy, string sortOrder)
{
	Expression<Func<EmployeeModel, object>> sortColumn = sortBy switch
	{
		"name" => x => x.Name,
		"dob" => x => x.DOB,
		_ => x => x.Id
	};

	if(sortOrder == "desc")
	{
		employees = employees.OrderByDescending(sortColumn);
	}
	else
	{
		employees = employees.OrderBy(sortColumn);
	}

    return employees;
}

Above we've implemented sorting logic under the private method SortEmployeeRecords(), which is returning the sorting expression based on the sortBy field, then it applies the sorting based on sort order. This implementation is simple and clean and easy to implement. However there are multiple ways we can implement sorting. The above approach is one of these.

Note: We've added totalRecods count before the sort. The reason behind this is we don't want to include sorting in count. The totalRecords count only return the no. of rows present in the table for that search criteria. Adding sorting to the count will unnecessarily add db overheads.

As we know we can implement sorting in multiple ways, if we don't want the above implementation, there is another solution using System.Linq.Dynamic.Core, which will create dynamic sorting.

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.

However, 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.

I hope you liked this information. Please share your feedback.

Thank you!