如何在ASP.NET核心Web API中实现搜索过滤器

Question

在我的ASP.NET Core-6 Web API中，我有以下两个模型：

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; }
}

为了能够进行分页，我有以下类：

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);
}
}

我正在使用实体框架，这是最后的代码：

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);
}
}

我已经成功地完成了分页，但需要使用以下字段进行排序和搜索筛选：FirstName、LastName、StaffNumber和DepartmentName。

我该如何做到这一点？

谢谢。

Answer 1

声明一个QueryObject类，该类映射传入的查询

例如，/api/SearchEmployees?firstName=mark&sortBy=name&isSortAscending=true&page=2&pageSize=10将映射到此类

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; }
}

//在你的控制器中做这样的事情：

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]);
}