1

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.

Sea_Ocean
  • 309
  • 3
  • 8

1 Answers1

4

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);
Nkosi
  • 235,767
  • 35
  • 427
  • 472
  • @Sea_Ocean Considering that you only need the Id, you should do a Select(x => x.Id) on Users and usersInRole to reduce the amount of data fetched from the database. – ckuri Nov 03 '19 at 08:51
  • @ckuri thanks for pointing that out. I will do exactly that in all my code. It's much more readable also. – Sea_Ocean Nov 03 '19 at 13:20