SolvedPrivate

How can I drill down in this list?

Posted on 2014-11-24
17
21 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 63

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
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: 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:Vel Eous
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
 
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:Vel Eous
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:Vel Eous
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:Vel Eous
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:
Vel Eous 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
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 …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

803 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