Modeling a many-to-many relationship between 3 tables with Code First

Question

I have the following 3 entities:

• User
• Account
• Role

And the relationship goes like

• One user can have many accounts
• One account can belong to many users
• Each user has a role in an account
• There are a few, predefined roles (defined in the enum Roles)

I got this far:

public class User
{
    public int Id { get; set; }
    public ICollection<Account> Accounts { get; set; }
}

public class Account
{
    public int Id { get; set; }
    public ICollection<User> Users { get; set; }
}

public class Role
{
    public int Id { get; set; }
    public string RoleName { get; set; }
    public virtual ICollection<User> Users { get; set; }
    public virtual ICollection<Account> Accounts { get; set; }
}

Every time an user registers, they also create an account, so I thought of this:

public async Task AddAccountAsync(User user, string accountName)
{
    Account account = new Account(user, accountName);
    Role role = new Role(Roles.Owner);
    Accounts.Add(account);
    user.Accounts.Add(account);
    Entry(user).State = EntityState.Modified;
    await SaveChangesAsync();
 }

But I am not sure how to tie the role to the user to the account, so I can get the role an user has in an account.

For example:

Consider 3 users and 3 accounts:

• Account1 = (User1, Owner), (User2, TeamMember)
• Account2 = (User2, Owner)
• Account3 = (User3, Owner), (User1, ReadOnlyMember)

Following @IvanStoev's answer, I got this:

public async Task AddAccountAsync(User user, string accountName)
{
   Role role = new Role(Roles.Owner);
   Account account = new Account(model.AccountCurrency, model.AccountName, model.Description);
   UserAccount uc = new UserAccount
   {
       User = user,
       Account = account,
       Role = role
   };
   account.UserAccounts.Add(uc);
   Accounts.Add(account);

   user.UserAccounts.Add(uc);
   Entry(user).State = EntityState.Modified;
   await SaveChangesAsync();
}

Should I be saving the UserAccount object (as a DbContext<UserAccount>)?

Answer 1

• One user can have many accounts
• One account can belong to many users
• Each user has a role in an account

The automatic link table does not work for scenarios like this when you need to associate additional information (Role in this case) with the User - Account link.

So instead of automatic many-to-many association you need to use two one-to-many associations with explicit link entity like this:

Model:

public class User
{
    public int Id { get; set; }
    public ICollection<UserAccount> UserAccounts { get; set; }
}

public class Account
{
    public int Id { get; set; }
    public ICollection<UserAccount> UserAccounts { get; set; }
}

public class UserAccount
{
    public int UserId { get; set; }
    public int AccountId { get; set; }
    public int RoleId { get; set; }
    public User User { get; set; }
    public Account Account { get; set; }
    public Role Role { get; set; }
}

public class Role
{
    public int Id { get; set; }
    public string RoleName { get; set; }
    public ICollection<UserAccount> UserAccounts { get; set; }
}

Configuration:

modelBuilder.Entity<UserAccount>()
    .HasKey(e => new { e.UserId, e.AccountId });

modelBuilder.Entity<UserAccount>()
    .HasRequired(e => e.User)
    .WithMany(e => e.UserAccounts)
    .HasForeignKey(e => e.UserId);

modelBuilder.Entity<UserAccount>()
    .HasRequired(e => e.Account)
    .WithMany(e => e.UserAccounts)
    .HasForeignKey(e => e.AccountId);

modelBuilder.Entity<UserAccount>()
    .HasRequired(e => e.Role)
    .WithMany(e => e.UserAccounts)
    .HasForeignKey(e => e.RoleId);

You can create a new UserAccount in a several ways.

One way is to add it to one of the child UserAccounts collections. In your example, account.UserAccounts.Add(uc); followed by context.Accounts.Add(account) will automatically add it to context.UserAccounts, user.UserAccounts and role.UserAccounts.

Another way is to use context.UserAccounts.Add(uc); in which case it will be automatically added to user, account and role child UserAccounts collections.