CODEWITHSUNDEEP
c#asp.net-mvcasp.net-mvc-5asp.net-identity
Zhi Kai
Zhi Kai

Reputation: 1587

Identity new table foreign key to AspNetUsers

I am creating a new table (Let's call it Chart) under MVC Identity which consists of 2 columns (PatientId and DoctorId) that will refer back to the Id column of AspNetUsers. The new table will also have a PK of its own. The following is the IdentityModels class.

public class ApplicationUser : IdentityUser 
{
  public virtual Chart Chart { get; set; }     
  ...
}

public class Chart 
{
   [Key]
   public int Id { get; set; } 

   //How to FK the following two params?
   public string PatientId { get; set; }
   public string DoctorId { get; set; }

   public virtual ApplicationUser User { get; set; } // navigation property    
}

public class ApplicationDbContext : IdentityDbContext<ApplicationUser> 
{
    public ApplicationDbContext()
          : base("DefaultConnection", throwIfV1Schema: false) {
}

public System.Data.Entity.DbSet<Chart> Chart { get; set; }

public static ApplicationDbContext Create()
{
   return new ApplicationDbContext();
}

May I know how I can refer both my PatientId and DoctorId back to the Id column of AspNetUsers table?

It will be a one-to-many relationship (One DoctorId can have many PatientId, but one PatientId can only be attached to one DoctorId).

Upvotes: 0

Views: 3615

Answers (1)

Chris Pratt
Chris Pratt

Reputation: 239260

If I'm correct in assuming that both Patient and Doctor are in fact "users", then you should actually inherit them from ApplicationUser.

public class Patient : ApplicationUser
{
    // patient-specific properties

    [ForeignKey("Doctor")]
    public string DoctorId { get; set; } // IdentityUser's PK is string by default
    public virtual Doctor Doctor { get; set; }
}

public class Doctor : ApplicationUser
{
    // doctor-specific properties

    public virtual ICollection<Patient> Patients { get; set; }
}

By default, Entity Framework employs single-table inheritance, so in this configuration, you won't actually end up with separate tables for Doctor and Patient. Instead, all the properties of both will be added to AspNetUsers. For the most part, this is not an issue. The only time it can be problematic is if you need to require a property specific to just one subclass, such as Doctor. All properties on subclasses in this configuration must be nullable, since there is logically no way to provide a required value for Doctor when saving Patient. However, this is only enforced at the database-level. You're still free to validate an input in a form, for example, as being required, even if the table-column backing it is not.

That said, there are other strategies you can use. In this case, the most appropriate alternative would be TPT, or Table-Per-Type. Here, you get a table for each discrete type, ApplicationUser, Doctor, and Patient. Then, on the subclasses (Doctor and Patient) a foreign key is added to ApplicationUser. It is that ApplicationUser instance that holds the true "id" for the entity. To use TPT, it's as simple as adding the Table attribute to each class:

[Table("Doctors")]
public class Doctor : ApplicationUser

[Table("Patients")]
public class Patient : ApplicationUser

UPDATE

Regarding Chart, with this setup, your implementation would look like:

public class Chart 
{
    [Key]
    public int Id { get; set; } 

    [ForeignKey("Patient")]
    public string PatientId { get; set; }
    public virtual Patient Patient { get; set; }

    [ForeignKey("Doctor")]
    public string DoctorId { get; set; }
    public virtual Doctor Doctor { get; set; }
}

Upvotes: 2

Related Questions