.net core linq select overload index does not work-CodePudding

I got a linq lambda select code that works before I added the Select index overload. Before, I got the list of records but I need the index which I use to assign a unique Id to each record. When I add with ToList(), I get an exception with no error/inner exception. Only way I can get the code to not throw an error is to use .AsEnumberable() but I need a list. I read many post that .ToList() works with the overload but I have been unsuccessful.

Here is my code and my attempt to fix this

  var emps = this.DbContext.Employees
                    .GroupJoin(this.DbContext.Depts,
                        employee => employee.EmployeeId,
                        dept => dept.EmployeeId,
                        (employee, dept) => new { employee, dept }
                    )
                    .SelectMany(
                        employee_dept_left => employee_dept_left.dept.DefaultIfEmpty(),
                        (employee_dept_left, dept) => new { employee_dept_left, dept }
                    )
                    .Join(this.DbContext.Divs,
                        emp_emp_dept => emp_emp_dept.employee_dept_left.employee.DivId,
                        division => division.DivId,
                        (emp_emp_dept, division) => new { emp_emp_dept, division }
                    )
                    .Where(s => !string.IsNullOrEmpty(filter.selectedDiv))
                    .GroupBy(grouped => new
                    {
                        grouped.emp_emp_dept.employee_dept_left.employee.EmployeeId,
                        grouped.emp_emp_dept.employee_dept_left.employee.LastNm,
                        grouped.emp_emp_dept.employee_dept_left.employee.FirstNm,
                        grouped.emp_emp_dept.employee_dept_left.employee.DivId
                    })
                    .Select((joined, index) => new EmployeeViewModel
                    {
                        Id = index,
                        EmployeeId = joined.Key.EmployeeId,
                        LastNm = joined.Key.LastNm.Trim(),
                        FirstNm = joined.Key.FirstNm.Trim(),
                        DivisionId = joined.Key.DivId,
                    }).ToList();

The error message says

Could not be translated. 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.

I tried using .AsEnumerable() instead of .ToList():

List<EmployeeViewModel> test = emps.Cast<EmployeeViewModel>().ToList();

but this throws an exception.

Any help is greatly appreciated.

Thanks in advance

CodePudding user response：

Problem that this Select is not currently translatable to the SQL. You can make additional Select to solve issue with AsEnumerable().

    ...
    .Select(joined => new 
    {
        EmployeeId = joined.Key.EmployeeId,
        LastNm = joined.Key.LastNm.Trim(),
        FirstNm = joined.Key.FirstNm.Trim(),
        DivisionId = joined.Key.DivisionId,
    })
    .AsEnumerable()
    .Select((x, index) => new EmployeeViewModel
    {
        Id = index,
        EmployeeId = x.EmployeeId,
        LastNm = x.LastNm,
        FirstNm = x.FirstNm,
        DivisionId = x.DivisionId,
    }).ToList();

And note that query is more readable in Query syntax when there is joins.

var query = 
    from employee in this.DbTracsContext.Employees
    join dept in his.DbTracsContext.Depts on employee.EmployeeId equals dept.EmployeeId into employee_dept_left
    from dept in employee_dept_left.DefaultIfEmpty()
    join division in this.DbTracsContext.Depts on employee.DivisionId equals division.DivisionId
    where string.IsNullOrEmpty(filter.DivisionSelection) || filter.DivisionSelection == "0" || employee.DivisionId == filter.DivisionSelection
    group employee by new { employee.EmployeeId, employee.LastNm, employee.FirstNm, employee.DivisionId } into g
    select new
    {
        EmployeeId = g.Key.EmployeeId,
        LastNm = g.Key.LastNm.Trim(),
        FirstNm = g.Key.FirstNm.Trim(),
        DivisionId = g.Key.DivisionId,
    };

var emps = query
    .AsEnumerable()
    .Select((x, index) => new EmployeeViewModel
    {
        Id = index,
        EmployeeId = x.EmployeeId,
        LastNm = x.LastNm,
        FirstNm = x.FirstNm,
        DivisionId = x.DivisionId,
    }).ToList();