How to select Entity Framework many-to-many query?

Question

I have a many-to-many relation in my Entity Framework context.

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public IList<UserRole> UserRoles { get; set; }
}

public class UserRole
{
    public int UserId { get; set; }
    public int RoleId { get; set; }
}

public class Role
{
    public int Id { get; set; }
    public string Name { get; set; }
    public IList<UserRole> UserRoles { get; set; }
}

And my context is:

public class MyContext : DbContext
{       
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<UserRole>().HasKey(sc => new { sc.UserId, sc.RoleId });
    }

    public DbSet<User> Users { get; set; }
    public DbSet<Role> Roles { get; set; }
    public DbSet<UserRole> UserRoles { get; set; }
}

So when a user is assigned to a role, the record is in UserRole table. My roles table has 4 rows of data:

[
    { roleId = 1, name = "Administrator" },
    { roleId = 2, name = "Editor"},
    { roleId = 3, name = "x"},
    { roleId = 4, name = "y"}
]

But I want to select all roles for a user. But user assigned data property should be true like the following. For example I want to select roles for userid = 1. Because 2 role assigned.

roles = [
    { roleId = 1, name = "Administrator", isAddigned = true },
    { roleId = 2, name = "Editor", isAddigned = true },
    { roleId = 3, name = "x", isAddigned = false },
    { roleId = 4, name = "y", isAddigned = false }
]

Bot how can I select this query using Entity Framework?

Steve Py Steve Py · Accepted Answer · 2019-12-16T00:09:47

From what it sounds like you have a user (ID #1) that has 2 roles currently assigned, Administrator and Editor. You want to list all available roles with a flag for "Is Assigned" set to True if the user has that role assignment, False if they do not.

To start, you likely do not need a UserRoles DbSet in your context. It's advisable to only create DbSets for entities that are considered top level entities (stuff you'll query) and rely on relationships to manage the rest.

If your goal is to return a list of Roles to associate to a user with an indicator of whether a user holds them or not (such as for a role assignment screen).

If you maintain UserRoles on the Role:

var roles = context.Roles
    .Select(x => new RoleViewModel
    {
       RoleId = x.RoleId,
       Name = x.Name,
       IsAssigned = x.UserRoles.Any(ur => ur.UserId == userId)
    }).ToList();

return roles;

When faced with a single-direction association (I.e. User contains UserRoles, but Roles does not) A simple first step would be to get the User's assigned role IDs, then use that as check against the complete list of roles. It requires 2 simple queries.

var usersRoleIds = context.Users
    .Where(x => x.UserId == userId)
    .SelectMany(x => x.UserRoles.Select(ur => RoleId))
    .ToList();
var roles = context.Roles
    .Select(x => new RoleViewModel
    {
       RoleId = x.RoleId,
       Name = x.Name,
       IsAssigned = userRoleIds.Contains(x.RoleId)
    }).ToList();

return roles;

How to select Entity Framework many-to-many query?

1 Answers