Complex GroupBy in C# LINQ lambda expressions

Question

Probably easy for long-time LINQ lambda expressions users, but I'm simply hitting the wall here...

I'm trying to get all Area objects that a user with certain ID can access. I'm using entity framework, if it matters for any reason.

context.AspNetUserRoles
    .Where(u => u.UserId == _currentUserId)
    .Select(ur => ur.AspNetRoles.RolePagePermissions.Select(pp => pp.Page.Area))
    .GroupBy(a => a.Select(g => g.Page.Select(gg => gg.Area)))
    .ToList()

Instead of getting a List<Area>, I'm getting List<IGrouping<IEnumerable<IEnumerable<Area>>>

Anyone having any clues about how to do this easily?

As many pages can be in the same area, and roles are connected to pages, the following code returns many duplicate areas (as one user has access to many pages, therefore one area for each page is returned):

context.AspNetUserRoles.Where(u => u.UserId == _currentUserId).SelectMany(ur => ur.AspNetRoles.RolePagePermissions.Select(pp => pp.Page.Area)).ToList()

Here is the model for testing:

public partial class Area
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Area()
    {
        this.Page = new HashSet<Page>();
    }

    public System.DateTime CreationDate { get; set; }
    public int CreationUser { get; set; }
    public int Id { get; set; }
    public string Title { get; set; }
    public byte Activated { get; set; }
    public string SimpleLineIcon { get; set; }
    public string ControllerName { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Page> Page { get; set; }
}

public partial class AspNetRoles
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public AspNetRoles()
    {
        this.AspNetUserRoles = new HashSet<AspNetUserRoles>();
        this.RolePagePermissions = new HashSet<RolePagePermissions>();
    }

    public string Id { get; set; }
    public string Name { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<AspNetUserRoles> AspNetUserRoles { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<RolePagePermissions> RolePagePermissions { get; set; }
}

public partial class AspNetUserRoles
{
    public int Id { get; set; }
    public string UserId { get; set; }
    public string RoleId { get; set; }

    public virtual AspNetRoles AspNetRoles { get; set; }
    public virtual AspNetUsers AspNetUsers { get; set; }
}

public partial class AspNetUsers
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public AspNetUsers()
    {
        this.AspNetUserClaims = new HashSet<AspNetUserClaims>();
        this.AspNetUserLogins = new HashSet<AspNetUserLogins>();
        this.AspNetUserRoles = new HashSet<AspNetUserRoles>();
    }

    public string Id { get; set; }
    public string Email { get; set; }
    public bool EmailConfirmed { get; set; }
    public string PasswordHash { get; set; }
    public string SecurityStamp { get; set; }
    public string PhoneNumber { get; set; }
    public bool PhoneNumberConfirmed { get; set; }
    public bool TwoFactorEnabled { get; set; }
    public Nullable<System.DateTime> LockoutEndDateUtc { get; set; }
    public bool LockoutEnabled { get; set; }
    public int AccessFailedCount { get; set; }
    public string UserName { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<AspNetUserClaims> AspNetUserClaims { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<AspNetUserLogins> AspNetUserLogins { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<AspNetUserRoles> AspNetUserRoles { get; set; }
}

public partial class Page
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Page()
    {
        this.RolePagePermissions = new HashSet<RolePagePermissions>();
    }

    public System.DateTime CreationDate { get; set; }
    public int CreationUser { get; set; }
    public int Id { get; set; }
    public string Title { get; set; }
    public byte Activated { get; set; }
    public int AreaId { get; set; }
    public string SimpleLineIcon { get; set; }
    public string ActionName { get; set; }

    public virtual Area Area { get; set; }
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<RolePagePermissions> RolePagePermissions { get; set; }
}

public partial class RolePagePermissions
{
    public System.DateTime CreationDate { get; set; }
    public int CreationUser { get; set; }
    public int Id { get; set; }
    public string RoleId { get; set; }
    public int PageId { get; set; }
    public byte AccessAllowed { get; set; }
    public Nullable<System.DateTime> AccessAllowedByDate { get; set; }

    public virtual AspNetRoles AspNetRoles { get; set; }
    public virtual Page Page { get; set; }
}

`context.AspNetUserRoles.Where(u => u.UserId == _currentUserId).Select(ur => ur.AspNetRoles.RolePagePermissions.Select(pp => pp.Page.Area)).GroupBy(a => a.SelectMany(g => g.Page.Select(gg => gg.Area))).ToList()` — Soheil Alizadeh, Mar 24 '18 at 18:59
@SoheilAlizadeh Got List>>, so one IEnumerable less. Any ides how to get only List? — Alen Šimunic, Mar 24 '18 at 19:04

IPValverde · Answer 1 · 2018-03-24T19:58:12.463

In LinqToEntities the Select method will return an IQueryable of the type that your are selecting in your expression (It is the same idea with LinqToObjects, but there you have Function instead of Expression as the parameter of your Select method, and it will return an IEnumerable).

So if you select a property that is of type string, you'll have an IQueryable<string>, in your case you're selecting a list inside another list. What you need to do in there is to use the SelectMany that just unwraps the returned collection from your Expression giving you only a single collection with all the items you would have.

You'll end up with something like this:

List<Area> userAreas = context.AspNetUserRoles.Where(u => u.UserId == _currentUserId)
    .SelectMany(ur => ur.AspNetRoles.RolePagePermissions.Select(pp => pp.Page.Area)).ToList();

Since you already know the id of the user and there is a relationship in your entity models between the user and their areas, it's not necessary to user a GroupBy.

This was the very first approach I tried before, but I end up getting this error: http://prntscr.com/ivuq8e any ideas why? — Alen Šimunic, Mar 24 '18 at 19:26
The second `SelectMany` should be a `Select`, `Area` isn't a collection — Camilo Terevinto, Mar 24 '18 at 19:27

Drew Sumido · Answer 2 · 2018-03-24T19:46:00.350

0

This should be what you want:

    context.AspNetUserRoles.Where(u => u.UserId == _currentUserId)
   .SelectMany(ur => ur.AspNetRoles.RolePagePermissions.Select(pp => pp.Page.Area));

Not sure what you want to accomplish with the group by, but this will give the list of areas.

edited Mar 24 '18 at 19:46

answered Mar 24 '18 at 19:38

Drew Sumido

162
1
5

Sorry, removed the group by. What did you want to group it by? – Drew Sumido Mar 24 '18 at 19:47
Many pages can belong to the same area, so my previous code returned many same areas out, so I wanted to group by area. Distinct helped here and solved the issue for now. – Alen Šimunic Mar 24 '18 at 19:49

score 0 · Accepted Answer · answered Mar 24 '18 at 19:38

I found one of the solutions that returns a list of all accessible areas:

context.AspNetUserRoles.Where(u => u.UserId == _currentUserId).SelectMany(ur => ur.AspNetRoles.RolePagePermissions.Select(pp => pp.Page.Area)).Distinct().ToList()

So .Distinct() made it all happen, thanks all for help.

Complex GroupBy in C# LINQ lambda expressions

3 Answers3