How to flatten Entity Core many-to-many collections using 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

Answer 1

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();
    }