Earlier I had the following classes with relationship
[Table("User")]
public class User
{
// Base properties
[Key]
public int UserId { get; set; }
public string Name { get; set; }
// Relationships
public ICollection<UserRole> UserRoles { get; set; }
}
[Table("UserRole")]
public class UserRole
{
// Base properties
[Key]
public int UserRoleId { get; set; }
public string Name { get; set; }
// Relationships
public int UserId { get; set; }
[ForeignKey("UserId")]
public User User { get; set; }
}
EF Core was able to map this to SQL as intended which gave two tables
- User – UserId (PK), Name
- UserRole – UserRoleId (PK), Name, UserId (FK)
Later I wanted to add a way to verify the records and the verifiers are coming from the User table itself. The below is how the verification implemented:
[Table("User")]
public class User
{
// Base properties
[Key]
public int UserId { get; set; }
public string Name { get; set; }
// Relationships
public ICollection<UserRole> UserRoles { get; set; }
// Verification
public int? VerifierId { get; set; }
[ForeignKey("VerifierId")]
public User Verifier { get; set; }
}
[Table("UserRole")]
public class UserRole
{
// Base properties
[Key]
public int UserRoleId { get; set; }
public string Name { get; set; }
// Relationships
public int UserId { get; set; }
[ForeignKey("UserId")]
public User User { get; set; }
// Verification
public int? VerifierId { get; set; }
[ForeignKey("VerifierId")]
public User Verifier { get; set; }
}
The above setup is giving the following exception while adding the migration: Unable to determine the relationship represented by navigation property 'User.UserRoles' of type 'ICollection<UserRole>'. Either manually configure the relationship, or ignore this property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'.
However removing the below property in the UserRole table makes the migration possible:
ForeignKey("VerifierId")]
public User Verifier { get; set; }
But it is not what I want. I want the foreign key kept as well. How to make this possible? and why this is not allowed at the first place?
>Solution :
The problem here is that EF does not know which FK (UserId or VerifierId) should be mapped to the UserRoles property of the User class.
You need to define which foreign key should be used
[InverseProperty(nameof(UserRole.User))]
public ICollection<UserRole> UserRoles { get; set; }
See the documentation here
https://docs.microsoft.com/en-us/ef/core/modeling/relationships