EFCore join table and AutoMapper

EFCore join table and AutoMapper

Question

I want to query all users from my ASP.net Identity Users table and map them to a simple DTO like this:

public class UserDto
{
    public string Firstname { get; set; }
    public string Lastname { get; set; }
    public string Email { get; set; }
    public IEnumerable<string> Roles { get; set; }
}

The list of roles should only contain the names of the roles, so I join the roles in from the roles table and get the names. Now I want to simplify this by using AutoMapper and map the results directly into my DTO.

var users = await _userManager.Users
            .AsNoTracking()
            .Include(u => u.Roles)
            .Select(u => new {
                User = u,
                Roles = u.Roles
                    .Join(_roleManager.Roles, 
                            a => a.RoleId, 
                            b => b.Id, 
                            (a, b) => b.Name)
                    .ToList()
            })
            .ToListAsync();

I'm struggling to find a good solution to map this data to a list of UserDto objects with AutoMapper. I tried to user ProjectTo<UserDto> and implement the table join in my mapper configuration but I get a lot of efcore warnings that my queries are executed on the client.

Question: Is there a simple and efficient way to do this with AutoMapper and efcore?

Update Even without AutoMapper it produces a warning :(

var users = await _userManager.Users
            .AsNoTracking()
            .Include(u => u.Roles)
            .Select(u => new UserDto {
                Firstname = u.Firstname,
                Lastname = u.Lastname,
                Email = u.Email,
                Roles = u.Roles
                    .Join(_roleManager.Roles, 
                            a => a.RoleId, 
                            b => b.Id, 
                            (a, b) => b.Name)
                    .ToList()
            })                
            .ToListAsync();

This is the efcore logging output:

info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
      Executed DbCommand (2ms) [Parameters=[@__get_Item_0='?' (Size = 450)], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [e].[Id], [e].[AccessFailedCount], [e].[Address], [e].[City], [e].[ConcurrencyStamp], [e].[Country], [e].[CustomerIdentifier], [e].[Email], [e].[EmailConfirmed], [e].[Firstname], [e].[Gender], [e].[Lastname], [e].[LockoutEnabled], [e].[LockoutEnd], [e].[NormalizedEmail], [e].[NormalizedUserName], [e].[PasswordHash], [e].[PhoneNumber], [e].[PhoneNumberConfirmed], [e].[Region], [e].[SecurityStamp], [e].[TwoFactorEnabled], [e].[UserName], [e].[ZipCode]
      FROM [AspNetUsers] AS [e]
      WHERE [e].[Id] = @__get_Item_0

info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
      Executed DbCommand (2ms) [Parameters=[@__normalizedRoleName_0='?' (Size = 256)], CommandType='Text', CommandTimeout='30']
      SELECT TOP(2) [r].[Id], [r].[ConcurrencyStamp], [r].[Name], [r].[NormalizedName]
      FROM [AspNetRoles] AS [r]
      WHERE [r].[NormalizedName] = @__normalizedRoleName_0

info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
      Executed DbCommand (6ms) [Parameters=[@__get_Item_0='?' (Size = 450), @__get_Item_1='?' (Size = 450)], CommandType='Text', CommandTimeout='30']
      SELECT TOP(1) [e].[UserId], [e].[RoleId]
      FROM [AspNetUserRoles] AS [e]
      WHERE ([e].[UserId] = @__get_Item_0) AND ([e].[RoleId] = @__get_Item_1)

warn: Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory[6]
      The Include operation for navigation: 'u.Roles' was ignored because the target navigation is not reachable in the final query results. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'CoreEventId.IncludeIgnoredWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.

info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT [u].[Firstname], [u].[Lastname], [u].[Email], [u].[Id]
      FROM [AspNetUsers] AS [u]

info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
      Executed DbCommand (2ms) [Parameters=[@_outer_Id='?' (Size = 450)], CommandType='Text', CommandTimeout='30']
      SELECT [b].[Name]
      FROM [AspNetUserRoles] AS [a]
      INNER JOIN [AspNetRoles] AS [b] ON [a].[RoleId] = [b].[Id]
      WHERE @_outer_Id = [a].[UserId]

info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
      Executed DbCommand (1ms) [Parameters=[@_outer_Id='?' (Size = 450)], CommandType='Text', CommandTimeout='30']
      SELECT [b].[Name]
      FROM [AspNetUserRoles] AS [a]
      INNER JOIN [AspNetRoles] AS [b] ON [a].[RoleId] = [b].[Id]
      WHERE @_outer_Id = [a].[UserId]

Update 2

Following the warning, I just removed the Include statement an found a working solution:

var users = await _userManager.Users
            .AsNoTracking()
            .Select(u => new UserDto {
                Firstname = u.Firstname,
                Lastname = u.Lastname,
                Email = u.Email,
                Roles = u.Roles
                    .Join(_roleManager.Roles, 
                            a => a.RoleId, 
                            b => b.Id, 
                            (a, b) => b.Name)
                    .ToList()
            })                
            .ToListAsync();

Accepted Answer

I removed the Include statement from the query, moved the select statement to my AutoMapper profile and added the roles table as a parameter to my ProjectTo statement. Now its working as expected and not producing any efcore warnings.

ef query

        var users = await _userManager.Users
            .AsNoTracking()
            .ProjectTo<UserDto>(new { roles = _roleManager.Roles })              
            .ToListAsync();

automapper profile

        IQueryable<IdentityRole> roles = null;
        CreateMap<User, UserDto>()
            .ForMember(x => x.Roles, opt => 
                opt.MapFrom(src => 
                    src.Roles
                        .Join(roles, a => a.RoleId, b => b.Id, (a, b) => b.Name)
                        .ToList()
                )
            );