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 ^^

Answer 1

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.