实体框架查询以获取用户的角色名称数组

Question

我一直在尝试纠正一个可以检索当前用户角色名称数组的查询。可悲的是，我的代码无法返回数组。我是实体框架的新手，并且对这种语言的连接和内部查询非常困惑。如果我在标准SQL中工作，我将正确查询以返回所有角色名称，其中roalid在用户中的角色中。

。

现有代码：

var user = from obj in db.Users
                       join obj2 in db.Roles on obj.Id equals obj2.Id
                       where obj.UserName == name select new {
                FirstName =obj.FirstName,
                LastName =obj.LastName,
                Email =obj.Email,
                JoinDate =obj.JoinDate,
                ProfilePic =obj.ProfileSettings.ProfilePicture,
                Roles = ""
            };

我不想使用当前索赔，因为我想确保将getuser称为最准确的角色，而不是存储在access_token中的角色。

Answer 1

解决方案像这样做group join

var user = from obj in db.Users
           join obj2 in db.Roles on obj.Id equals obj2.Id into roles
           where obj.UserName == name 
           select new {
            FirstName =obj.FirstName,
            LastName =obj.LastName,
            Email =obj.Email,
            JoinDate =obj.JoinDate,
            ProfilePic =obj.ProfileSettings.ProfilePicture,
            Roles = roles.Select(e=>e.Name)
          };

更新

如果您具有User实体的Roles导航属性，则可以执行此操作：

var user = db.Users.Where(e=>e.UserName == name)
                   .Select(obj=>new {FirstName =obj.FirstName,
                                     LastName =obj.LastName,
                                     Email =obj.Email,
                                     JoinDate =obj.JoinDate,
                                     ProfilePic =obj.ProfileSettings.ProfilePicture,
                                     Roles = obj.Roles.Select(e=>e.Name)
                                    });

Answer 2

我使用了我对实际SQL的理解，也就是说，我将进行子查询以获取此数据。这是我想要的。

代码：

var user = from obj in db.Users
                       where obj.UserName == name
                       select new
                       {
                           ID1 = obj.Id,
                           FirstName = obj.FirstName,
                           LastName = obj.LastName,
                           Email = obj.Email,
                           JoinDate = obj.JoinDate,
                           ProfilePic = obj.ProfileSettings.ProfilePicture,
                           Roles = (from info in obj.Roles
                                    from allData in db.Roles
                                    where allData.Id == info.RoleId
                                    select allData.Name).ToList()
                       };