Implement filter in asp.net core and entity framework core

Search and Filter using ASP.NET Core Web API and EF Core

Hello Everyone, Welcome to our new blog. Here we'll know how to add filters and search functionality in our ASP.NET Core Web API. This will allow us to filter or search rows from the database based on our search criteria. Before diving into the implementation, let's first understand what filtering and searching is.

What is Filtering and Searching?

• Filtering - In the context of Web API, Filtering is the mechanism of retrieving a subset of results from the datasource where each record meets a specific condition. This means that only the rows that satisfy the user-provided criteria will be returned.
• Searching - Search is the process of retriving results which matches the search criteria either fully or partially. Typically, we search for a keyword and return the set of results that match that keyword.

Implement Search And Filtering in ASP.NET Core Web API

We'll implement the search and filter functionality to our existing  EmployeeManagement project, where we've already covered How to Create a Web API using ASP.NET Core and Entity Framework Core and Pagination in ASP.NET Core Web API. We're not creating different projects for each implementation rather we're simply extending the feature to our EmployeeManagement Project. Please go through the articles to better understand the architecture.

Create a Filter model

We’ll start by creating a model class for filtering. This model will contain all the fields that we want to filter by. In this case, we want to filter employee records based on Designation, Date of Birth (DOB), and Department. Additionally, we’ll implement a search function to look up employees by their Name. Below is the filter model that includes all the fields used for filtering and searching:

public class FilterModel
{
    public string? SearchByName { get; set; }
    public string? Designation { get; set; }
    public DateTime? DobStatrtDate { get; set; }
    public DateTime? DobEndDate { get; set; }
    public string? Department { get; set; }
}

Update Controller Action method to accept Filter Parameters

To filter the records, we need to pass the filter model as query parameters. We’ll update the action method in the controller to accept the FilterModel model and pagination parameters. Below is the updated action method:

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

[FromQuery] will bind the query parameters to FilterModel, if the field matches with the query params passed in the url.

We’ve added the FilterModel as a query parameter, but the filtering logic will reside in the EmployeeService class, where the GetAllEmployees method is implemented. Therefore, we need to pass the FilterModel from the action method to the service method so that records are filtered.

Update Interface method declaration

To support filtering in the service, we need to modify the method signature in the IEmployeeService interface to accept the FilterModel:

Task<PaginatedListModel<EmployeeModel>> GetAllEmployees(int pageIndex, int pageSize, FilterModel filter);

Update the GetAllEmployee() method

Now, we’ll update the GetAllEmployees method to accept the filter model and apply the filtering logic based on the conditions provided. Here’s the updated method:

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

   if (!string.IsNullOrWhiteSpace(filter.Designation))
        employees = employees.Where(employee => employee.Designation == filter.Designation);

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

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

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

   var totalRecords = await employees.CountAsync();

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

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

This will return us the records based on the user input. Before Looking into the results, let's understand how this filter logic will work.

Understanding the filter logic

var employees = employeeDBContext.Employees.AsQueryable();

This line is crucial for enabling dynamic filter. Here AsQueryable() method is important. This method will allow the Employees Collection as Queryable object. Which means it will not immediately retrive the rows from Employees Table from database. Rather it will only build the query till all the filter condition are applied and it will retrive the rows from DB only when ToListAsync() or CountAsync() is applied to that object.

With the above line, it will retrive all the employees from DB as currently it doesn't has any filter condition.

Apply Filters to the Queryable Object

if (!string.IsNullOrWhiteSpace(filter.Designation))
    employees = employees.Where(employee=> employee.Designation == filter.Designation);

Here we've added the filter condition to our employee Queryable Object. This will add the condition to the employees object to filter the rows which matches the Designation passed.

Same logic is applied for the rest of all the conditions. We're not going through all the filter fields.

Final Execution

After all the conditions are added, we've added the following lines

var totalRecords = await employees.CountAsync();

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

The first line will return the count of total records which statisfies the filtercondtion. And the second line will return the rows which satisfies the given filter condition. Here the DB call will happen as we've used CountAsync() and ToListAsync(). This is when entity framework will send the query to get the desired result.

And in the last line we're returning the results retrived.

Testing Search and Filter Functionality Using Swagger UI

Now, the implementation is complete, let's run the project and verify the Search and Filter functionality is working as expected.

GET /api/Employees request view

Swagger View having Search and Filter Options for the Employees

We've passed the Department = IT in the query parameter. This will return only the records which belong to the IT department. Here is the response for our request:

Search and Filter API Response

Conclusion

In this blog we learnt how to implement Searching and Filter records using ASP.Net Core Web API and Entity Framework Core. We started by the concept of Search and Filter and it's importance. We then walked through creating a FilterModel model and then Updating the EmployeeController and EmployeeService. We also walked through the logic we've added for filtering in our EmployeeService class step by step. After that we verifed the API is working using Swagger.

Applying filters helps users quickly find the exact records they need, enhancing user experience by saving time and reducing the need to shift through individual records.

Thank you for following along! We hope this guide has helped you understand and implement search and filtering in your projects. If you have any questions or need further assistance, feel free to leave a comment or reach out.