CODEWITHSUNDEEP
arraysentity-frameworkasp.net-identity
Bailey Miller
Bailey Miller

Reputation: 1523

Entity framework query to get array of role names for user

I have been trying to right a single query that can retrieve an array of the current users role names. I have gotten really close sadly my code doesn't return an array. I am fairly new to the entity framework and very confused on joins and inner queries within this language. If I was working in standard sql I would right a sub query to return all roles names where from roles where roleid is in user.roles.

Existing Code: 

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 = ""

            };

I don't want to use the current claims because I want to ensure that when getUser is called the most accurate roles are returned not the ones stored inside the access_token.

Upvotes: 1

Views: 733

Answers (2)

Bailey Miller
Bailey Miller

Reputation: 1523

I used what I understand about actual SQL and that is I would do a sub query to get this data. This works as I wanted.

Code:

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

Upvotes: 0

ocuenca
ocuenca

Reputation: 39326

A solution is doing a group join like this

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

Update

If you have Roles navigation property from User entity then you could do this:

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

Upvotes: 1

Related Questions