SolvedPrivate

How can I drill down in this list?

Posted on 2014-11-24
17
20 Views
Last Modified: 2016-02-18
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
0
Comment
Question by:Camillia
17 Comments
 
LVL 51

Expert Comment

by:HainKurt
ID: 40463021
i guess you should use

x.Users.RoleID instead of another select
0
 
LVL 7

Author Comment

by:Camillia
ID: 40463051
No, I get "RoleID" is not defined. I don't see it in intellisense either.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 40463757
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
 
LVL 7

Author Comment

by:Camillia
ID: 40463764
let me try
0
 
LVL 7

Author Comment

by:Camillia
ID: 40463772
I've attached 2 screenshots.

One for what I see in "list" and the other what's in "Users"
users.png
list.png
0
 
LVL 14

Expert Comment

by:Tchuki
ID: 40464302
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
 
LVL 7

Author Comment

by:Camillia
ID: 40464368
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
 
LVL 7

Author Comment

by:Camillia
ID: 40464725
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 7

Author Comment

by:Camillia
ID: 40464769
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
 
LVL 14

Expert Comment

by:Tchuki
ID: 40464873
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
 
LVL 7

Author Comment

by:Camillia
ID: 40464875
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
 
LVL 14

Expert Comment

by:Tchuki
ID: 40464894
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
 
LVL 7

Author Comment

by:Camillia
ID: 40464902
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
 
LVL 14

Expert Comment

by:Tchuki
ID: 40464912
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
 
LVL 7

Author Comment

by:Camillia
ID: 40464918
yes,..just a grid of users with their roles.
0
 
LVL 14

Accepted Solution

by:
Tchuki earned 500 total points
ID: 40464961
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
 
LVL 7

Author Comment

by:Camillia
ID: 40464968
thanks,let me try it.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now