当fk与pk不同时如何链接一对一的关系 - EntityFramework [英] How to link one-to-one relationship when fk is different from pk - EntityFramework

问题描述

我的数据库中有一个自定义用户表，我想与 aspnetusers 表创建一对一的关系，这样当我注册新客户时，通过 UserManager 的 applicationuser 类应该添加 用户名、电子邮件、密码和学校代码 到用户表，并在自己的表中添加一个 fk.有没有实现这种场景的教程/示例?

我正在尝试添加与我的用户表的一对一关系，但 fk 与 pk 不同

• 用户表 [列]
  • 用户名
  • 密码
  • 学校代码
  • 用户 ID [pk]

ASPNETUsers 表 [标准列]

• 身份证，[pk]
• 用户名，
• 密码哈希，
• 安全印章，
• 判别器，
• 电子邮件，
• 已确认电子邮件，
• 电话号码，
• 已确认电话号码，
• 启用双因素，
• LockoutEndDateUtc,
• LockoutEnabled,
• AccessFailedCount
• userId [fk 到用户表]

我在 ApplicationUser 类中添加了属性

公共类 ApplicationUser : IdentityUser{公共虚拟用户用户{get;放;}公共 int 用户 ID { 获取；放;}}公共类 ApplicationDbContext : IdentityDbContext<ApplicationUser>{受保护的覆盖无效 OnModelCreating(DbModelBuilder modelBuilder){base.OnModelCreating(modelBuilder);modelBuilder.Entity<ApplicationUser>().HasRequired(i => i.user).WithRequiredDependent(i => i.appUser);}}公共类用户{公共 int 用户 ID { 获取；放;}公共字符串用户名 { 获取；放;}公共字符串密码{获取；放;}公共虚拟应用程序用户 appUser { 获取；放;}}

我还需要做什么，是否正确?实体框架如何与这个一起工作?

我试图在这里解释我的应用程序结构:

更新在运行它给我错误MySql.Data.MySqlClient.MySqlException:on 子句"中的未知列Extent8.appUser_Id"

如果我定义

modelBuilder.Entity().HasKey(u => u.UserID);

在 OnModelCreating 中，它会尝试通过此 UserID 链接 AspNetRoles 和 Claims 等表，当然会崩溃.

ApplicationUser_Claims_Source_ApplicationUser_Claims_Target: : 引用约束的Dependent Role中所有属性的类型必须与Principal Role中对应的属性类型相同.实体IdentityUserClaim"上的属性UserId"类型与引用约束ApplicationUser_Claims"中实体ApplicationUser"上的属性UserID"类型不匹配.ApplicationUser_Logins_Source_ApplicationUser_Logins_Target: : 引用约束的Dependent Role中所有属性的类型必须与Principal Role中对应的属性类型相同.实体IdentityUserLogin"上的属性UserId"类型与引用约束ApplicationUser_Logins"中实体ApplicationUser"上的属性UserID"类型不匹配.ApplicationUser_Roles_Source_ApplicationUser_Roles_Target: : 一个参照约束的Dependent Role中所有属性的类型必须与Principal Role中对应的属性类型相同.实体IdentityUserRole"上的属性UserId"类型与引用约束ApplicationUser_Roles"中实体ApplicationUser"上的属性UserID"类型不匹配.

解决方案

简而言之，EF 的一对一关系是如何工作的......

当使用 一对一 关系时，Entity Framework 使用子级中父级的 Id 列作为父级的 FK，它不会创建FK 的第二列，因为这将被视为 一对多 关系，因为第二列将允许父 Id 存在于许多子条目中，而 PK 为FK 不会.

我应该如何配置这两个键?

由于您的 ApplicationUser 继承自 IdentityUser，它将使用它现有的属性和关系.它已经定义了一个 Id 列，因此不需要创建 UserId 以及尝试使 UserId 成为 key 财产.您应该使用现有的 Id 列，默认情况下是 string 并且它看起来您现有的 User 表有一个 integer PK，所以他们不匹配.

然而，这并不是世界末日.Identity 的内置实现使用 string 作为其类(AspNetUsers、AspNetRoles 等...)的键，但它们允许我们使用另一种类型的主键.p>

那么……我该怎么办?

Identity 为每个表定义了两个类，所以有两个IdentityUser 类，两个IdentityRole，以此类推.一个是基础泛型类，它接受一些泛型.另一个是该基类的内置实现，它提供这些泛型类型.

这个基类如下所示:

public class IdentityUser: IUser其中 TLogin : IdentityUserLogin<TKey>其中 TRole : IdentityUserRole<TKey>其中 TClaim : IdentityUserClaim<TKey>

第一个泛型类型是TKey，它定义了key的类型，即string.第二个是一个TLogin，它是一个IdentityUserLogin，这意味着它应该是使用相同的继承IdentityUserLogin的某个类TKey 类型用作键(可能是 string，也可能是 int).

同时，这些基类的内置实现如下所示:

公共类 IdentityUser: IdentityUser, IUser, IUser;{}

其中将 TKey 定义为 string 用作键.IdentityUserLogin 和 IdentityUserRole 等其他泛型是实现 string 键的基类的内置类.看看 IdentityUserRole 长什么样子:

public class IdentityUserRole IdentityUserRole{}

看到了吗?string 也是如此.许多其他类使用 string 作为 TKey 的默认值.

所以如果你要像这样改变你的班级:

public class ApplicationUser : IdentityUser{公共虚拟用户用户{get;放;}}

还不行，因为我们把TKey改成了int，但是内置的IdentityUserLogin还是用字符串.所以我们需要像这样创建自己的 IdentityUserLogin:

公共类 MyNiceUserLogin : IdentityUserLogin<int>{}

...并像这样使用它:

公共类 ApplicationUser : IdentityUser{公共虚拟用户用户{get;放;}}

...但是我们仍然必须对 IdentityUserRole 和 IdentityUserClaim 等其他类做同样的事情.

我将它配置为使用 int.那么一对一的关系呢?

你的代码在这里:

protected override void OnModelCreating(DbModelBuilder modelBuilder){base.OnModelCreating(modelBuilder);modelBuilder.Entity<ApplicationUser>().HasRequired(i => i.user).WithRequiredDependent(i => i.appUser);}

...工作正常.您正在以 必须 存在的方式进行配置，没有 User 就没有 ApplicationUser，反之亦然.

<小时>

现在，我建议您阅读 this Stack Overflow answer 确切了解要更改 Identity 的类以使用 int 作为键要遵循哪些步骤.

<块引用>

我也强烈推荐你阅读 这篇文章 由 John Atten 撰写，所以你可以深入了解如何自定义/扩展身份.

I have a custom users table in my database and i want to create a one-to-one relationship with aspnetusers table so that when i register a new customer, applicationuser class through UserManager should add the username, email, password and school code to the users table and add an fk in its own table. IS there any tutorial/example implementing this kind of scenario?

I am trying to add a one-to-one relationship with my users table but the fk is different than pk

• Users Table [columns]
  • username
  • password
  • school code
  • userId [pk]

ASPNETUsers Table [standard columns]

• Id, [pk]
• UserName,
• PasswordHash,
• SecurityStamp,
• Discriminator,
• Email,
• EmailConfirmed,
• PhoneNumber,
• PhoneNumberConfirmed,
• TwoFactorEnabled,
• LockoutEndDateUtc,
• LockoutEnabled,
• AccessFailedCount
• userId [fk to users table]

I have added property inside ApplicationUser class

public class ApplicationUser : IdentityUser
{
    public virtual User user { get; set; }
    public int UserID { get; set; }
}

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<ApplicationUser>()
          .HasRequired(i => i.user).WithRequiredDependent(i => i.appUser);
      }

}

 public class User
{
    public int UserID { get; set; }
    public string Username { get; set; }
    public string Password { get; set; }
    public virtual ApplicationUser appUser { get; set; }
}

What else do i need to do and is it correct? How does entityframework work with this one?

I have tried to explain My application structure here:

UPDATE On running it gives me error MySql.Data.MySqlClient.MySqlException: Unknown column 'Extent8.appUser_Id' in 'on clause'

If I define

modelBuilder.Entity<ApplicationUser>().HasKey(u => u.UserID);

inside OnModelCreating then it tries to link AspNetRoles and Claims etc tables through this UserID and ofcourse crashes.

ApplicationUser_Claims_Source_ApplicationUser_Claims_Target: : The types of all properties in the Dependent Role of a referential constraint must be the same as the corresponding property types in the Principal Role. The type of property 'UserId' on entity 'IdentityUserClaim' does not match the type of property 'UserID' on entity 'ApplicationUser' in the referential constraint 'ApplicationUser_Claims'. ApplicationUser_Logins_Source_ApplicationUser_Logins_Target: : The types of all properties in the Dependent Role of a referential constraint must be the same as the corresponding property types in the Principal Role. The type of property 'UserId' on entity 'IdentityUserLogin' does not match the type of property 'UserID' on entity 'ApplicationUser' in the referential constraint 'ApplicationUser_Logins'. ApplicationUser_Roles_Source_ApplicationUser_Roles_Target: : The types of all properties in the Dependent Role of a referential constraint must be the same as the corresponding property types in the Principal Role. The type of property 'UserId' on entity 'IdentityUserRole' does not match the type of property 'UserID' on entity 'ApplicationUser' in the referential constraint 'ApplicationUser_Roles'.

解决方案

How EF's one-to-one relationship works, in short...

When working with one-to-one relationship, Entity Framework uses the parent's Id column in the child as the FK to the parent, it won't create a second column for FK because that would be considered one-to-many relationship, because that second column would allow a parent Id to exist in many child entries, whereas the PK as FK wouldn't.

How should I configure both keys?

Since your ApplicationUser inherits from IdentityUser, it will use it's existing properties and relationships. It already defines an Id column, so creating UserId is not necessary, as well as trying to make UserId the key property. You should use the existing Id column, which by default is string and it looks your existing User table has an integer PK, so they don't match.

However, this is not the end of the world. Identity's built-in implementation uses string as key for their classes (AspNetUsers, AspNetRoles, etc...) but they allows us to use another type of primary key.

So...what should I do?

Identity defines two classes for each table, so there are two IdentityUser classes, two IdentityRole, and so on. One is the base generic class, which accepts some generics. The other is the built-in implementation of that base class, which supplies those generic types.

This base class looks like this:

public class IdentityUser<TKey, TLogin, TRole, TClaim> : IUser<TKey>
        where TLogin : IdentityUserLogin<TKey>
        where TRole : IdentityUserRole<TKey>
        where TClaim : IdentityUserClaim<TKey>

The first generic type is TKey, which defines the key's type, which is string. The second is a TLogin, which is a IdentityUserLogin<TKey>, which means it should be some class inheriting IdentityUserLogin using the same TKey type used as key (maybe string, maybe an int).

Meanwhile, the built-in implementation for these base classes looks like this:

public class IdentityUser 
    : IdentityUser<string, IdentityUserLogin, IdentityUserRole, IdentityUserClaim>, IUser, IUser<string> {}

Which defines TKey as a string to be used as key. The other generics like IdentityUserLogin and IdentityUserRole, are built-in classes implementing the base classes which a string key. Take a look how IdentityUserRole looks like:

public class IdentityUserRole  IdentityUserRole<string> {}

See? string as well. Many other classes use string as default for TKey.

So if you were to change your class like this:

public class ApplicationUser : IdentityUser<int, IdentityUserLogin, IdentityUserRole, IdentityUserClaim>
{
    public virtual User user { get; set; }
}

It wouldn't work yet, because we changed TKey to be int, but the built-in IdentityUserLogin still uses string. So we need to create our own IdentityUserLogin like this:

public class MyNiceUserLogin : IdentityUserLogin<int>
{
}

...and use it like this:

public class ApplicationUser : IdentityUser<int, MyNiceUserLogin, IdentityUserRole, IdentityUserClaim>
{
    public virtual User user { get; set; }
}

...but we still had to do the same with the other classes like IdentityUserRole and IdentityUserClaim.

I configured it to use int. Now what about one-to-one relationship?

Your code here:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<ApplicationUser>()
      .HasRequired(i => i.user).WithRequiredDependent(i => i.appUser);
}

...works fine. You're configuring in a way both must exist, there is no ApplicationUser without a User and vice-versa.

---

Now, I recommend you read this Stack Overflow answer to know exactly what steps to follow in order to change Identity's classes to use int as key.

I also strongly recommend you to read this post by John Atten, so you can understand deeply about how to customize/extend Identity.

这篇关于当fk与pk不同时如何链接一对一的关系 - EntityFramework的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！