How to implement search filter in ASP.NET Core Web API

Question

In my ASP.NET Core-6 Web API, I have these two models:

public class Department
{
    public int Id { get; set; }
    public string DepartmentName { get; set; }
}

public class Employee
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string StaffNumber { get; set; }

    public int DepartmentId { get; set; }
    public Department Department { get; set; }
}

In order to be able to do pagination, I have these classes:

public class Result
{
    internal Result(bool succeeded, IEnumerable<string> errors)
    {
        Succeeded = succeeded;
        Errors = errors.ToArray();
    }

    public bool Succeeded { get; set; }
    public string[] Errors { get; set; }

    public static Result Success()
    {
        return new Result(true, new string[] { });
    }

    public static Result Failure(IEnumerable<string> errors)
    {
        return new Result(false, errors);
    }
}

public class PaginatedList<T>
{
    public List<T> Items { get; }
    public int PageIndex { get; }
    public int TotalPages { get; }
    public int TotalCount { get; }

    public PaginatedList(List<T> items, int count, int pageIndex, int pageSize)
    {
        PageIndex = pageIndex;
        TotalPages = (int)Math.Ceiling(count / (double)pageSize);
        TotalCount = count;
        Items = items;
    }

    public bool HasPreviousPage => PageIndex > 1;
    public bool HasNextPage => PageIndex < TotalPages;

    public static async Task<PaginatedList<T>> CreateAsync(IQueryable<T> source, int pageIndex, int pageSize, CancellationToken cancellationToken)
    {
        var count = await source.CountAsync(cancellationToken);
        var items = await source.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync(cancellationToken);
        return new PaginatedList<T>(items, count, pageIndex, pageSize);
    }
}

I am using Entity Framework, and this is the final code:

public class GetAllEmployeesWithPaginationQuery : IRequestWrapper<PaginatedList<EmployeeDto>>
{
    public int DepartmentId { get; set; }
    public int PageNumber { get; set; } = 1;
    public int PageSize { get; set; } = 10;
}

public class GetAllEmployeesWithPaginationQueryHandler : IRequestHandlerWrapper<GetAllEmployeesWithPaginationQuery, PaginatedList<EmployeeDto>>
{
    private readonly IApplicationDbContext _context;
    private readonly IMapper _mapper;

    public GetAllEmployeesWithPaginationQueryHandler(IApplicationDbContext context, IMapper mapper)
    {
        _context = context;
        _mapper = mapper;
    }

    public async Task<ServiceResult<PaginatedList<EmployeeDto>>> Handle(GetAllEmployeesWithPaginationQuery request, CancellationToken cancellationToken)
    {
        PaginatedList<EmployeeDto> list = await _context.Employees
            .Where(x => x.DepartmentId == request.DepartmentId)
            .OrderBy(o => o.Name)
            .ProjectToType<EmployeeDto>(_mapper.Config)
            .PaginatedListAsync(request.PageNumber, request.PageSize, cancellationToken);

        return list.Items.Any() ? ServiceResult.Success(list) : ServiceResult.Failed<PaginatedList<EmployeeDto>>(ServiceError.NotFound);
    }
}

I have successfully done the pagination, but needs to do the sorting and search filter using the following fields: FirstName, LastName, StaffNumber and DepartmentName.

How do I achieve this?

Thank you.

Zee · Accepted Answer · 2022-03-19 01:40:39Z

1

Declare a QueryObject class, this class maps your incoming query

For example /api/SearchEmployees?firstName=mark&sortBy=name&isSortAscending=true&page=2&pageSize=10 will be mapped to this class

public class QueryObject
{
        public int? Id { get; set; }

        public string FirstName { get; set; }

       public string LastName {get;set;}

        public string Abbr { get; set; }

        public bool IsSortAscending { get; set; }

        public string SortBy { get; set; }

        public int Page { get; set; }

        public int? PageSize { get; set; }
}

//In you controller do something like this:

public List<Employee> GetEmployee(QueryObject query)
{
    var employees = _context.Employees.AsQueryable();

    if(!string.IsNullOrEmpty(query.FirstName))
    {
        employees = employees.Where(e => e.Name.Contains(query.Name));
    }

 var ColumnsMap = new Dictionary<string, Expression<Func<Province, object>>>
            {
                ["name"] = c => c.Name,
                ["abbr"] = c => c.
            };
      employees = employees.ApplyOrdering(query, ColumnsMap);

     //Do paging as you have done earlier

     return employees.ToList();
    }

    //This function orders based on the key and Expression Function You pass

        public static IQueryable<T> ApplyOrdering<T>(this IQueryable<T> query, QueryObject queryObj, Dictionary<string, Expression<Func<T, object>>> columnsMap)
        {
            if (string.IsNullOrWhiteSpace(queryObj.SortBy) || !columnsMap.ContainsKey(queryObj.SortBy))
                return query;

            return queryObj.IsSortAscending ? query.OrderBy(columnsMap[queryObj.SortBy]) : query.OrderByDescending(columnsMap[queryObj.SortBy]);
        }

edited Mar 19, 2022 at 1:40

answered Mar 19, 2022 at 1:31

Zee

6424 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

midowu Over a year ago

What about if I want to have something that can be re-used in other controllers and models. For example, Department, Leave, Salary and so on

Zee Over a year ago

Create an interface like IQueryObject and put all the common properties like page, pagesize, sortby, Department, leave, salary, etc. For your special query like employee, inherit a CustomerQueryObject from IQueryObject and customize it

Collectives™ on Stack Overflow

How to implement search filter in ASP.NET Core Web API

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related