How to flatten Entity Core many-to-many collections using Linq

asp.net-core-identity automapper c# entity-framework-core linq

Question

I can't figure out how to flatten a many to many collection. The entities are for the Identity tables created with Entity Framework Core. They contain navigation properties I added manually because the Identity entities don't include these by default (along with custom model builder code that is not shown)

public class AppUser : IdentityUser<long> {

    public string FirstName { get; set; }
    public string LastName { get; set; }

    public virtual List<AppUserRole> UserRoles { get; set; } = new List<AppUserRole>();

}

public class AppRole : IdentityRole<long> {

    public virtual List<AppUserRole> UserRoles { get; set; } = new List<AppUserRole>();

}

public class AppUserRole : IdentityUserRole<long> {

    public virtual AppUser User { get; set; }
    public virtual AppRole Role { get; set; }

}

This is the call in my repo:

public async Task<IEnumerable<AppUser>> GetAllWithRoles() 
{
    return await _dbSet
        .AsNoTracking()
        .Include(u => u.UserRoles)
        .ThenInclude(ur => ur.Role)
        .ToListAsync();
}

The above call returns back this structure:

[
{
    "firstName": "XXXX",
    "lastName": "YYYYY",
    "userRoles": [
        {
            "role": {
                "userRoles": [],
                "id": 1,
                "name": "xxx",
                "normalizedName": "xxxx",
                "concurrencyStamp": "1617fe40-77e2-46cb-9c1c-df597d09775c"
            },
            "userId": 1,
            "roleId": 1
        }
    ]       
}
]

what I want is this:

[
{
    "firstName": "Alex",
    "lastName": "Florin",
    "RoleName": "Role1",
},
{
    "firstName": "Alex",
    "lastName": "Florin",
    "RoleName": "Role2",
},
{
    "firstName": "Jon",
    "lastName": "Smith",
    "RoleName": "Role1",
},
]

What I want is to flatten the collection. I have looked into SelectMany but I can't figure out how to use it with a many to many collection as I am fairly new to Linq. And I know the call function's type needs to be change to a viewmodel that matches my desired structure.

Automapper is another option as I'm using that to create the viewmodels for my simpler entities but I'm not clear how to set use that to flatten a many-to-many relationship

1
0
3/28/2018 9:37:30 PM

Accepted Answer

I figured it out. The one issue is that it excludes any users with UserRoles = null but since all users are guaranteed to have at least one role in our system, it should be fine.

    public async Task<IEnumerable<UserEditorViewModel>> GetAllWithRoles() {

        return await _dbSet
            .AsNoTracking()
            .SelectMany(u => u.UserRoles)
            .Select(ur => new UserEditorViewModel {
                FirstName = ur.User.FirstName,
                LastName = ur.User.LastName,
                RoleName = ur.Role.Name
            })
            .ToListAsync();
    }
0
3/28/2018 11:49:50 PM

Popular Answer

Not sure what you want to flatten exactly, to Select all Roles from User

users.Select(p => p.UserRoles.Role)

To Select all Users from Role

roles.Select(p => p.UserRoles.User)

Edit:

I think this exmaple will help you https://blog.oneunicorn.com/2017/09/25/many-to-many-relationships-in-ef-core-2-0-part-2-hiding-as-ienumerable/

Your Model could look like this

public class AppUser : IdentityUser<long> {

    public string FirstName { get; set; }
    public string LastName { get; set; }

    private ICollection<AppRole> UserRoles{ get; } = new List<PostTag>();

    [NotMapped]
    public IEnumerable<string> RoleNames => UserRole.Role.Name

}

public class AppRole : IdentityRole<long> {
    private ICollection<AppRole> UserRoles{ get; } = new List<PostTag>();

    [NotMapped]
    public IEnumerable<AppRole> Users => UserRole.User
}

public class AppUserRole : IdentityUserRole<long> {
    public long UserID{ get; set; }
    public AppUser User { get; set; }

    public long RoleID { get; set; }
    public Role Role { get; set; }
}


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow