EFCore连接表和AutoMapper

Question

我想从我的ASP.net Identity用户表中查询所有用户并将它们映射到一个简单的DTO，如下所示：

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

角色列表应该只包含角色名称，因此我从角色表中连接角色并获取名称。现在我想通过使用AutoMapper将结果直接映射到我的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();

我在使用AutoMapper将这些数据映射到UserDto对象列表时遇到了困难。我尝试使用ProjectTo<UserDto>并在我的映射器配置中实现表连接，但我得到了很多efcore警告，说我的查询在客户端上执行。

问题：有没有一种简单而有效的方法可以使用AutoMapper和efcore完成此操作？

更新：即使没有AutoMapper，它也会产生警告 :(

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();

这是efcore的日志输出:

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]

更新2

在收到警告后，我刚刚删除了Include语句并找到了一个可行的解决方案：

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();

- Steffen

---

在寻找AutoMapper解决方案之前，您是否已经找到了手动解决方案？因为这可能只是当前EF Core的限制。 - Ivan Stoev

@IvanStoev 我已更新我的问题。在选择语句中手动映射到UserDto时，我仍然存在问题。 - Steffen

我对最后一个警告有点困惑。但是在删除了Include语句之后，它就可以工作了。 - Steffen

Answer 1

我已经从查询中删除了包含语句，将选择语句移到了我的 AutoMapper配置文件中，并将角色表作为参数添加到我的 ProjectTo 语句中。现在它能够按预期工作，而且不会产生任何 efcore 警告。

ef 查询

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

自动映射配置文件

        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()
                )
            );

- Steffen