CODEWITHSUNDEEP
c#linqentity-framework
Imran Balouch
Imran Balouch

Reputation: 2170

LINQ JOIN for Collections in Collections

I have a table named Users with Id as Primary Key another table Roles with Id as Primary Key and a table named UserRoles having 2 foreign Keys User_Id and Role_Id.

In my code I have a class named Role as:

public partial class Role
{
   public virtual int Id {get;set;}
   public virtual string RoleName {get;set;}
}

And I have another class named User as:

public partial class User
{
    private ICollection<Role> _userRoles;

    ...All Properties...
    public virtual ICollection<Role> UserRoles
    {
        get { return _userRoles ?? (_userRoles = new List<Role>()); }
        protected set { _userRoles = value; }
    }
}

The mapping of the classes is defined properly, so when I get a user, I get that to which roles s/he belongs. Now I want to show list of roles in the grid list and want to show number of users against each role.

I have tried to use join but I caouldn't reach the results.

Any ideas what can be the best way to do it? Thanks in advance

Upvotes: 0

Views: 107

Answers (3)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236208

Add navigation property to Role class:

public partial class Role
{
   public virtual int Id {get;set;}
   public virtual string RoleName {get;set;}
   public virtual ICollection<User> Users { get; set; } // Here
}

Then simply query users count for each role:

var query = from r in context.Roles
            select new {
                 r.RoleName,
                 UsersCount = r.Users.Count()
            };

Generated SQL will look like 

SELECT [Extent1].[Id] AS [Id],
       [Extent1].[RoleName] AS [RoleName],
       (SELECT COUNT(1) AS [A1]
        FROM   [dbo].[UserRoles] AS [Extent2]
        WHERE  [Extent1].[Id] = [Extent2].[Role_Id]) AS [C1]
FROM   [dbo].[Roles] AS [Extent1]

As you can see, for each role subquery will be generated. Run it and see if performance is acceptable in your case.

Upvotes: 2

Rubixus
Rubixus

Reputation: 767

You can merge the inner roles lists, then group the list by role, and then get the count of each group.

List<User> users = ...;

var roleCounts = users.SelectMany(user => user.UserRoles)
                      .GroupBy(role => role)
                      .Select(roleGroup => new {Role = roleGroup.Key, Count = roleGroup.Count());

foreach (var roleCount in roleCounts)
{
    Role role = roleCount.Role;
    int count = roleCount.Count;
    ...
}

Upvotes: 0

aquaraga
aquaraga

Reputation: 4168

You could use the Aggregate function in Linq:

        var users = new List<User>();
        var roles = new List<Role>();
        //Populate users and roles

        var rolesAndUsers = new Dictionary<Role, List<User>>();

        users.Aggregate(rolesAndUsers, (d, u) =>
                                           {
                                               ICollection<Role> userRoles = u.UserRoles;
                                               foreach (var userRole in userRoles)
                                               {
                                                   if (!d.ContainsKey(userRole))
                                                       d.Add(userRole, new List<User>());
                                                   d[userRole].Add(u);
                                               }
                                               return d;
                                           });

Upvotes: 2

Related Questions