I was trying to add an index on the result set that LINQ was giving me and I was successful in doing so. I'll not go in deep about why indexing I'm doing as the major reason being the nature of data that I'm dealing with is having redundancy and I need to work with it.

Not much into the LINQ but i have managed to get through the problem statement that i have. Now i'm having following doubts.

1. Whether im putting index on the record properly ?

2. Do i need to align query ? i mean the sequence of predicates that i have used in query ?

3. It seems to be neat query then why still it is giving error as :

   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.

Heres my query :

dbEmployees = await _dbContext.Employees
                                        .AsNoTracking()
                                        .OrderBy(x => x.EmployeeName)
                                        .ThenBy(x => x.EmployeeCode)
                                        .ThenBy(x => x.BadgeNumber)
                                        .ThenBy(x => x.StartDate)
                                        .ThenBy(x => x.SignOnTime)
                                        .Where(x => x.UnitCode == "BU" && x.DepartmentId == 1)
                                        .Select((item, employeeIndex) => new
                                        {
                                            EmployeeIndex = employeeIndex   1,
                                            item
                                        })
                                        .Where(x => x.EmployeeIndex >= employeeIndexPointer)
                                        .Select(x => x.item)
                                        .Take(10)
                                        .ToListAsync();

Any help or pointers are welcomed.

CodePudding user response：

You will need to use LINQ to Object (instead of LINQ to SQL) if operating on IEnumerable<T>

Notice the .AsEnumerable() right before our .Where() statement, this will transform the result set from IQueryable<T> to IEnumerable<T>

var dbEmployees = await _dbContext.Employees
    .AsNoTracking()
    .OrderBy(x => x.EmployeeName)
    .ThenBy(x => x.EmployeeCode)
    .ThenBy(x => x.BadgeNumber)
    .ThenBy(x => x.StartDate)
    .ThenBy(x => x.SignOnTime)
    .AsEnumerable()
    .Where(x => x.UnitCode == "BU" && x.DepartmentId == 1)
    .Select((item, employeeIndex) => new
    {
        EmployeeIndex = employeeIndex   1,
        item
        })
    .Where(x => x.EmployeeIndex >= employeeIndexPointer)
    .Select(x => x.item)
    .Take(10)
    .ToListAsync();

Read a bit more here: Are Linq to SQL and Linq to Objects queries the same?