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
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();
}
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; }
}
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow