Entity Framework: create a personal relationship with yourself
I have a contact with an entity that needs to be attached to itself NOT in hierarchical mode
public partial class Contact
{
[Key, Column(Order = 0)]
public int AgentId { get; set; }
[Key, Column(Order = 1)]
public int ContactId { get; set; }
public virtual Contact Opposite { get; set; }
public ..... many other properties
}
Each contact has an opposite one Contact
connected ON c1.AgentId = c2.ContactId AND c1.ContactId = c2.AgentId
. Opposite contact is optional, but when it exists, they are equal to each other. Not a parent-child. The name of this relationship should be Opposite
. I have already declared a property in the class Contact
. Now I am trying to establish a relationship but it doesn't work and I feel like I don't know how to set it up correctly. Please advice?
public class EFDbContext : DbContext
{
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
Action<ForeignKeyAssociationMappingConfiguration> mapKey = x => x.MapKey("ContactId", "AgentId");
modelBuilder.Entity<Contact>().HasOptional<Contact>(c => c.Opposite).WithRequired().Map(mapKey);
}
}
source to share
The relationship cannot be optional when using the primary key as the foreign key of the relationship, because the primary key cannot have a value NULL
. The foreign key will always have values - say ( ContactId
= 1, AgentId
= 2) - and the foreign key constraint will be violated if the row with ( AgentId
= 1, ContactId
= 2) doesn't exist.
But with the required relationship, you can only have row pairs, and it is impossible to insert any meaningful row at all into a database table, because it will always violate a foreign key constraint: To insert a row ( AgentId
= 1, ContactId
= 2) there must be a row ( AgentId
= 2 , ContactId
= 1) and vice versa. The only possible lines you could insert are lines like ( AgentId
= 1, ContactId
= 1), that is, where the contact Opposite
is the contact itself.
To get an optional relationship, you need separate foreign keys:
public partial class Contact
{
[Key, Column(Order = 0)]
public int AgentId { get; set; }
[Key, Column(Order = 1)]
public int ContactId { get; set; }
[ForeignKey("Opposite"), Column(Order = 2)]
public int? OppositeContactId { get; set; }
[ForeignKey("Opposite"), Column(Order = 3)]
public int? OppositeAgentId { get; set; }
public virtual Contact Opposite { get; set; }
//...
}
This is a one-to-many relationship. With the Fluent API, instead of data annotations, it would be:
modelBuilder.Entity<Contact>()
.HasOptional(c => c.Opposite)
.WithMany()
.HasForeignKey(c => new { c.OppositeContactId, c.OppositeAgentId });
On the EF side, you cannot establish a one-to-one relationship. You can only add a unique index to a composite foreign key in the database to ensure that no two contacts have the same opposite.
source to share