Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
SolvedPrivate

How can I drill down in this list?

Posted on 2014-11-24
17
Medium Priority
?
31 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
17 Comments
 
LVL 59

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 64

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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
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 2000 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

What is a Denial of Service (DoS)?

A DoS is a malicious attempt to prevent the normal operation of a computer system. You may frequently see the terms 'DDoS' (Distributed Denial of Service) and 'DoS' used interchangeably, but there are some subtle differences.

Question has a verified solution.

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

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

715 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