C#•3y ago

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.

    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.

12 Replies

SernikOP•3y ago

sorry for my english and thanks for help!

Brady Kelly•3y ago

Post us the exact details of that "can't translate" exception please. Then, I've often found that is the result of using custom functions in your where clause like GetDepartmentName. One solution is to first query and get a dictionary of all departments for you employees collection and do a dictionary lookup instead of using GetDepartmentName

SernikOP•3y ago

The LINQ expression 'DbSet<Employee>()
          .OrderBy(e => e.EmpNo)
          .Where(e => e.EmpNo > __lastId_0)
          .Where(e => (int)e.Gender.get_Chars(0) == (int)__allowedGender_1)' could not be translated. Additional information: Translation of method 'string.get_Chars' failed. If this method can be mapped to your custom function, see
 https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable',
'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

The LINQ expression 'DbSet<Employee>()
          .OrderBy(e => e.EmpNo)
          .Where(e => e.EmpNo > __lastId_0)
          .Where(e => (int)e.Gender.get_Chars(0) == (int)__allowedGender_1)' could not be translated. Additional information: Translation of method 'string.get_Chars' failed. If this method can be mapped to your custom function, see
 https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable',
'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

It would add complexity to this method, but ill try that also somehow this can't compare two chars, but can compare two strings, so when I convert gender char to string, first filter works

Brady Kelly•3y ago

It's not failing on your GetDepartmentName, but it looks like it's failing, for the first time at least, on e.Gender[0] == allowedGender. Looks like it doesn't like indexing on a string. A possible solution, assuming GenderFilter.GetAllowedValue returns a char, is to use e.Gender == allowedGender.ToString().

SernikOP•3y ago

yeah, I did that and it works thanks

Brady Kelly•3y ago

Oh, you already found that out. It's not that it can't compare chars, but it can't use indexing on a string like e.Gender[0]

SernikOP•3y ago

im trying to do something with this now:

 Unhandled exception rendering component: The LINQ expression 'DbSet<Employee>()
          .OrderBy(e => e.EmpNo)
          .Where(e => e.EmpNo > __lastId_0)
          .Where(e => ___departmentService_1.GetDepartmentName(e.EmpNo) == __allowedDepartment_2)' could not be translated.

 Unhandled exception rendering component: The LINQ expression 'DbSet<Employee>()
          .OrderBy(e => e.EmpNo)
          .Where(e => e.EmpNo > __lastId_0)
          .Where(e => ___departmentService_1.GetDepartmentName(e.EmpNo) == __allowedDepartment_2)' could not be translated.

One solution is to first query and get a dictionary of all departments for you employees collection and do a dictionary lookup instead of using GetDepartmentName

Is there any better way to do this? I don't want to store in memory, for example 20 000 salary records wait a minute, i have an idea... nevermind, it's worst linq i have ever seen and it does't even work

if (_appState.DepartmentFilter.GetAllowedValue(out var allowedDepartment))
        {
            employees = employees.Where(e =>
                (e.DeptEmps.MaxBy(de => de.ToDate) != null &&
                 e.DeptEmps.MaxBy(de => de.ToDate)!.DeptNoNavigation.DeptName == allowedDepartment)
                || (e.DeptManagers.MaxBy(dm => dm.ToDate) != null &&
                    e.DeptManagers.MaxBy(dm => dm.ToDate)!.DeptNoNavigation.DeptName == allowedDepartment));
        }

if (_appState.DepartmentFilter.GetAllowedValue(out var allowedDepartment))
        {
            employees = employees.Where(e =>
                (e.DeptEmps.MaxBy(de => de.ToDate) != null &&
                 e.DeptEmps.MaxBy(de => de.ToDate)!.DeptNoNavigation.DeptName == allowedDepartment)
                || (e.DeptManagers.MaxBy(dm => dm.ToDate) != null &&
                    e.DeptManagers.MaxBy(dm => dm.ToDate)!.DeptNoNavigation.DeptName == allowedDepartment));
        }

Brady Kelly•3y ago

You could use a subquery to get e.g. department name and trust that the database will optimize those lookups

SernikOP•3y ago

Wow, it works, thanks!

SernikOP•3y ago

query looks like this:

SernikOP•3y ago

it probably has horrible efficiency, but it works 😄

Brady Kelly•3y ago

It doesn't look too inefficient. Mush better than separate queries for departments and salaries for each employee

Gaming

Programming

Keyset pagination with filters