How can I drill down in this list?

Please see attached screenshot.

See how "roles" has "Users" and Users has "RoleId and "UserId".

How can I drill down to get a list of "Users"?

This is what I tried. I also tried a foreach loop for "list" but don't see RoleId and UserId

var roles = _dataContext.Roles.ToList();
            var list = (from p in roles.Select(x => x.Users.Select(y => y.RoleId))
                select p).ToList();

Open in new window

roledrilldown.png
LVL 8
CamilliaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HainKurtSr. System AnalystCommented:
i guess you should use

x.Users.RoleID instead of another select
0
CamilliaAuthor Commented:
No, I get "RoleID" is not defined. I don't see it in intellisense either.
0
Fernando SotoRetiredCommented:
Hi Camilla;

Modify the query to be this, and in debug expand the Users so we can see what properties are there.

var list = (from p in roles.Select(x => x.Users)
                select p).ToList();

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

CamilliaAuthor Commented:
let me try
0
CamilliaAuthor Commented:
I've attached 2 screenshots.

One for what I see in "list" and the other what's in "Users"
users.png
list.png
0
Vel EousResearch & Development ManagerCommented:
Are you wanting to return just a list of the Users with the specified RoleId?  If so then perhaps the following will work for you, I have made some assumptions on your object tree.

namespace EE.Q_28568740.Cons
{
    using System;
    using System.Collections.Generic;
    using System.Linq;

    public class Program
    {
        private static void Main(string[] args)
        {
            var roles = new List<Role>();

            // Creating some fake objects to work with, making assumptions about their structure
            for (var i = 0; i < 5; i++)
            {
                var users = new List<User>();
                for (var j = 0; j < 10; j++)
                {
                    users.Add(new User
                    {
                        UserId = Guid.NewGuid(),
                        RoleId = i
                    });
                }
                roles.Add(new Role {RoleId = i, Users = users});
            }

            const int roleId = 1;
            // Following produces a list of just users with the specified roleId
            var list = roles.Where(e => e.RoleId == roleId).Select(e => e.Users).ToList();

            Console.ReadKey(); // Slap a breakpoint here and inspect 'list'
        }
    }

    public class Role
    {
        public int RoleId { get; set; }
        public List<User> Users { get; set; }
    }

    public class User
    {
        public Guid UserId { get; set; }
        public int RoleId { get; set; }
    }
}

Open in new window

0
CamilliaAuthor Commented:
Are you wanting to return just a list of the Users with the specified RoleId?
Yes. I'll try it when I get to work and post back.
0
CamilliaAuthor Commented:
I followed, Tchuki, and I might be close. I changed it like this. I think this should work but list brings back zero count.

See attached screenshot.

If no one has time, it's ok. I just won't display the role but I know my manager will ask for it when I demo this page to him tomorrow.

var roles = _dataContext.Roles.ToList();
var list = roles.Where(e => e.Id.ToString() == e.Users.Select(x => x.RoleId).ToString() ).Select(e => e.Users).ToList();

Open in new window

rolesnewcode.png
0
CamilliaAuthor Commented:
See my comment on ID: 40464725


I turned on profiler and I see this SQL. I think that line of code should work but it loops thru, twice and just takes the last iteration. It takes the iteration of Role=2 and I don't have any users for role=2.

So, how can I keep both iterations. I need a left join. Even if a user doesn't have a role, I want to bring it back.

Again, if no one has time, it's ok for now (but we're close :))

This is what I see in Profiler.

//brings back one row
exec sp_executesql N'SELECT 
    [Extent1].[UserId] AS [UserId], 
    [Extent1].[RoleId] AS [RoleId]
    FROM [dbo].[AspNetUserRoles] AS [Extent1]
    WHERE [Extent1].[RoleId] = @EntityKeyValue1',N'@EntityKeyValue1 nvarchar(128)',@EntityKeyValue1=N'1'

//brings back nothing which is correct

exec sp_executesql N'SELECT 
    [Extent1].[UserId] AS [UserId], 
    [Extent1].[RoleId] AS [RoleId]
    FROM [dbo].[AspNetUserRoles] AS [Extent1]
    WHERE [Extent1].[RoleId] = @EntityKeyValue1',N'@EntityKeyValue1 nvarchar(128)',@EntityKeyValue1=N'2'

Open in new window

0
Vel EousResearch & Development ManagerCommented:
Your query looks at though you are just going to return all users for all roles.  I thought you wanted to return the users for a specific role id?

You need to specify somewhere in the query the specific role id you want to return results for.
0
CamilliaAuthor Commented:
No, I want a list of users and their roles.  I can do it in a stored proc but I want to do it in LINQ.

That SQL is the result of the LINQ running. I didn't write that SQL.
0
Vel EousResearch & Development ManagerCommented:
var list = roles.Select(e => e.Users.Where(x => e.Id == x.RoleId).ToList()).ToList();

Open in new window


Assuming your Users are a collection within the Roles the above will return a list that looks as follows

+ Role
  - User
  - User
  - User
+ Role
  - User
  - User
  - User

If you are expecting some other list structure an example would be helpful (for me at least).
0
CamilliaAuthor Commented:
Thanks!

I wanted to display them like this. I think a user can have one role in this app.
I wanted to display it like this in a grid. But yes, what if a user has more than one role? I guess I can display only one role. I have "Admin" and "User".

User 1            Role1
User2              Role2
0
Vel EousResearch & Development ManagerCommented:
So rather than displaying users by role:

Role A -> User A, User B ...

You want to display roles by user:

User A -> Role A, Role B ...
0
CamilliaAuthor Commented:
yes,..just a grid of users with their roles.
0
Vel EousResearch & Development ManagerCommented:
Your comment about there being possibly more than one role per user and more than one user per role suggests a many to many relationship and that User has a collection of Role and Role a collection of User.  If so take a look at the following, it returns a flattened list of users and containing a collection of the roles they are associated with:

namespace EE.Q_28568740.Cons
{
    using System;
    using System.Collections.Generic;
    using System.Linq;

    public class Program
    {
        private static void Main(string[] args)
        {
            var roles = new List<Role>();

            // Creating some fake objects to work with, making assumptions about their structure
            var roleA = new Role {Id = 1};
            var roleB = new Role {Id = 2};
            var roleC = new Role {Id = 3};

            var userA = new User { RoleId = new List<int> { 1, 3 }, UserId = Guid.NewGuid() };
            var userB = new User { RoleId = new List<int> { 2, 3 }, UserId = Guid.NewGuid() };
            var userC = new User { RoleId = new List<int> { 1 }, UserId = Guid.NewGuid() };
            var userD = new User { RoleId = new List<int> { 1, 2 }, UserId = Guid.NewGuid() };

            roleA.Users.Add(userA);
            roleA.Users.Add(userC);
            roleA.Users.Add(userD);

            roleB.Users.Add(userB);
            roleB.Users.Add(userD);

            roleC.Users.Add(userA);
            roleC.Users.Add(userB);

            roles.Add(roleA);
            roles.Add(roleB);
            roles.Add(roleC);

            // Following produces a list of just users with the specified roleId
            var list = roles.SelectMany(e => e.Users).Distinct().ToList();

            Console.ReadKey(); // Slap a breakpoint here and inspect 'list'
        }
    }

    public class Role
    {
        public Role()
        {
            this.Users = new List<User>();
        }

        public int Id { get; set; }
        public List<User> Users { get; set; }
    }

    public class User
    {
        public Guid UserId { get; set; }
        public List<int> RoleId { get; set; }
    }
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CamilliaAuthor Commented:
thanks,let me try it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.