ASP.Net Core: Get User Roles with LinQ Select method

Question

I had a problem when select user roles with LinQ in Asp.Net Core. This is my function:

public async Task<ApiResult<PagedResult<UserViewModel>>> GetUserPaging(PagingRequestBase request)
{
    if(request==null) return new ApiErrorResult<PagedResult<UserViewModel>>("Invalid request");
    var query= await userManager.Users.Skip((request.PageIndex-1)*request.PageSize)
    .Take(request.PageSize)
    .Select(async x => new UserViewModel{
        UserName=x.UserName,
        Dob=x.Dob,
        Email=x.Email,
        FirstName=x.FirstName,
        LastName=x.LastName,
        Roles=await userManager.GetRolesAsync(x)
    }).ToListAsync();
}

I want to use async keyword inside Select method in order to use: await userManager.GetRolesAsync(x) but intellisense warning me:

Async lambda expressions cannot be converted to expression trees.

Thanks for yours help ^^

I think your answer is here https://stackoverflow.com/questions/43562150/asp-net-core-1-1-getting-all-users-and-their-roles — mohsen, May 30 '20 at 15:42
Thanks for your answer, but in ASP.NET Core Identity 2.x, IdentityUser no longer contains a Roles property — dnguyen244, May 30 '20 at 15:48

score 1 · Accepted Answer · answered May 30 '20 at 16:00

I can suggest you 2 solutions. The first one is so dividing the two steps. First, get the users, and then get the roles. I think the problem could be that EF can't convert the async code to the expression tree and then convert it to SQL. So making it by separate ensures you that the second step is linq to object request (not linq to SQL). The problem with this solution is that you are making to much requests to the Database. It is the N + 1 problem.

public async Task<ApiResult<PagedResult<UserViewModel>>> GetUserPaging(PagingRequestBase request)
{
    if(request==null) return new ApiErrorResult<PagedResult<UserViewModel>>("Invalid request");
    var users = await userManager.Users.Skip((request.PageIndex-1)*request.PageSize)
    .Take(request.PageSize)
    .ToListAsync();

    var query = users.Select(async x => new UserViewModel{
        UserName=x.UserName,
        Dob=x.Dob,
        Email=x.Email,
        FirstName=x.FirstName,
        LastName=x.LastName,
        Roles=await userManager.GetRolesAsync(x)
    })
}

So, this points us to the second approach. And it is making the request directly to the EF Database Context, and making the joining explicitly:

            YourfDbContext t = null;  // this is only for pointing it

            var x = from usr in t.Users 
                    join userRole in t.UserRoles on usr.Id equals userRole.UserId 
                    join role in t.Roles on userRole.RoleId equals role.Id into roles
                    select new {
                        User = usr,
                        Roles = roles
                    };

This solution only makes one DB request and get all the data you want.

Hi @dnguyen244! As I see your are new! I invite you to collaborate with Stackoverflow! If this answer was helpful to you, should vote up, or if it is wrong (or a very bad answer) you should vote down it. Also if it is the correct answer for you, you should mark it as the accepted answer! Sheers — Raúl Otaño, May 30 '20 at 16:08

ASP.Net Core: Get User Roles with LinQ Select method

1 Answers1