Map same Model class for multiple purposes in Entity FrameWork

Question

I have two model classes one is ApplicationUser and the second is Appointment. Application user includes all users that use the application, in my case, Doctors and Data entry operators. Doctors will be assigned to each appointment and Data entry operators will be making this log to DB. I want to map both these users with appointment. I have tried something like this

public class Appointment
{
    public int AppointmentID { get; set; }
    public DateTime Date { get; set; }

    public int DoctorID { get; set; }
    [ForeignKey("DoctorID")]
    public virtual ApplicationUser Doctor { get; set; }

    public int SystemUserID { get; set; }
    public virtual ApplicationUser SystemUser { get; set; }
}

public class ApplicationUser : IdentityUser
{
    public string Email { get; set; }
    public string Mobile { get; set; }
    public string FirstNsme { get; set; }
    public string LastName { get; set; }
}

But this throws an error

Appointment_Doctor_Target_Appointment_Doctor_Source: : 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 'DoctorID' on entity 'Appointment' does not match the type of property 'Id' on entity 'ApplicationUser' in the referential constraint 'Appointment_Doctor'.

Can anyone point out why this error is occurring and what is the correct approach to this problem?

Answer 1

IdentityUser as all entities in asp.net identity entity framework have string as key. You are trying to map to an int. So either use Guids as foreign keys in your Appointment entity

public class Appointment
{
    [Key]
    public int AppointmentID { get; set; }
    public DateTime Date { get; set; }

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

    public string SystemUserID { get; set; }
    [ForeignKey("SystemUserID ")]
    public virtual ApplicationUser SystemUser { get; set; }
}

or change the type of Ids in identity classes to int. You can find help here.

Answer 2

There are multiple issue in your classes.

What is DoctorID? Where it is defined?

You need to first focus on establishing correct relationship between your entities logically.

I think your Appointment class need not contain SystemUserID who added an appointment.

Second if you wanted to share some properties between two user types than create a common class and derive in Doctor and SystemUser.

Add DoctorId into Doctor table along with specific details pertaining to Doctor e.g. Specialty.

SystemUser adds a appointment so the table should contain data related to that i.e. doctorId and appointmentId.

Update:

Based on your comment, you could do something like this. Note its for reference only, you are better person to define a better DB Schema.

public class Appointment
{
    public int AppointmentID { get; set; }
    public DateTime Date { get; set; }

    public int DoctorID { get; set; }
    [ForeignKey("ApplicationUserId")]
    public virtual ApplicationUser Doctor { get; set; }

    public int SystemUserID { get; set; }
    [ForeignKey("ApplicationUserId")]
    public virtual ApplicationUser SystemUser { get; set; }
}

public class ApplicationUser
{
    public int ApplicationUserId { get; set; }
    public string Email { get; set; }
    public string Mobile { get; set; }
    public string FirstNsme { get; set; }
    public string LastName { get; set; }
    public UserType UserType { get; set; }
}

public enum UserType
{
    Doctor,
    SystemUser
}

Answer 3

FURTHER AND MORE COMPLEX ERROR:

I had this error multiple times across 4 linked tables.

Each table had composite keys of 3 - 7 fields, and one table referenced its own 3-field key with a different mix of its own columns.

I struggled for ages with fixing one sequence of fields (which does fix the error as mentioned in other posts) only to have knock-on effect in other entities.

The solution: Align all linked tables' FK fields in order of reducing occurrence

ORIGINALLY:

AFTER KEY FIELDS WERE ALIGNED:

And re-ordered all anonymous FK objects in FluentAPI in the DbContext to match the new order.

This fixed all headaches.