Keyset pagination with filters
Hello everyone!
I use this piece of code (probably very inefficient piece of code) to get employees from database using keyset pagination.
This code works perfectly fine unless i activate filters. I get an exception that ef core can't translate my shitty Linq to SQL and that's kinda understandable. But the question is, how can I make this work? Afaik loading all entities to list, filtering and then taking, for example 50 entities isn't a good idea.
I use this piece of code (probably very inefficient piece of code) to get employees from database using keyset pagination.
public IEnumerable<EmployeeView> GetEmployees()
{
var amount = _appState.ItemsPerPage;
var lastId = _appState.LastEmployeeId ?? 0;
var employees = _context.Employees
.AsNoTracking()
.OrderBy(e => e.EmpNo)
.Where(e => e.EmpNo > lastId);
#region filters
if (_appState.GenderFilter.GetAllowedValue(out var allowedGender))
employees = employees.Where(e =>
e.Gender[0] == allowedGender);
if (_appState.DepartmentFilter.GetAllowedValue(out var allowedDepartment))
employees = employees.Where(e =>
_departmentService.GetDepartmentName(e.EmpNo) == allowedDepartment);
if (_appState.SalaryFilter.GetAllowedValue(out var allowedSalary))
employees = employees.Where(e =>
_salaryService.GetSalaryValue(e.EmpNo) >= allowedSalary.From &&
_salaryService.GetSalaryValue(e.EmpNo) <= allowedSalary.To);
#endregion filters
employees = employees.Take(amount);
_appState.LastEmployeeId = employees.Last().EmpNo;
return employees.ToList().Select(e => new EmployeeView(
FirstName: e.FirstName,
LastName: e.LastName,
IsMale: e.Gender == "M",
DepartmentName: _departmentService.GetDepartmentName(e.EmpNo),
JobTitle: _titleService.GetTitleName(e.EmpNo),
Salary: _salaryService.GetSalaryValue(e.EmpNo)
));
} public IEnumerable<EmployeeView> GetEmployees()
{
var amount = _appState.ItemsPerPage;
var lastId = _appState.LastEmployeeId ?? 0;
var employees = _context.Employees
.AsNoTracking()
.OrderBy(e => e.EmpNo)
.Where(e => e.EmpNo > lastId);
#region filters
if (_appState.GenderFilter.GetAllowedValue(out var allowedGender))
employees = employees.Where(e =>
e.Gender[0] == allowedGender);
if (_appState.DepartmentFilter.GetAllowedValue(out var allowedDepartment))
employees = employees.Where(e =>
_departmentService.GetDepartmentName(e.EmpNo) == allowedDepartment);
if (_appState.SalaryFilter.GetAllowedValue(out var allowedSalary))
employees = employees.Where(e =>
_salaryService.GetSalaryValue(e.EmpNo) >= allowedSalary.From &&
_salaryService.GetSalaryValue(e.EmpNo) <= allowedSalary.To);
#endregion filters
employees = employees.Take(amount);
_appState.LastEmployeeId = employees.Last().EmpNo;
return employees.ToList().Select(e => new EmployeeView(
FirstName: e.FirstName,
LastName: e.LastName,
IsMale: e.Gender == "M",
DepartmentName: _departmentService.GetDepartmentName(e.EmpNo),
JobTitle: _titleService.GetTitleName(e.EmpNo),
Salary: _salaryService.GetSalaryValue(e.EmpNo)
));
}This code works perfectly fine unless i activate filters. I get an exception that ef core can't translate my shitty Linq to SQL and that's kinda understandable. But the question is, how can I make this work? Afaik loading all entities to list, filtering and then taking, for example 50 entities isn't a good idea.
