The entire error:

The foreign key property 'Appointment.CustomerId1' was created in a shadow state because a conflicting property with the simple name 'CustomerId' exists in the entity type, but is either not mapped, is already used for another relationship, or is incompatible with the associated primary key type.

Tables:

Appointment

User

In the Appointment table, I had 2 FKs: one pointing to the Customer Table (Id Column) and the other to Employee Table (Id Column). See the picture at the BEFORE section. But now, because I will have all users (customer employee) in the same table User, it has to change. That means that in the Appointment table I need to have 2 FKs but both need to point to the same table User, and the Id column. I want the CustomerId and EmployeeId to point to the Id from the User.

It creates 3 extra columns: UserId, CustomerId1, and EmployeeId1--which I don't want. I only used Conventions for the relationships, no Data Annotations or Fluent API.

There are 3 things that can cause the error:

• 1. not mapped
• 2. already used for another relationship
• 3. incompatible with the associated primary key type

By my understanding

• 3. is not my case, because the data type is the same (string).
• 2. is not my case because I have no other relationship.
• 1. There might be a problem but I'm not sure. I might need to add some Fluent Api for this mapping. This is what I've tried but it's not working: https://i.stack.imgur.com/UFrC6.png

Bi-directional method:

User class:

public ICollection<Appointment> AppointmentCustomers { get; set; }
public ICollection<Appointment> AppointmentEmployees { get; set; }

Appointment class:

public string CustomerId { get; set; }
[ForeignKey("CustomerId")]
public User Customer { get; set; }

public string EmployeeId { get; set; }
[ForeignKey("EmployeeId")]
public User Employee { get; set; }

OnModelCreating method:

builder.Entity<Appointment>()
    .HasOne(u => u.Customer)
    .WithMany(app => app.AppointmentCustomers)
    .HasForeignKey(u => u.CustomerId)
    .OnDelete(DeleteBehavior.NoAction);

builder.Entity<Appointment>()
    .HasOne(u => u.Employee)
    .WithMany(app => app.AppointmentEmployees)
    .HasForeignKey(u => u.EmployeeId)
    .OnDelete(DeleteBehavior.NoAction);

CodePudding user response：

Before, EF could work out the keys by convention. The convention being based on the Type name (Employee vs. Customer) rather than the variable name. If you had named the FKs EmployeeKey/EmpID and CustomerKey/CustID you would have had a similar problem. EF's convention wouldn't have associated those as the FKs so it would have created shadow properties.

Now that you are pointing these both at the same Type (User) EF cannot use convention, so you have to be explicit. The easiest way to fix that is to use the [ForeignKey] attribute. This can either be put on the FK property to point at the navigation property, or on the navigation property to point at the FK.

I.e.

[ForeignKey(nameof(Customer))]
public string CustomerId { get; set; }
[ForeignKey(nameof(Employee))]
public string EmployeeId { get; set; }

public virtual User Customer { get; set; }
public virtual User Employee { get; set; }

or

public string CustomerId { get; set; }
public string EmployeeId { get; set; }

[ForeignKey(nameof(CustomerId))]
public virtual User Customer { get; set; }
[ForeignKey(nameof(EmployeeId))]
public virtual User Employee { get; set; }

Edit:

The error you are now getting is because User has a collection of Appointments but you'd have to tell EF which collection this FK links to. Here you have an appointment which references two users, a customer and an employee. From the User perspective there are 2 distinct relationships. Appointments-I-am-a-Customer and Appointments-I-am-an-Employee.

If you are interested in only one relationship you need to configure EF to map User.Appointments based on the Appointment Customer or Employee relationship. If you are interested in both then you have two options.

a) Add 2 Appointments collections to User, i.e. AppointmentsAsCustomer and AppointmentsAsEmployee and set up the mappings in ModelBuilder or an EntityTypeConfiguration. b) Remove the Appointments collection on User and configure EF with a .HasOne(x => x.Customer).WithMany() and .HasOne(x => x.Employee).WithMany() then when you want to query appointments by a user, do it from Appointments rather than expecting to navigate via User:

instead of:

var appointmentsForUser = context.Users
    .Where(u => u.UserId == userId)
    .SelectMany(u => u.Appointments) // can't work for both asEmployee and asCustomer
    .ToList();

use:

var appointmentsForUser = context.Appointments
    .Where(a => a.Customer.UserId == userId 
       || a.Employee.UserId == userId) // if you want appts where user is customer or employee
    .ToList();

Bi-directional references (where Apointments has a reference to user(s) and User has a reference to Appointments) should be used sparingly only as absolutely required rather than by default. They can lead to weird behavior when updating plus lazy loading implications, and "gotchas" like this with ambiguous relationships.

CodePudding user response：

The property was already used for another relationship from the old Customer and Employee classes.

(Steve Py's answer helps a lot with the foreign key configurations, but from what I've read it's better to configure them through Fluent API vs Data Annotations.)