entity-framework - 使用asp.net核心中的连接查询在实体框架中获取用户角色

Question

我有 4 个表，分别称为用户、角色、照片和用户关系。用户和角色表是身份服务器表。我想返回带有角色名称的用户列表。我可以获得其他详细信息，但是在获取角色名称时我被卡住了。

以下是我自己创建的表格截图

相片：

用户关系：

这是我试过的代码

var relations = await (from relation in _context.UserRelationships
                                   where (relation.RelatingUserId == id || relation.RelatedUserId == id)
                                    && (relation.Status == UserRelationShipStatus.Active)
                                    && (relation.Type != UserRelationshipType.Blocked)

                                   select new
                                   {
                                       RelationId = relation.Id,
                                       User = relation.RelatedUserId == id ? (from usr in _context.Users
                                                                              join photo in _context.Photos on usr.Id equals photo.UserId
                                                                              where usr.Id == relation.RelatingUserId && photo.IsMain == true
                                                                              select new
                                                                              {
                                                                                  UserId = usr.Id,
                                                                                  UserName = usr.UserName,
                                                                                  PhotoUrl = photo.Url,
                                                                                  Role = ?
                                                                              })
                                                       : (from usr in _context.Users
                                                          join photo in _context.Photos on usr.Id equals photo.UserId
                                                          where usr.Id == relation.RelatedUserId && photo.IsMain == true
                                                          select new
                                                          {
                                                              UserId = usr.Id,
                                                              UserName = usr.UserName,
                                                              PhotoUrl = photo.Url,
                                                              Role = ?
                                                          })

                                   }
                                  ).ToListAsync();

我在这里所做的是在我的 UserRelationships 表中包含两个名为 RelatingUserId 和 RelatedUserId 的字段。这意味着关系发送用户和接收用户。如果传递的 UserId 等于这些字段之一，那么我想获取该用户详细信息。我想知道的是在获取用户角色时该怎么做。

标签： entity-frameworkasp.net-corejoin

Answer 1

您无法在 Linq 查询中检索数据。尝试使用 ViewModel 而不是匿名类型返回结果。然后通过检索用户角色将角色添加到结果中。

1.创建视图模型

public class UserRelationshipsViewModel
{
    public string RelationId { get; set; }
    public UserViewModel User { get; set; }

}

public class UserViewModel
{
    public string UserId { get; set; }
    public string UserName { get; set; }
    public string PhotoUrl {get; set;}

    public IList<string> Roles { get; set; }
}

2.用ViewModel返回数据

var relations = await (from relation in _context.UserRelationships
                               ...
                               select new UserRelationshipsViewModel
                               {
                                   RelationId = relation.Id,
                                   User = relation.RelatedUserId == id ? (from usr in _context.Users
                                                                          join photo in _context.Photos on usr.Id equals photo.UserId
                                                                          where usr.Id == relation.RelatingUserId && photo.IsMain == true
                                                                          select new UserViewModel
                                                                          {
                                                                              UserId = usr.Id,
                                                                              UserName = usr.UserName,
                                                                              PhotoUrl = photo.Url,

                                                                          })
                                                   : (from usr in _context.Users
                                                      join photo in _context.Photos on usr.Id equals photo.UserId
                                                      where usr.Id == relation.RelatedUserId && photo.IsMain == true
                                                      select new UserViewModel
                                                      {
                                                          UserId = usr.Id,
                                                          UserName = usr.UserName,
                                                          PhotoUrl = photo.Url,

                                                      })

                               }
                              ).ToListAsync();

3.将角色添加到数据。

 foreach (var r in relations)
        {
            var user = await _userManager.FindByIdAsync(r.User.UserId);
            var Roles = await _userManager.GetRolesAsync(user);
            r.User.Roles = Roles;
        }

更新：

当有数千个用户时会导致性能不佳。我尝试加入 UserRoles 表来获取角色。

              (from usr in _context.Users

                    join ur in _context.UserRoles on usr.Id equals ur.UserId into bt
                    join photo in _context.Photos on usr.Id equals photo.UserId
                    where usr.Id == relation.RelatingUserId && photo.IsMain == true
                    select new
                    {

                        Roles = (from x in bt
                                join r in _context.Roles on x.RoleId equals r.Id
                                select new
                                {
                                    r.Name
                                }
                                ).ToList()

                    })