SolvedPrivate

How can I drill down in this list?

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

Expert Comment

by:Huseyin KAHRAMAN
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

730 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