使用Code First建立3个表之间的多对多关系 [英] Modeling a many-to-many relationship between 3 tables with Code First

问题描述

我有以下3个实体：

• 用户


• 帐户


• 角色

关系如

• 一个用户可以有很多帐户


• 一个帐户可以属于许多用户


• 每个用户在帐户中都有一个角色


• 有一些预定义的角色（在枚举角色中定义）

• 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)

我得到这很远：

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.

例如：

考虑3个用户和3个帐户：

Consider 3 users and 3 accounts:

• Account1 =（User1，Owner），（User2，TeamMember）


• Account2 =（User2，Owner）


• Account3 =（User3，Owner），（User1，ReadOnlyMember）

关于@ IvanStoev的回答， / p>

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

我应该保存UserAccount对象（作为 DbContext< UserAccount> ）？

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

推荐答案

<一个用户可以拥有多个帐户

• 一个帐户可以属于许多用户


• 每个用户在帐户中都有角色

当您需要关联其他信息时，自动链接表不适用于此类情况（与用户 - 帐户链接。

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:

型号：

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

配置：

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

您可以创建一个新的 UserAccount 几种方法。

You can create a new UserAccount in a several ways.

一种方法是将其添加到其中一个 UserAccounts 集合中。在您的示例中， account.UserAccounts.Add（uc）; 后跟 context.Accounts.Add（account）将自动添加到 context.UserAccounts ， user.UserAccounts 和 role.UserAccounts 。

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.

另一种方法是使用 context.UserAccounts.Add（uc）; 在这种情况下，它将被自动添加到用户，帐户和角色孩子 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.

这篇关于使用Code First建立3个表之间的多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！