CODEWITHSUNDEEP
entity-frameworkef-code-firstcode-first
Vladimir WWW
Vladimir WWW

Reputation: 53

Entity Framework: create one-to-one relation to itself

I have the entity Contact that must be linked to itself NOT in the 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
}

Every Contact has opposite Contact linked ON c1.AgentId = c2.ContactId AND c1.ContactId = c2.AgentId. The opposite contact is optional but when it exists they are equal one-to-one. Not a parent-child. The name of this relation must be Opposite. I have already declared property in the Contact class. Now I am trying to set relationship but this is not working and I feel like I have no idea how to set it up properly. 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);
    }
}

Upvotes: 4

Views: 3127

Answers (1)

Slauma
Slauma

Reputation: 177163

The relationship cannot be optional when you use the primary key as the foreign key of the relationship because a primary key cannot have NULL values. The foreign key will always have values - say (ContactId = 1, AgentId = 2) - and the foreign key constraint would be violated if the row with (AgentId = 1, ContactId = 2) does not exist.

But with a required relationship you can only have pairs of rows and it is impossible to insert any meaningful row at all into the database table because it would always violate the foreign key constraint: To insert row (AgentId = 1, ContactId = 2) the row (AgentId = 2, ContactId = 1) must exist and vice versa. The only possible rows you could insert are rows like (AgentId = 1, ContactId = 1), i.e. where the Opposite contact is the contact itself.

In order to achieve 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 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 EF side you cannot make it a one-to-one relationship. You could only add a unique index on the composite foreign key in the database to ensure that no two contacts have the same opposite.

Upvotes: 2

Related Questions