EFCore連接表和AutoMapper

.net-core automapper c# entity-framework-core

我想從我的ASP.net Identity Users表中查詢所有用戶,並將它們映射到一個簡單的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();

一般承認的答案

我從查詢中刪除了Include語句,將select語句移動到我的AutoMapper配置文件,並將roles表作為參數添加到我的ProjectTo語句中。現在它按預期工作,並沒有產生任何efcore警告。

如果查詢

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


Related

許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow