In ASP.NET core 3.0, I get the following exception when I apply the Except() method

Question

InvalidOperationException: Processing of the LINQ expression 'Except

I am using the Identity system in ASP.NET core 3.0 to make a website. I have a list of users, some of whom are assigned to roles. Basically, I am trying to get a list of users who are NOT assigned to a particular role.

My appraoch to implement this is by getting all the users minus those who are already assigned to a role. I am trying to do this with the help of Except() method. Here are the basics of my action method:

[HttpGet]
public async Task<IActionResult> AddUsersToRole(string roleName)
{
    IdentityRole role = await roleManager.FindByNameAsync(roleName);
    if (role != null)
    {
        IList<ApplicationUser> usersInRole = await userManager.GetUsersInRoleAsync(role.Name);
        IQueryable<ApplicationUser> usersNotInRole = userManager.Users.Except(usersInRole);
        List<AddUsersToRoleViewModel> models = new List<AddUsersToRoleViewModel>();

    // Exception is thrown here...
    // Copy data to ViewModel
    foreach (var user in usersNotInRole)
    {
        models.Add(new AddUsersToRoleViewModel { UserId = user.Id });
    }

    return View(models);
}

The exception is thrown when I try to read data from usersNotInRole object. Even when I remove the ViewModel and just pass the usersNotInRole object to the View, I still get the exception. Any ideas? I am newbie to programming.

Answer 1

The query provider is unable to translate the linq expression into a query.

The exception is thrown at the foreach because that is when the queriable is enumerated and executed against the source.

Use AsEnumerable to load the users into memory.

var usersNotInRole = userManager.Users.AsEnumerable().Except(usersInRole);